יצירת פונקציה מוגדרת משתמש (UDF) ב- Excel VBA (מדריך אולטימטיבי)

עם VBA, אתה יכול ליצור פונקציה מותאמת אישית (הנקראת גם פונקציה מוגדרת על ידי משתמש) שניתן להשתמש בה בדפי העבודה בדיוק כמו פונקציות רגילות.

אלה מועילים כאשר הפונקציות הקיימות של Excel אינן מספיקות. במקרים כאלה, תוכל ליצור פונקציה מותאמת אישית משלך (UDF) המותאמת לצרכים הספציפיים שלך.

במדריך זה אעסוק בכל אודות יצירה ושימוש בפונקציות מותאמות אישית ב- VBA.

אם אתה מעוניין ללמוד VBA בדרך הקלה, בדוק את שלי הדרכה מקוונת ב- Excel VBA.

מהו הליך פונקציות ב- VBA?

הליך פונקציה הוא קוד VBA המבצע חישובים ומחזיר ערך (או מערך ערכים).

באמצעות הליך פונקציה, באפשרותך ליצור פונקציה בה תוכל להשתמש בגליון העבודה (בדיוק כמו כל פונקציית Excel רגילה כגון SUM או VLOOKUP).

כאשר יצרת הליך פונקציה באמצעות VBA, תוכל להשתמש בו בשלוש דרכים:

1. כנוסחה בגליון העבודה, שבה היא יכולה לקחת ארגומנטים כתשומות ומחזירה ערך או מערך ערכים.
2. כחלק מקוד המשנה של VBA שלך או קוד פונקציה אחר.
3. בעיצוב מותנה.

אמנם יש כבר 450+ פונקציות Excel מובנות בגליון העבודה, אך ייתכן שתזדקק לפונקציה מותאמת אישית אם:

• הפונקציות המובנות אינן יכולות לעשות את מה שאתה רוצה לעשות. במקרה זה, תוכל ליצור פונקציה מותאמת אישית המבוססת על הדרישות שלך.
• הפונקציות המובנות יכולות לבצע את העבודה אך הנוסחה ארוכה ומסובכת. במקרה זה, אתה יכול ליצור פונקציה מותאמת אישית שקל לקרוא ולהשתמש בה.

שים לב שפונקציות מותאמות אישית שנוצרו באמצעות VBA יכולות להיות איטיות משמעותית מהפונקציות המובנות. לפיכך, אלה מתאימים ביותר למצבים בהם אינך יכול להשיג את התוצאה באמצעות הפונקציות המובנות.

פונקציה נגד. שגרת משנה ב- VBA

'תת שגרה' מאפשרת לך לבצע סדרת קוד בעוד 'פונקציה' מחזירה ערך (או מערך ערכים).

כדי לתת לך דוגמה, אם יש לך רשימת מספרים (חיוביים ושליליים), ואתה רוצה לזהות את המספרים השליליים, הנה מה שאתה יכול לעשות עם פונקציה ותת -שגרה.

תת -שגרה יכולה לחדור בכל תא בטווח ויכולה להדגיש את כל התאים שיש בהם ערך שלילי. במקרה זה, תת -השגרה בסופו של דבר משנה את המאפיינים של אובייקט הטווח (על ידי שינוי צבע התאים).

עם פונקציה מותאמת אישית, אתה יכול להשתמש בה בעמודה נפרדת והיא יכולה להחזיר TRUE אם הערך בתא שלילי ושקר אם הוא חיובי. עם פונקציה, אינך יכול לשנות את מאפייני האובייקט. המשמעות היא שאתה לא יכול לשנות את צבע התא עם פונקציה עצמה (עם זאת, אתה יכול לעשות זאת באמצעות עיצוב מותנה עם הפונקציה המותאמת אישית).

כאשר אתה יוצר פונקציה מוגדרת משתמש (UDF) באמצעות VBA, תוכל להשתמש בפונקציה זו בגליון העבודה בדיוק כמו כל פונקציה אחרת. אעסוק בנושא נוסף בסעיף 'דרכים שונות לשימוש בפונקציה המוגדרת על ידי משתמש ב- Excel'.

יצירת פונקציה פשוטה מוגדרת משתמש ב- VBA

הרשה לי ליצור פונקציה פשוטה המוגדרת על ידי המשתמש ב- VBA ולהראות לך כיצד היא פועלת.

הקוד שלהלן יוצר פונקציה שתחלץ את החלקים המספריים ממחרוזת אלפאנומרית.

פונקציה GetNumeric (CellRef כמחרוזת) כ- Long StringLength כ- Long StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) ואז התוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i GetNumeric = פונקציית סיום התוצאה

כאשר יש לך את הקוד הנ"ל במודול, תוכל להשתמש בפונקציה זו בחוברת העבודה.

להלן כיצד פונקציה זו - GetNumeric - ניתן להשתמש ב- Excel.

עכשיו לפני שאני אגיד לך כיצד נוצרת הפונקציה הזו ב- VBA וכיצד היא פועלת, יש כמה דברים שכדאי שתדע:

• כאשר אתה יוצר פונקציה ב- VBA, היא הופכת לזמינה בחוברת העבודה כולה בדיוק כמו כל פונקציה רגילה אחרת.
• כאשר אתה מקליד את שם הפונקציה ואחריו השווה לסימן, Excel יראה לך את שם הפונקציה ברשימת הפונקציות התואמות. בדוגמה שלמעלה, כאשר הזנתי = Get, Excel הראה לי רשימה עם הפונקציה המותאמת אישית שלי.

אני מאמין שזו דוגמה טובה כאשר אתה יכול להשתמש ב- VBA ליצירת פונקציה פשוטה לשימוש ב- Excel. אתה יכול לעשות את אותו הדבר גם עם נוסחה (כפי שמוצג במדריך זה), אבל זה הופך להיות מסובך וקשה להבנה. עם UDF זה, אתה רק צריך להעביר טיעון אחד ותקבל את התוצאה.

אנטומיה של פונקציה מוגדרת משתמש ב- VBA

בחלק למעלה, נתתי לך את הקוד והראיתי לך כיצד פועלת הפונקציה UDF בגליון עבודה.

עכשיו בואו נצלול לעומק ונראה כיצד נוצרת פונקציה זו. עליך למקם את הקוד שלהלן במודול בעורך VB. אני מכסה את הנושא הזה בסעיף - 'היכן לשים את קוד VBA עבור פונקציה המוגדרת על ידי משתמש'.

פונקציה GetNumeric (CellRef כמחרוזת) כל עוד פונקציה זו מחלצת את החלק המספרי מהמחרוזת Dim StringLength כמספר שלם StringLength = Len (CellRef) For i = 1 ל- StringLength If IsNumeric (Mid (CellRef, i, 1)) ואז התוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i GetNumeric = פונקציית סיום התוצאה

השורה הראשונה של הקוד מתחילה במילה - פונקציה.

מילה זו אומרת ל- VBA שהקוד שלנו הוא פונקציה (ולא תת -שגרה). אחרי המילה פונקציה מופיע שם הפונקציה - GetNumeric. זהו השם בו נשתמש בגיליון העבודה לשימוש בפונקציה זו.

• שם הפונקציה לא יכול להכיל רווחים. כמו כן, אינך יכול לתת שם לפונקציה אם היא מתנגשת בשם של הפניה לתא. לדוגמה, אינך יכול לתת שם לפונקציה ABC123 מכיוון שהיא מתייחסת גם לתא בגליון העבודה של Excel.
• אתה לא צריך לתת לפונקציה שלך את אותו שם כמו של פונקציה קיימת. אם תעשה זאת, Excel תעניק עדיפות לפונקציה המובנית.
• אתה יכול להשתמש בקו תחתון אם אתה רוצה להפריד בין מילים. לדוגמה, Get_Numeric הוא שם מקובל.

אחרי הפונקציה יש כמה ארגומנטים בסוגריים. אלה הטענות שהפונקציה שלנו תצטרך מהמשתמש. אלה בדיוק כמו הטיעונים שאנחנו צריכים לספק לפונקציות המובנות של Excel. לדוגמה בפונקציה COUNTIF, ישנם שני ארגומנטים (טווח וקריטריונים)

בתוך הסוגריים, עליך לציין את הארגומנטים.

בדוגמה שלנו, יש רק טיעון אחד - CellRef.

זה גם מנהג טוב לציין איזה סוג של טיעון הפונקציה מצפה. בדוגמה זו, מכיוון שנזין את הפונקציה בהתייחסות לתא, נוכל לציין את הארגומנט כסוג 'טווח'. אם לא תציין סוג נתונים, VBA יחשוב שהוא גרסה (מה שאומר שאתה יכול להשתמש בכל סוג נתונים).

אם יש לך יותר מארגומנטים אחד, תוכל לציין אותם באותו סוגר - מופרדים בפסיק. נראה בהמשך הדרכה זו כיצד להשתמש במספר ארגומנטים בפונקציה המוגדרת על ידי משתמש.

שים לב שהפונקציה מצוינת כסוג הנתונים 'מחרוזת'. זה יגיד ל- VBA שהתוצאה של הנוסחה תהיה מסוג נתוני מחרוזת.

למרות שאני יכול להשתמש כאן בסוג נתונים מספריים (כגון Long או Double), פעולה זו תגביל את טווח המספרים שהוא יכול להחזיר. אם יש לי מחרוזת ארוכה של 20 מספרים שאני צריכה לחלץ מהמחרוזת הכוללת, הצהרת הפונקציה כארוכה או כפולה תיתן שגיאה (מכיוון שהמספר יהיה מחוץ לטווח שלו). מכאן ששמרתי את סוג נתוני פלט הפונקציה כמחרוזת.

השורה השנייה של הקוד - זו בירוק שמתחילה באפוסטרוף - היא הערה. בעת קריאת הקוד, VBA מתעלם משורה זו. תוכל להשתמש בזה כדי להוסיף תיאור או פירוט אודות הקוד.

השורה השלישית של הקוד מכריזה על משתנה 'StringLength' כסוג נתונים שלם. זהו המשתנה שבו אנו מאחסנים את ערך אורך המחרוזת המנותחת על ידי הנוסחה.

השורה הרביעית מכריזה על המשתנה תוצאה כסוג נתוני מחרוזת. זהו המשתנה שבו נחלץ את המספרים מהמחרוזת האלפאנומרית.

השורה החמישית מקצה את אורך המחרוזת בארגומנט הקלט למשתנה 'StringLength'. שים לב כי 'CellRef' מתייחס לטיעון שיינתן על ידי המשתמש בעת שימוש בנוסחה בדף העבודה (או שימוש בו ב- VBA - אותו נראה בהמשך הדרכה זו).

השורות השישיות, השביעיות והשמיניות הן החלק של הלולאה For Next. הלולאה פועלת פעמים רבות ככל שיש תווים רבים בארגומנט הקלט. מספר זה ניתן על ידי הפונקציה LEN והוא מוקצה למשתנה 'StringLength'.

אז הלולאה עוברת מ- '1 לאורך המיתר'.

בתוך הלולאה, משפט IF מנתח כל תו של המחרוזת ואם הוא מספרי, הוא מוסיף את התו המספרי הזה למשתנה התוצאה. הוא משתמש בפונקציית MID ב- VBA לשם כך.

השורה האחרונה השנייה בקוד מקצה את ערך התוצאה לפונקציה. שורת הקוד הזו מבטיחה שהפונקציה תחזיר את ערך 'התוצאה' בחזרה לתא (משם היא נקראת).

השורה האחרונה של הקוד היא פונקציית סיום. זוהי שורת קוד חובה שאומרת ל- VBA שקוד הפונקציה מסתיים כאן.

הקוד לעיל מסביר את החלקים השונים של פונקציה מותאמת אישית טיפוסית שנוצרה ב- VBA. בחלקים הבאים, נצלול לעומק אלמנטים אלה ונראה גם את הדרכים השונות לביצוע פונקציית VBA ב- Excel.

ארגומנטים בפונקציה מוגדרת משתמש ב- VBA

בדוגמאות שלעיל, שם יצרנו פונקציה המוגדרת על ידי משתמש כדי לקבל את החלק המספרי ממחרוזת אלפאנומרית (GetNumeric), הפונקציה נועדה לקחת ארגומנט אחד בודד.

בחלק זה אסקור כיצד ליצור פונקציות שאינן לוקחות טיעון לאלה שלוקחות טיעונים מרובים (נדרשות וגם ארגומנטים אופציונאליים).

יצירת פונקציה ב- VBA ללא טיעונים

בגליון העבודה של Excel, יש לנו מספר פונקציות שאינן דורשות טיעונים (כגון RAND, TODAY, NOW).

פונקציות אלה אינן תלויות בשום טיעוני קלט. לדוגמה, הפונקציה TODAY תחזיר את התאריך הנוכחי ופונקציית RAND תחזיר מספר אקראי בין 0 ל -1.

אתה יכול ליצור פונקציה דומה כזו גם ב- VBA.

להלן הקוד שייתן לך את שם הקובץ. זה לא לוקח שום טיעון מכיוון שהתוצאה שהוא צריך להחזיר אינה תלויה בשום טיעון.

Function WorkNameName () כ- String WorkbookName = ThisWorkbook.Name סיום פונקציה

הקוד לעיל מציין את תוצאת הפונקציה כסוג נתוני מחרוזת (מכיוון שהתוצאה הרצויה לנו היא שם הקובץ - שהוא מחרוזת).

פונקציה זו מקצה את הערך של 'ThisWorkbook.Name' לפונקציה, המוחזרת כאשר משתמשים בפונקציה בגליון העבודה.

אם הקובץ נשמר, הוא מחזיר את השם עם סיומת הקובץ, אחרת הוא פשוט נותן את השם.

אולם לעיל יש בעיה אחת.

אם שם הקובץ משתנה, הוא לא יתעדכן אוטומטית. בדרך כלל פונקציה מתרעננת בכל פעם שיש שינוי בארגומנטים הקלט. אך מכיוון שאין פונקציות בפונקציה זו, הפונקציה אינה מחשבת מחדש (גם אם תשנה את שם חוברת העבודה, סגור אותה ולאחר מכן פתח מחדש).

אם תרצה, תוכל לכפות חישוב מחדש באמצעות קיצור המקשים - Control + Alt + F9.

כדי לגרום לנוסחה לחשב מחדש בכל פעם שיש שינוי בגליון העבודה, עליך להוסיף לו שורת קוד.

הקוד שלהלן גורם לפונקציה לחשב מחדש בכל פעם שיש שינוי בגליון העבודה (בדיוק כמו פונקציות דומות אחרות של דפי עבודה כגון פונקציה TODAY או RAND).

Function WorkNameName () כ- String Application.Volatile True WorkbookName = ThisWorkbook.Name סיום פונקציה

כעת, אם תשנה את שם חוברת העבודה, פונקציה זו תתעדכן בכל פעם שיש שינוי בגליון העבודה או בעת פתיחת חוברת עבודה זו מחדש.

יצירת פונקציה ב- VBA עם ארגומנט אחד

באחד הסעיפים למעלה, כבר ראינו כיצד ליצור פונקציה שלוקחת רק ארגומנט אחד (הפונקציה GetNumeric מכוסה למעלה).

בואו ניצור עוד פונקציה פשוטה הדורשת טיעון אחד בלבד.

פונקציה שנוצרה עם הקוד שלהלן תמיר את הטקסט שהופנה לאותיות רישיות. עכשיו כבר יש לנו פונקציה עבורה ב- Excel, ופונקציה זו היא רק להראות לך כיצד היא פועלת. אם עליך לעשות זאת, עדיף להשתמש בפונקציית ה- UPPER המובנית.

פונקציה ConvertToUpperCase (CellRef כטווח) ConvertToUpperCase = UCase (CellRef) פונקציית סיום

פונקציה זו משתמשת בפונקציה UCase ב- VBA כדי לשנות את הערך של המשתנה CellRef. לאחר מכן הוא מקצה את הערך לפונקציה ConvertToUpperCase.

מכיוון שפונקציה זו דורשת ויכוח, איננו צריכים להשתמש בחלק Application.Volatile כאן. ברגע שהויכוח משתנה, הפונקציה תתעדכן אוטומטית.

יצירת פונקציה ב- VBA עם ארגומנטים מרובים

בדיוק כמו פונקציות גליון עבודה, באפשרותך ליצור פונקציות ב- VBA הדורשות טיעונים מרובים.

הקוד להלן ייצור פונקציה שתחלץ את הטקסט לפני המפריד שצוין. זה דורש שני ארגומנטים - הפניה לתא שיש לה את מחרוזת הטקסט, והמפריד.

פונקציה GetDataBeforeDelimiter (CellRef כטווח, Delim As String) כמחרוזת Dim Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = תוצאה סיום התוצאה

כאשר עליך להשתמש ביותר מארגומנט אחד בפונקציה המוגדרת על ידי משתמש, תוכל לכלול את כל הארגומנטים בסוגריים, להפריד בפסיק.

שים לב כי עבור כל ארגומנט, תוכל לציין סוג נתונים. בדוגמה שלעיל, 'CellRef' הוכרז כסוג נתונים של טווח ו- 'Delim' הוכרז כסוג נתונים של מחרוזת. אם אינך מציין סוג נתונים, VBA מחשיב שמדובר בסוג נתונים משתנה.

כאשר אתה משתמש בפונקציה לעיל בגליון העבודה, עליך לתת להתייחסות התא שיש לה את הטקסט כארגומנט הראשון ואת תו המפריד במרכאות כפולות כארגומנט השני.

לאחר מכן הוא בודק את מיקומו של המפריד באמצעות פונקציית INSTR ב- VBA. מיקום זה משמש לאחר מכן לחילוץ כל התווים לפני המפריד (באמצעות הפונקציה LEFT).

לבסוף, הוא מקצה את התוצאה לפונקציה.

נוסחה זו רחוקה מלהיות מושלמת. לדוגמה, אם תזין תוחם שאינו מצוי בטקסט, זה יתן שגיאה. כעת תוכל להשתמש בפונקציה IFERROR בגליון העבודה כדי להיפטר מהשגיאות, או שתוכל להשתמש בקוד שלהלן המחזיר את הטקסט כולו כאשר הוא אינו מצליח למצוא את התוחם.

פונקציה GetDataBeforeDelimiter (CellRef כטווח, Delim As String) כמחרוזת Dim Dim תוצאה As String Dim DelimPosition כמכלול DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 אם DelimPosition <0 ואז DelimPosition = Len (CellRef) תוצאה = שמאל ( CellRef, DelimPosition) GetDataBeforeDelimiter = פונקציית סיום התוצאה

אנו יכולים לייעל את הפונקציה הזו עוד יותר.

אם תזין את הטקסט (שממנו אתה רוצה לחלץ את החלק לפני המפריד) ישירות בפונקציה, זה יתן לך שגיאה. קדימה … נסה את זה!

זה קורה מכיוון שציינו את 'CellRef' כסוג נתוני טווח.

לחלופין, אם אתה רוצה שהמפריד יהיה בתא ותשתמש בהפניה לתא במקום בקידוד קשיח בנוסחה, אינך יכול לעשות זאת עם הקוד לעיל. זה בגלל שה- Delim הוכרז כסוג נתונים של מחרוזות.

אם אתה רוצה שלפונקציה תהיה הגמישות לקבל קלט טקסט ישיר או הפניות לתאים מהמשתמש, עליך להסיר את הצהרת סוג הנתונים. זה בסופו של דבר יהפוך את הטיעון כסוג נתונים משתנה, שיכול לקחת כל סוג של טיעון ולעבד אותו.

הקוד שלהלן יעשה זאת:

פונקציה GetDataBeforeDelimiter (CellRef, Delim) כתוצאה ממחרוזת עמוקה כמחרוזת Dim DelimPosition כמספר שלם DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 אם DelimPosition <0 ואז DelimPosition = Len (CellRef) תוצאה = שמאל (CellRef, DelimPosition) GetDataBeforeDelimiter = פונקציית סיום התוצאה

יצירת פונקציה ב- VBA עם ארגומנטים אופציונאליים

ישנן פונקציות רבות ב- Excel שחלק מהארגומנטים הם אופציונליים.

לדוגמה, לפונקציית VLOOKUP האגדית יש 3 ארגומנטים חובה וארגומנט אופציונלי אחד.

ארגומנט אופציונלי, כפי שהשם מרמז, הוא אופציונלי לציון. אם לא תציין את אחד הטיעונים החובה, הפונקציה שלך תיתן לך שגיאה, אך אם לא תציין את הארגומנט האופציונלי, הפונקציה שלך תפעל.

אבל טיעונים אופציונליים אינם מועילים. הם מאפשרים לך לבחור מתוך מגוון אפשרויות.

לדוגמה, בפונקציה VLOOKUP, אם אינך מציין את הארגומנט הרביעי, VLOOKUP מבצע חיפוש משוער ואם אתה מציין את הארגומנט האחרון כ- FALSE (או 0), הוא מבצע התאמה מדויקת.

זכור כי הארגומנטים האופציונליים חייבים תמיד לבוא אחרי כל הטיעונים הנדרשים. לא תוכל לקבל ארגומנטים אופציונליים בהתחלה.

כעת נראה כיצד ליצור פונקציה ב- VBA עם ארגומנטים אופציונליים.

פונקציה עם טיעון אופציונלי בלבד

למיטב ידיעתי, אין פונקציה מובנית שלוקחת ארגומנטים אופציונליים בלבד (אני יכול לטעות כאן, אבל אני לא יכול לחשוב על שום פונקציה כזו).

אבל אנחנו יכולים ליצור אחד עם VBA.

להלן קוד הפונקציה שייתן לך את התאריך הנוכחי בפורמט dd-mm-yyyy אם אינך מזין שום ארגומנט (כלומר השאר אותו ריק), ובתבנית "dd mmmm, yyyy" אם אתה מזין משהו כטיעון (כלומר כל דבר כדי שהטיעון לא יהיה ריק).

פונקציה CurrDate (אופציונלי fmt כגרסה) תוצאה עמומה אם IsMissing (fmt) ואז CurrDate = פורמט (תאריך, "dd-mm-yyyy") אחר CurrDate = פורמט (תאריך, "dd mmmm, yyyy") סיום אם סיום פונקציה

שים לב שהפונקציה שלעיל משתמשת ב- 'IsMissing' כדי לבדוק אם הארגומנט חסר או לא. כדי להשתמש בפונקציה IsMissing, הארגומנט האופציונלי שלך חייב להיות מסוג נתונים משתנים.

הפונקציה לעיל פועלת לא משנה מה תזין כטיעון. בקוד, אנו בודקים רק אם הארגומנט האופציונלי מסופק או לא.

אתה יכול להפוך את זה לחזק יותר על ידי קבלת ערכים ספציפיים בלבד כארגומנטים והצגת שגיאה בשאר המקרים (כפי שמוצג בקוד שלהלן).

פונקציה CurrDate (אופציונלי fmt כגרסה) תוצאה עמומה אם IsMissing (fmt) ואז CurrDate = פורמט (תאריך, "dd-mm-yyyy") ElseIf fmt = 1 ואז CurrDate = פורמט (תאריך, "dd mmmm, yyyy") אחר CurrDate = CVErr (xlErrValue) פונקציית סוף אם סוף

הקוד לעיל יוצר פונקציה המציגה את התאריך בתבנית "dd-mm-yyyy" אם לא מסופק ארגומנט, ובפורמט "dd mmmm, yyyy" כאשר הארגומנט הוא 1. זה נותן שגיאה בכל שאר המקרים.

פונקציה עם ארגונים דרושים כמו גם אופציונלי

כבר ראינו קוד שמחלץ את החלק המספרי ממחרוזת.

כעת נסתכל על דוגמה דומה הדורשת ארגומנטים נדרשים וגם אופציונאליים.

הקוד שלהלן יוצר פונקציה שמחלצת את חלק הטקסט מתוך מחרוזת. אם הארגומנט האופציונלי הוא TRUE, הוא נותן את התוצאה באותיות גדולות, ואם הארגומנט האופציונלי הוא FALSE או מושמט, הוא נותן את התוצאה כפי שהיא.

פונקציה GetText (CellRef כטווח, TextCase אופציונלי = False) כמחרוזת Dim StringLength כתוצאה שלמדים שלמים As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) ואז תוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i אם TextCase = True אז תוצאה = UCase (תוצאה) GetText = פונקציית סיום התוצאה

שים לב שבקוד לעיל, אתחלנו את הערך של 'TextCase' כ- False (הסתכל בתוך הסוגריים בשורה הראשונה).

בכך, הבטחנו שהארגומנט האופציונאלי יתחיל בערך ברירת המחדל שהוא FALSE. אם המשתמש מציין את הערך כ- TRUE, הפונקציה מחזירה את הטקסט באותיות גדולות, ואם המשתמש מציין את הארגומנט האופציונלי כ- FALSE או משמיט אותו, הטקסט המוחזר הוא כפי שהוא.

יצירת פונקציה ב- VBA עם מערך כטענה

עד כה ראינו דוגמאות ליצירת פונקציה עם ארגומנטים אופציונליים/נדרשים - כאשר ארגומנטים אלה היו ערך יחיד.

תוכל גם ליצור פונקציה שיכולה לקחת מערך כארגומנט. בפונקציות של גליון עבודה של Excel, ישנן פונקציות רבות שלוקחות ארגומנטים של מערך, כגון SUM, VLOOKUP, SUMIF, COUNTIF וכו '.

להלן הקוד היוצר פונקציה שנותנת את סכום כל המספרים הזוגיים בטווח התאים שצוין.

פונקציה AddEven (CellRef כטווח) תא עמום כטווח עבור כל תא ב- CellRef אם IsNumeric (Cell.Value) ואז אם Cell.Value Mod 2 = 0 ואז התוצאה = תוצאה + Cell.Value סוף אם סוף אם התא הבא AddEven = תוצאה סוף פוּנקצִיָה

אתה יכול להשתמש בפונקציה זו בגליון העבודה ולספק את טווח התאים שיש להם את המספרים כארגומנט. הפונקציה תחזיר ערך יחיד - סכום כל המספרים הזוגיים (כפי שמוצג להלן).

בפונקציה שלעיל, במקום ערך יחיד, סיפקנו מערך (A1: A10). כדי שזה יעבוד, עליך לוודא שסוג הנתונים שלך של הארגומנט יכול לקבל מערך.

בקוד למעלה ציינתי את הארגומנט CellRef כטווח (שיכול לקחת מערך כקלט). תוכל גם להשתמש בסוג הנתונים של הגרסה כאן.

בקוד, יש לולאה לכל אחד שעוברת בכל תא ובודקת אם זה מספר לא. אם זה לא קורה, שום דבר לא קורה וזה עובר לתא הבא. אם זה מספר, הוא בודק אם הוא שווה או לא (באמצעות פונקציית MOD).

בסופו של דבר, כל המספרים הזוגיים מתווספים וסכום זה מוקצה חזרה לפונקציה.

יצירת פונקציה עם מספר בלתי מוגדר של ארגומנטים

בעת יצירת פונקציות מסוימות ב- VBA, ייתכן שלא תדעו את המספר המדויק של ארגומנטים שמשתמש רוצה לספק. אז הצורך הוא ליצור פונקציה שיכולה לקבל ככל שיסופקו טיעונים רבים ולהשתמש בהם להחזרת התוצאה.

דוגמה לפונקציית גליון עבודה כזו היא הפונקציה SUM. אתה יכול לספק לו מספר ארגומנטים (כגון זה):

= SUM (A1, A2: A4, B1: B20)

הפונקציה שלעיל תוסיף את הערכים בכל הארגומנטים הללו. כמו כן, שים לב כי אלה יכולים להיות תא בודד או מערך של תאים.

אתה יכול ליצור פונקציה כזו ב- VBA על ידי הארגומנט האחרון (או שזה יכול להיות הארגומנט היחיד) כאופציונלי. כמו כן, לארגומנט אופציונלי זה יש להקדים את מילת המפתח 'ParamArray'.

'ParamArray' הוא משתנה המאפשר לך לקבל כמה טיעונים שאתה רוצה. שים לב כי השימוש במילה ParamArray לפני ארגומנט הופך את הארגומנט לאופציונלי. עם זאת, אינך צריך להשתמש במילה אופציונלית כאן.

עכשיו בואו ניצור פונקציה שיכולה לקבל מספר שרירותי של ארגומנטים ותוסיף את כל המספרים בארגומנטים שצוינו:

פונקציה AddArguments (ParamArray arglist () כגרסא) עבור כל arg In arglist AddArguments = AddArguments + arg הבא פונקציית סוף הסיום של arg

הפונקציה לעיל יכולה לקחת כל מספר ארגומנטים ולהוסיף ארגומנטים אלה כדי לתת את התוצאה.

שים לב שאתה יכול להשתמש בערך יחיד בלבד, הפניה לתא, בוליאני או ביטוי כארגומנט. אינך יכול לספק מערך כארגומנט. לדוגמה, אם אחד הטיעונים שלך הוא D8: D10, נוסחה זו תיתן לך שגיאה.

אם אתה רוצה להיות מסוגל להשתמש בשני הארגומנטים מרובי התאים, עליך להשתמש בקוד שלהלן:

פונקציה AddArguments (ParamArray arglist () כגרסא) עבור כל arg In arglist עבור כל תא ב- arg AddArguments = AddArguments + תא הבא תא הבא Arg פונקצית סוף

שים לב שנוסחה זו פועלת עם מספר תאים והפניות מערכים, אולם היא אינה יכולה לעבד ערכים או ביטויים מקודדים. אתה יכול ליצור פונקציה חזקה יותר על ידי בדיקה וטיפול בתנאים אלה, אך זו לא הכוונה כאן.

הכוונה כאן היא להראות לך כיצד ParamArray פועל כך שתוכל לאפשר מספר בלתי מוגדר של ארגומנטים בפונקציה. במקרה שאתה רוצה פונקציה טובה יותר מזו שנוצרה על ידי הקוד לעיל, השתמש בפונקציה SUM בגיליון העבודה.

יצירת פונקציה המחזירה מערך

עד כה ראינו פונקציות שמחזירות ערך יחיד.

בעזרת VBA, תוכל ליצור פונקציה המחזירה גרסה שיכולה להכיל מערך שלם של ערכים.

נוסחאות מערך זמינות גם כפונקציות מובנות בגליונות עבודה של Excel. אם אתה מכיר נוסחאות מערך ב- Excel, תדע שהן מוזנות באמצעות Control + Shift + Enter (במקום רק Enter). תוכל לקרוא עוד על נוסחאות מערך כאן. אם אינך יודע על נוסחאות מערך, אל תדאג, המשך לקרוא.

בואו ניצור נוסחה המחזירה מערך של שלושה מספרים (1,2,3).

הקוד שלהלן יעשה זאת.

פונקציה ThreeNumbers () כ- Variant Dim NumberValue (1 עד 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue פונקציית סוף

בקוד לעיל, צייננו את הפונקציה 'שלושת מספרים' כגרסה. זה מאפשר לו להחזיק מערך ערכים.

המשתנה 'NumberValue' מוכרז כמערך בעל 3 אלמנטים. הוא מחזיק את שלושת הערכים ומייחס אותו לפונקציית 'שלושת המספרים'.

באפשרותך להשתמש בפונקציה זו בגליון העבודה על ידי הזנת הפונקציה ולחיצה על מקש Ctrl + Shift + Enter (החזק את המקשים Control ו- Shift ולאחר מכן הקש Enter).

כאשר תעשה זאת, הוא יחזיר 1 בתא, אך במציאות הוא מחזיק בכל שלושת הערכים. כדי לבדוק זאת, השתמש בנוסחה שלהלן:

= MAX (ThreeNumbers ())

השתמש בפונקציה שלמעלה עם Control + Shift + Enter. תבחין שהתוצאה כעת היא 3, מכיוון שהיא הערכים הגדולים ביותר במערך המוחזר על ידי הפונקציה Max, המקבלת את שלושת המספרים כתוצאה מהפונקציה המוגדרת על ידי המשתמש שלנו - ThreeNumbers.

תוכל להשתמש באותה טכניקה ליצירת פונקציה המחזירה מערך של שמות חודש כפי שמוצג בקוד שלהלן:

פונקציה Months () בתור משתנה Dim MonthName (1 עד 12) MonthName (1) = "ינואר" חודש שם (2) = "פברואר" חודש שם (3) = "מרץ" חודש שם (4) = "אפריל" שם חודש (5) = "מאי" MonthName (6) = "יוני" MonthName (7) = "יולי" MonthName (8) = "אוגוסט" MonthName (9) = "ספטמבר" MonthName (10) = "אוקטובר" MonthName (11) = "נובמבר "MonthName (12) =" דצמבר "Months = פונקציית סיום שם חודש

כעת, כאשר אתה מזין את הפונקציה = חודשים () בגליון העבודה של Excel ומשתמש ב- Control + Shift + Enter, הוא יחזיר את כל מערך שמות החודשים. שים לב שאתה רואה רק את ינואר בתא כי זהו הערך הראשון במערך. זה לא אומר שהמערך מחזיר ערך אחד בלבד.

כדי להראות לך את העובדה שהוא מחזיר את כל הערכים, בצע זאת - בחר את התא עם הנוסחה, עבור לשורת הנוסחאות, בחר את כל הנוסחה ולחץ על F9. זה יראה לך את כל הערכים שהפונקציה מחזירה.

אתה יכול להשתמש בזה באמצעות נוסחת INDEX שלהלן כדי לקבל רשימה של כל שמות החודש בבת אחת.

= INDEX (חודשים (), שורה ())

עכשיו אם יש לך הרבה ערכים, זה לא מנהג טוב להקצות ערכים אלה אחד אחד (כפי שעשינו לעיל). במקום זאת, תוכל להשתמש בפונקציית המערך ב- VBA.

אז אותו קוד בו אנו יוצרים את הפונקציה 'חודשים' יתקצר כפי שמוצג להלן:

חודשים פונקציה () כחודשים משתנים = מערך ("ינואר", "פברואר", "מרץ", "אפריל", "מאי", "יוני", _ "יולי", "אוגוסט", "ספטמבר", "אוקטובר" , "נובמבר", "דצמבר") פונקציית סיום

הפונקציה שלמעלה משתמשת בפונקציה Array כדי להקצות את הערכים ישירות לפונקציה.

שים לב שכל הפונקציות שנוצרו למעלה מחזירות מערך אופקי של ערכים. המשמעות היא שאם תבחר 12 תאים אופקיים (נניח A1: L1), והזן את הנוסחה = חודשים () בתא A1, זה ייתן לך את כל שמות החודשים.

אבל מה אם אתה רוצה ערכים אלה בטווח תאים אנכי.

תוכל לעשות זאת באמצעות נוסחת TRANSPOSE בגליון העבודה.

כל שעליך לעשות הוא לבחור 12 תאים אנכיים (רציפים), והזן את הנוסחה שלהלן.

הבנת היקפה של פונקציה מוגדרת משתמש ב- Excel

לפונקציה יכולים להיות שני היקפים - פּוּמְבֵּי אוֹ פְּרָטִי.

• א היקף ציבורי פירוש הדבר שהפונקציה זמינה לכל הגיליונות בחוברת העבודה וכן לכל ההליכים (תת ופונקציה) בכל המודולים בחוברת העבודה. זה שימושי כאשר אתה רוצה לקרוא לפונקציה מתת -שגרה (נראה כיצד זה יתבצע בחלק הבא).
• א היקף פרטי פירוש הדבר שהפונקציה זמינה רק במודול שבו היא קיימת. אינך יכול להשתמש בו במודולים אחרים. אתה גם לא תראה אותו ברשימת הפונקציות בגליון העבודה. לדוגמה, אם שם הפונקציה שלך הוא 'חודשים ()' ואתה מקליד פונקציה ב- Excel (אחרי הסימן =), הוא לא יראה לך את שם הפונקציה. עם זאת, תוכל עדיין להשתמש בו אם תזין את שם הנוסחה.

אם אינך מציין דבר, הפונקציה היא פונקציה ציבורית כברירת מחדל.

להלן פונקציה שהיא פונקציה פרטית:

שם חוברת עבודה של פונקציה פרטית () כ- String WorkbookName = ThisWorkbook.Name סיום פונקציה

אתה יכול להשתמש בפונקציה זו בתת -שורות ובהליכים באותם מודולים, אך אינך יכול להשתמש בה במודולים אחרים. פונקציה זו גם לא תופיע בדף העבודה.

הקוד שלהלן יהפוך את הפונקציה הזו לציבורית. הוא יופיע גם בדף העבודה.

Function WorkNameName () כ- String WorkbookName = ThisWorkbook.Name סיום פונקציה

דרכים שונות לשימוש בפונקציה מוגדרת משתמש ב- Excel

לאחר שיצרת פונקציה מוגדרת משתמש ב- VBA, תוכל להשתמש בה בדרכים רבות ושונות.

בואו נדבר תחילה על אופן השימוש בפונקציות בגליון העבודה.

שימוש ב- UDF בגליונות עבודה

כבר ראינו דוגמאות לשימוש בפונקציה שנוצרה ב- VBA בגליון העבודה.

כל שעליך לעשות הוא להזין את שם הפונקציות, והוא מופיע באינטליסנס.

שים לב שכדי שהפונקציה תופיע בגליון העבודה, היא חייבת להיות פונקציה ציבורית (כפי שמוסבר בחלק לעיל).

תוכל גם להשתמש בתיבת הדו -שיח הוספת פונקציה להוספת הפונקציה המוגדרת על -ידי המשתמש (באמצעות השלבים שלהלן). זה יעבוד רק עבור פונקציות ציבוריות.

• עבור לכרטיסייה נתונים.
• לחץ על האפשרות 'הכנס פונקציה'.
• בתיבת הדו -שיח Insert Function, בחר User Defined כקטגוריה. אפשרות זו מופיעה רק כאשר יש לך פונקציה בעורך VB (והפונקציה ציבורית).
• בחר את הפונקציה מרשימת כל הפונקציות שהוגדרו על ידי הציבור.
• לחץ על הלחצן Ok.

השלבים שלעיל יכניסו את הפונקציה לדף העבודה. הוא מציג גם תיבת דו -שיח פונקציות ארגומנטים שתתן לך פרטים על הארגומנטים והתוצאה.

אתה יכול להשתמש בפונקציה המוגדרת על ידי משתמש בדיוק כמו כל פונקציה אחרת ב- Excel. זה גם אומר שאתה יכול להשתמש בו עם פונקציות Excel מובנות אחרות. לדוגמה. הנוסחה שלהלן תיתן את שם חוברת העבודה באותיות גדולות:

= למעלה (חוברת עבודה ())

שימוש בפונקציות המוגדרות על ידי משתמשים בהליכים ופונקציות VBA

כאשר יצרת פונקציה, תוכל להשתמש בה גם בהליכי משנה אחרים.

אם הפונקציה ציבורית, ניתן להשתמש בה בכל הליך באותו מודול או אחר. אם הוא פרטי, ניתן להשתמש בו רק באותו מודול.

להלן פונקציה המחזירה את שם חוברת העבודה.

Function WorkNameName () כ- String WorkbookName = ThisWorkbook.Name סיום פונקציה

ההליך שלהלן קורא לפונקציה ולאחר מכן מציג את השם בתיבת הודעות.

תת ShowWorkbookName () MsgBox חוברת עבודה שם סוף

ניתן גם לקרוא לפונקציה מפונקציה אחרת.

בקודים להלן, הקוד הראשון מחזיר את שם חוברת העבודה, והשני מחזיר את השם באותיות גדולות על ידי קריאה לפונקציה הראשונה.

Function WorkNameName () כ- String WorkbookName = ThisWorkbook.Name סיום פונקציה

פונקציה WorkbookNameinUpper () WorkbookNameinUpper = UCase (שם חוברת עבודה) פונקציית סיום

קריאה לפונקציה המוגדרת על ידי משתמש מחוברות עבודה אחרות

אם יש לך פונקציה בחוברת עבודה, תוכל לקרוא לפונקציה זו גם בחוברות עבודה אחרות.

ישנן מספר דרכים לעשות זאת:

1. יצירת תוסף
2. פונקציית שמירה בחוברת העבודה המאקרו האישית
3. התייחסות לפונקציה מחוברת עבודה אחרת.

יצירת תוסף

על ידי יצירה והתקנה של תוסף, תהיה לך הפונקציה המותאמת אישית זמינה בכל חוברות העבודה.

נניח שיצרת פונקציה מותאמת אישית - 'GetNumeric' ואתה רוצה אותה בכל חוברות העבודה. לשם כך, צור חוברת עבודה חדשה וקלט את קוד הפונקציה במודול בחוברת עבודה חדשה זו.

כעת בצע את השלבים שלהלן כדי לשמור אותו כתוסף ולאחר מכן התקן אותו ב- Excel.

• עבור ללשונית קובץ ולחץ על שמור בשם.
• בתיבת הדו -שיח שמירה בשם, שנה את סוג 'שמור בשם' ל- .xlam. השם שתקצה לקובץ יהיה שם התוסף שלך. בדוגמה זו הקובץ נשמר בשם GetNumeric.
  • תבחין כי נתיב הקובץ שבו הוא נשמר משתנה באופן אוטומטי. אתה יכול להשתמש בברירת המחדל או לשנות אותו אם תרצה.
• פתח חוברת עבודה חדשה של Excel ועבור לכרטיסייה מפתחים.
• לחץ על האפשרות תוספות Excel.
• בתיבת הדו-שיח תוספות, דפדף ואתר את הקובץ ששמרת ולחץ על אישור.

כעת התוסף הופעל.

כעת תוכל להשתמש בפונקציה המותאמת אישית בכל חוברות העבודה.

שמירת הפונקציה בחוברת עבודה מאקרו אישית

חוברת עבודה מאקרו אישית היא חוברת עבודה נסתרת במערכת שלך שנפתחת בכל פעם שאתה פותח את יישום Excel.

זה מקום שבו אתה יכול לאחסן קודי מאקרו ולאחר מכן לגשת לפקודות מאקרו אלה מכל חוברת עבודה. זה מקום נהדר לאחסן את הפקודות המאקרו שבהן אתה רוצה להשתמש לעתים קרובות.

כברירת מחדל, אין חוברת עבודה מאקרו אישית ב- Excel שלך. עליך ליצור אותו על ידי הקלטת מאקרו ושמירתו בחוברת העבודה של המאקרו האישי.

תוכל למצוא את השלבים המפורטים כיצד ליצור ולשמור פקודות מאקרו בחוברת העבודה המאקרו האישית כאן.

התייחסות לפונקציה מחוברת עבודה אחרת

בעוד ששתי השיטות הראשונות (יצירת תוסף ושימוש בחוברת עבודה מאקרו אישית) יעבדו בכל המצבים, אם ברצונך להתייחס לפונקציה מחוברת עבודה אחרת, חוברת עבודה זו צריכה להיות פתוחה.

נניח שיש לך חוברת עבודה בשם 'חוברת עבודה עם פורמולה, ויש לו את הפונקציה בשם 'GetNumeric '.

כדי להשתמש בפונקציה זו בחוברת עבודה אחרת (בעוד ש חוברת עבודה עם פורמולה פתוח), תוכל להשתמש בנוסחה שלהלן:

= 'חוברת עבודה עם נוסחה'! GetNumeric (A1)

הנוסחה לעיל תשתמש בפונקציה המוגדרת על ידי המשתמש ב חוברת עבודה עם פורמולה קובץ ותן לך את התוצאה.

שים לב שמכיוון שלשם חוברת העבודה יש ​​רווחים, עליך לצרף אותו במרכאות בודדות.

שימוש בהצהרת פונקציות יציאה VBA

אם אתה רוצה לצאת מפונקציה בזמן שהקוד פועל, תוכל לעשות זאת באמצעות משפט 'יציאת פונקציה'.

הקוד שלהלן יחלץ את שלושת התווים המספריים הראשונים ממחרוזת טקסט אלפאנומרית. ברגע שהוא מקבל את שלושת התווים, הפונקציה מסתיימת ומחזירה את התוצאה.

פונקציה GetNumericFirstThree (CellRef כטווח) Long Long StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If J = 3 ואז צא מהפונקציה If IsNumeric (Mid (CellRef, i, 1)) ואז J = J + 1 תוצאה = תוצאה ואמצע (CellRef, i, 1) GetNumericFirstThree = סיום התוצאה אם ​​הפונקציה הבאה i סוף

הפונקציה שלעיל בודקת את מספר התווים שהם מספריים, וכאשר היא מקבלת 3 תווים מספריים, היא יוצאת מהפונקציה בלולאה הבאה.

איתור באגים של פונקציה המוגדרת על ידי משתמש

ישנן מספר טכניקות שבהן תוכל להשתמש בעת ניפוי באגים של פונקציה המוגדרת על ידי משתמש ב- VBA:

איתור באגים של פונקציה מותאמת אישית באמצעות תיבת ההודעות

השתמש בפונקציה MsgBox כדי להציג תיבת הודעות עם ערך מסוים.

הערך שאתה מציג יכול להתבסס על מה שאתה רוצה לבדוק. לדוגמה, אם ברצונך לבדוק אם הקוד מבוצע או לא, כל הודעה תעבוד, ואם ברצונך לבדוק אם הלולאות פועלות או לא, תוכל להציג ערך ספציפי או מונה הלולאה.

איתור באגים של פונקציה מותאמת אישית על ידי הגדרת נקודת השבירה

הגדר נקודת שבירה כדי שתוכל לעבור כל שורה אחת בכל פעם. כדי להגדיר נקודת שבירה, בחר את השורה שבה אתה רוצה אותה והקש F9, או לחץ על האזור האנכי האפור שנותר לשורות הקוד. כל אחת מהשיטות הללו תוסיף נקודת שבירה (תראה נקודה אדומה באזור האפור).

לאחר שהגדרת את נקודת השבר ואתה מבצע את הפונקציה, היא עוברת עד לקו נקודת השבר ואז נעצרת. כעת תוכל לעבור על הקוד באמצעות מקש F8. לחיצה על F8 עוברת פעם אחת לשורה הבאה בקוד.

איתור באגים של פונקציה מותאמת אישית באמצעות Debug.Print בקוד

תוכל להשתמש במשפט Debug.Print בקוד שלך כדי לקבל את הערכים של המשתנים/הארגומנטים שצוין בחלון המיידי.

לדוגמה, בקוד שלהלן, השתמשתי ב- Debug.Print כדי לקבל את הערך של שני משתנים - 'j' ו'תוצאה '

פונקציה GetNumericFirstThree (CellRef כטווח) Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If J = 3 ואז צא מהפונקציה If IsNumeric (Mid (CellRef, i, 1)) ואז J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = תוצאה סוף אם הפונקציה הבאה i סוף

כאשר קוד זה מבוצע, הוא מציג את הדברים הבאים בחלון המיידי.

פונקציות מובנות של Excel vs. פונקציה מוגדרת על ידי משתמש

ישנם מעט יתרונות חזקים בשימוש בפונקציות מובנות של Excel על פני פונקציות מותאמות אישית שנוצרו ב- VBA.

• פונקציות מובנות מהירות הרבה יותר מפונקציות VBA.
• כאשר אתה יוצר דוח/לוח מחוונים באמצעות פונקציות VBA ושולח אותו ללקוח/עמית, הם לא יצטרכו לדאוג אם הפקודות המאקרו מופעלות או לא. במקרים מסוימים, לקוחות/לקוחות נבהלים מלראות אזהרה בסרגל הצהוב (שפשוט מבקש מהם לאפשר פקודות מאקרו).
• עם פונקציות Excel מובנות, אינך צריך לדאוג לגבי סיומות קבצים. אם יש לך פקודות מאקרו או פונקציות שהוגדרו על ידי המשתמש בחוברת העבודה, עליך לשמור אותו ב- .xlsm.

אמנם יש הרבה סיבות חזקות להשתמש בפונקציות מובנות של Excel, אך בכמה מקרים עדיף שתשתמש בפונקציה המוגדרת על ידי המשתמש.

• עדיף להשתמש בפונקציה המוגדרת על ידי משתמש אם הנוסחה המובנית שלך ענקית ומסובכת. זה הופך לרלוונטי עוד יותר כאשר אתה צריך מישהו אחר שיעדכן את הנוסחאות. לדוגמה, אם יש לך נוסחה ענקית המורכבת מפונקציות רבות ושונות, אפילו שינוי הפניה לתא יכול להיות מייגע וחשוף לשגיאות. במקום זאת, באפשרותך ליצור פונקציה מותאמת אישית הדורשת רק טיעון אחד או שניים ועושה את כל המאמצים הכבדים.
• כשאתה צריך לעשות משהו שאי אפשר לעשות אותו על ידי פונקציות מובנות של Excel. דוגמה לכך יכולה להיות כאשר רוצים לחלץ את כל התווים המספריים ממחרוזת. במקרים כאלה, היתרון בשימוש בפונקציה המוגדרת על ידי המשתמש עולה על השליליות שלה.

היכן לשים את קוד VBA עבור פונקציה המוגדרת על ידי משתמש

בעת יצירת פונקציה מותאמת אישית, עליך להכניס את הקוד לחלון הקוד של חוברת העבודה שבה אתה רוצה את הפונקציה.

להלן השלבים להכנסת הקוד לפונקציה 'GetNumeric' בחוברת העבודה.

1. עבור לכרטיסייה מפתחים.
2. לחץ על האפשרות Visual Basic. פעולה זו תפתח את עורך ה- VB בקצה האחורי.
3. בחלונית סייר הפרויקט בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אובייקט עבור חוברת העבודה שבה ברצונך להכניס את הקוד. אם אינך רואה את סייר הפרוייקטים עבור לכרטיסייה תצוגה ולחץ על סייר הפרוייקטים.
4. עבור אל הוספה ולחץ על מודול. זה יכניס אובייקט מודול לחוברת העבודה שלך.
5. העתק והדבק את הקוד בחלון המודול.

אולי תאהב גם את מדריכי ה- VBA הבאים של Excel:

• עבודה עם תאים וטווחים ב- Excel VBA.
• עבודה עם גליונות עבודה ב- Excel VBA.
• עבודה עם חוברות עבודה באמצעות VBA.
• כיצד להשתמש בלולאות ב- Excel VBA.
• אירועי Excel VBA - מדריך קל (ומלא)
• שימוש בהצהרות IF Then Else ב- VBA.
• כיצד להקליט מאקרו ב- Excel.
• כיצד להריץ מאקרו ב- Excel.
• כיצד למיין נתונים ב- Excel באמצעות VBA (מדריך שלב אחר שלב).
• פונקציית VBA InStr של Excel - מוסברת עם דוגמאות.