קורס Excel VBA – המדריך המלא Excel VBA

מאקרו ומבוא ל-VBA

VBA (Visual Basic for Applications) היא התאמה של שפת הקוד Visual Basic לתוכנות חבילת ה-Office (Excel, Word, Power Point וכו') המאפשרת לגשת אל "מאחורי הקלעים" של התכנה ולתכנת תכונות נוספות מותאמות אישית ולהרחיב את היכולות של התוכנות. שפת ה- VBA מותאמת בנפרד לכל אחת מהתוכנות, ול-Excel בפרט.

המטרות העיקריות בתכנות באמצעות ה-VBA הן:

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

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

         מאקרו – רצף של פעולות מאוחדות לתהליך אחד. את רצף זה ניתן להקליט ולשמור כפקודת הרצה אחת.

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

הקלטת מאקרו

הוספת תפריט "מפתחים"

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

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

1. תפריט קובץ
2. אפשרויות
3. תת-תפריט התאמה אישית של רצועת הכלים
4. סימון V בתיבת הסימון של רצועת מפתחים
5. אישור

מיד יתווסף תפריט מפתחים לרצועת הכלים:

הקלטת מאקרו חדש

הוספת תפריט "מפתחים"

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

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

1. תפריט קובץ
2. אפשרויות
3. תת-תפריט התאמה אישית של רצועת הכלים
4. סימון V בתיבת הסימון של רצועת מפתחים
5. אישור

מיד יתווסף תפריט מפתחים לרצועת הכלים:

הקלטת מאקרו חדש

הקלטת רצף פקודות חדש ניתן להתחיל ממספר מקומות בתוכנה:

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

לחצן הקלט מאקרו בשורת הסטטוס:

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

פקודות מאקרו בתפריט תצוגה:

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

לחצן הקלט מאקרו בתפריט מפתחים:

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

לאחר הלחיצה, תפתח תיבת דוח שיח הקלטת מאקרו:

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

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

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

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

5. אישור.

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

ההקלטה אינה רושמת את הבחירות והסימונים שהיו לפני תחילת ההקלטה.

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

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

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

זהו תכנון האלגוריתם הפשוט.

שימו לב! רצף זה אינו כולל בחירה של תאי הכותרת, כלומר:

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

על מנת לשלב בחירה וסימון תאים במאקרו ר' סעיף הקלטה יחסית והקלטה מוחלטת.

הקלטה יחסית והקלטה מוחלטת

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

• ברצוננו שההקלטה תתייחס לבחירת התאים באופן ספציפי – הקלטה מוחלטת.
  (לדוגמא: בחירת תא 3A או בחירת טווח 15E:6C).

• או כתזוזה יחסית מהמיקום המסומן כרגע – הקלטה יחסית.
  (לדוגמא: לחיצה על תא 3A בזמן ההקלטה, לא תירשם כבחירת תא 3A, אלא כבחירה בתא הנמצא שני תאים למטה, אם התא המסומן בעת ההקלטה היה 1A למשל).

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

לאחר הלחיצה, לחצן זה יקבל רקע כהה, לזיהוי מופעל או מכובה.

בהמשך לרצף הפעולות שיצרנו בסעיף הקודם

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

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

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

לפיכך:

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

גיליון לדוגמא

להלן גיליון לדוגמא עליו נקליט את רצף הפעולות הנ"ל:

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

שלב א' – נבחר את טווח הכותרת שנרצה לעצב:

שלב ב' – נפעיל את תיבת הדו-שיח הקלטת מאקרו ונזין את השדות הדרושים (ר' סעיף הקלטת מאקרו חדש):

שלב ג' – נבצע את פעולות 1-4. יש להימנע מתוספת פעולות בגיליון שאינן מפורטות, כמו לחיצה על תאים, גלילה מיותרת וכיו"ב:

שלב ד' – נלחץ על הלחצן השתמש בהפניות יחסיות כדי שהקלטת המעבר לתא הסמוך תהיה יחסית ולא קבועה לתא 1G, כפי שנדרש בסעיף 5. את המעבר לתא הסמוך נבצע באמצעות לחיצה על מקש החץ השמאלי במקלדת (במקרה הצורך, יש לשלב את מקשי החיצים עם צירוף המקשים Ctrl או Shift כדי להגיע לקצוות הטווח):

שלב ה' – נקליט את סעיפים 5-8. יש להימנע מתוספת פעולות בגיליון שאינן מפורטות, כמו לחיצה על תאים, גלילה מיותרת וכיו"ב:

שלב ו' – נלחץ על הלחצן השתמש בהפניות יחסיות על מנת לבטל את ההקלטה היחסית, וכדי שהקלטת סימון תא 1A תהיה מוחלטת, כפי שנדרש בסעיף 9. לאחר מכן נלחץ על תא 1A ובכך נסיים את רצף הפקודות.

שלב ז' – עצירת ההקלטה.

הרצת מאקרו

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

• באמצעות תיבת הדו-שיח מאקרו.
• באמצעות קיצור מקשים – אם הוקצה מראש בעת הגדרות ההקלטה.
• באמצעות לחצנים מותאמים אישית.

תיבת הדו-שיח מאקרו:

1. תפריט מפתחים
2. לחצן פקודות מאקרו
3. בחירה במאקרו הרצוי
4. הפעל

בתיבת הדו-שיח מאקרו תופיע רשימת כל הקלטות המאקרו והפקודות שתוכנתו ב-VBA:

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

הוספת לחצן מותאם אישית

ניתן להוסיף לחצן מותאם אישית להפעלת המאקרו בארבעה אופנים שונים:

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

הוספת לחצן בסרגל הכלים לגישה מהירה

1. קובץ
2. אפשרויות
3. סרגל כלים לגישה מהירה
4. בשדה בחר פקודות מתוך יש לבחור בפקודות מאקרו ברשימה הנפתחת:

5. ברשימת פקודות המאקרו שתופיע, יש לבחור בפקודה הרצויה וללחוץ על הוסף. הפקודה תתווסף לרשימת פקודות בסרגל הכלים לגישה מהירה:

6. אישור

הפקודה החדשה תופיע בסרגל כלים לגישה מהירה:

הוספת לחצן בתפריט

1. קובץ
2. אפשרויות
3. התאמה אישית של רצועת הכלים
4. בשדה בחר פקודות מתוך יש לבחור בפקודות מאקרו ברשימה הנפתחת:

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

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

7. יש לבחור בפקודת המאקרו הרצויה מתוך רשימת פקודות המאקרו וללחוץ על הוסף:

8. אישור

הפקודה תתווסף לתפריט אותו בחרנו ברצועת הכלים:

הפיכת אובייקט ללחצן

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

1. לחיצה במקש ימני על האובייקט הרצוי. יפתח תפריט מהיר לאובייקט זה:

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

3.בחירה במאקרו הרצוי ולחיצה על אישור

הפיכת פקד טופס ללחצן

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

1. מפתחים
2. לחיצה על הוסף תפתח רשימת פקדי טופס לבחירה.
3. יש לבחור בלחצן (פקד טופס):

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

5. אישור

צפייה בקוד המאקרו המוקלט ועריכתו

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

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

• דרך תיבת הדו-שיח מאקרו
• ישירות דרך עורך ה-VBA.

1. תפריט מפתחים (או תצוגה)
2. פקודות מאקרו
3. בחירת המאקרו הרצוי.
4. ערוך

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

מבנה ממשק עורך ה-VBA

ממשק העורך, בנוי בצורה הקלאסית בדומה לשאר תוכנות ה-Office:

I. שורת הכותרת – מציגה את שם הפרויקט (חוברת העבודה) בעריכה הנוכחית.

II. שורת התפריטים – כל פקודות ואפשרויות התוכנה מחולקות לקטגוריות.

III. סרגלי כלים – קיצורים בצלמיות לפקודות השימושיות ביותר מתוך התפריטים.

IV. איזור העבודה – שטח העריכה והכתיבה של הקודים והתוכן, בחלונות הרצויים.

V. סייר הפרויקטים – חלונית ניווט לבחירת פרויקט, חוברת עבודה או מודול לעריכה.

התמצאות במבנה קוד המאקרו

הקוד במופיע באיזור העבודה כתוב ברמת אובייקט מסוים. אובייקט יכול להיות:

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

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

הקלטת המאקרו תאוחסן בתוך מודול, וחלון המודול יפתח כאשר תפתח פקודת המאקרו לעריכה (ר' סעיף צפייה בקוד המאקרו המוקלט ועריכתו):

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

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

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

בדוגמה הנ"ל ניתן לראות:

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

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

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

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

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

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

הרצת המאקרו שורה אחר שורה

ניתן להריץ את המאקרו שורה אחר שורה בשתי דרכים:

• דרך תיבת הדו-שיח מאקרו.
• דרך תפריט Debug בעורך ה-VBA.

תיבת הדו-שיח מאקרו

1. תפריט מפתחים
2. לחצן פקודות מאקרו
3. בחירה במאקרו הרצוי.
4. צעד לתוך

תפריט Debug בעורך

1. הצב את הסמן בנקודה כלשהי בתוך קוד המאקרו המוקלט
2. תפריט Debug
3. Step Into (או לחיצה על מקש 8F במקלדת)

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

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

שולי איזור העריכה של הקוד:

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

אפשרויות עריכה לפקודת מאקרו

הערות

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

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

כדי להוסיף שורות הערות לקוד, או להפוך שורות קיימות בקוד להערות, יש להוסיף גרש עליון (') בתחילת השורה:

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

הזחות

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

1. באמצעות מקש הרווח ניתן לבצע הזחה של תו אחד בכל לחיצה. באמצעות מקש ה-Backspace ניתן לבצע יציאה של תו אחד בכל לחיצה.
2. באמצעות מקש ה-Tab ניתן לבצע הזחה של ארבעה תווים בכל לחיצה. באמצעות צירוף המקשים Shift + Tab ניתן לבצע יציאה של ארבעה תווים בכל לחיצה.

פיצול שורות

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

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

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

השלמה אוטומטית

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

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

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

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

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

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

לניהול אפשרויות ההשלמה האוטומטית, יש לסמן או לבטל את הסימון בין תיבות הבחירה בכרטיסיה Editor, שבחלון Options. את חלון זה ניתן לבחור בתפריט Tools.

אובייקטים, שיטות ומאפיינים בסיסיים

אובייקטים

שיטות

מאפיינים

חישובים

ניתן להשתמש בכוחו של האקסל לביצוע חישובים.

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

הצבת התוצאה – שורת הקוד תבצע את החישוב, של הנוסחה או הפונקציה, וההצבה הסופית בתא תציג את התוצאה בלבד. בשורת הנוסחאות ב-Excel יופיע רק ערך התוצאה.

הצבת החישוב – שורת הקוד תציב את החישוב כולו, של הנוסחה או הפונקציה, בתא. בשורת הנוסחאות ב-Excel יופיע כל מבנה החישוב של הנוסחה ו/או הפונקציה.

הצבת התוצאה

בכל אחת מהדוגמאות הבאות, תוצב התוצאה הסופית ולא הנוסחה, בתאים הנבחרים בגיליון:

• נוסחה – בתא 1A יוצב המספר 8.
• נוסחה עם הפנייה מוחלטת – בתא הפעיל בגיליון יוצב המספר שהוא תוצאת המכפלה של הערך בתא 5B והמספר 3.
• נוסחה עם הפנייה יחסית – בתא 8C יוצב המספר שהוא תוצאות החיבור של הערך בתא הפעיל בגיליון והתא שמתחתיו.
• פונקציה – בתא 6D יוצב המספר שהוא תוצאת הפונקציה MAX(הערך המקסימלי בטווח נתון) על הטווח 8C:2A.
  כלומר, ניתן לבצע חישובים באמצעות מאגר הפונקציות של התוכנה: בהתאם, האובייקט הוא Application (אפליקציית ה-(Excel והמאפיין שלו WorksheetFunction לשימוש בפונקציה מהמאגר. לאחר מכן ניתן לבחור את הפונקציה הרצויה: בדוגמה הנוכחית MAX.

הצבת החישוב

לשם כך ניתן להשתמש במספר מאפיינים כמו: Value, Value2, Formula, FormulaR1C1, טקסט ועוד. השימוש במאפיינים אלו תלוי בסוג הנתון הרצויים להצבה בתא. עוד אודות סוגי נתונים ר' 40. בחוברת זו נתמקד במאפיין Value.

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

לדוגמה:

• נוסחה – בתא הפעיל בגיליון תוצב הנוסחה המכפילה את המספר 3 בערך שבתא 5B.
• פונקציה – בתא 3J תוצב הפונקציה PMT עם הארגומנטים המופיעים שבדוגמה.

משתנים

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

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

למשל, ניתן לשלב במהלך רצף הפקודות את שורת הקוד:

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

כללים לשם המשתנה:

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

הכרזה על משתנים

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

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

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

• Dim (מהמילה Dimension, ממדים) – לפתיחת ההכרזה על משתנה.
• שם המשתנה – Mish בדוגמה הנוכחית.
• סוג המשתנה – String: לנתונים מסוג מחרוזות טקסטואליות.

ישנם סוגים רבים של משתנים. להלן מספר סוגים עיקריים:

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

String – לאחסנת מחרוזות תווים.

Date – לאחסנת תאריכים.

Byte – לאחסנת מספרים בלבד 0 עד 255.

Integer – לאחסנת מספרים בלבד בין 32,768- עד 32,768.

Long – לאחסנת מספרים בלבד בין 2,147,483,648- עד 2,147,483,648.

Range – לאחסנת טווחים.

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

1. תפריט Tools בעורך ה-VBA.
2. Options.
3. יש לסמן את תיבת הבחירה Require Variable Declaration בלשונית Editor.

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

השורה הראשונה פוקדת, שהערך בתא מתחת לתא הפעיל, יהיה הטקסט "Total Bill".

השורה השנייה פוקדת, שבתוך המשתנה Total_Price יוצב הערך של התא הפעיל.

השורה השלישית פוקדת, שבתוך המשתנה Vat_Prod יוצב הערך שהוא תוצאת המכפלה בין הערך שבתא 1A והערך של המשתנה Total_Price (שבו כאמור הוצב הערך שבתא הפעיל, לפי שורת הקוד הקודמת).

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

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

חלונית Locals

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

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

מבנה With

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

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

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

למשל, בקוד הבא ניתן לראות חזרתיות רבה על האובייקט ActiveCell לצורך הגדרות רבות. הפקודות האחרונות בקוד מציגות אפילו חזרתיות של ה- Activecell.Font:

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

את המבנה יש לסגור עם הפקודה End With.

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

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

עבודה עם תאריכים ושעות

כללי בסיס

• תוכנת ה-Excel מכירה בתאריכים החל מה-01/01/1900.
• תאריכים ושעות מאוחסנים כמספרים עשרוניים. המספר 1 מייצג את ה-01/01/1900 וכל יום נוסף במניין הימים מיוצג ע"י המספר העוקב. למשל, המספר 30 מייצג את ה-30/01/1900 והמספר 3.5 מייצג את ה-03/01/1900 בשעה 12:00 בצהריים (מחצית היום).
• בשל היותם של תאריכים סוג של עיצוב מספרים (ההמרה לתאריך או למספר יכולה להתבצע בחלונית הדו-שיח עיצוב תאים) ניתן לשלב אותם בחישובים. למשל הנוסחה 05/04/2026 + 3 תחזיר את התוצאה 08/04/2026, כי כאמור כל מספר מייצג תוספת של מניין בימין, ולכן התווספו שלושה ימים.
• כדי להזין בקוד ה-VBA תאריך בפורמט פשוט ולא כמספר, יש לתחום את ביטוי התאריך או השעה באמצעות הסימן #. למשל:

Date_VAR = #03/09/2020#

Time_VAR = #08:30:25 PM#

• להכרזה על משתנה התואם לתאריך או שעה, יש לבחור בסוג Date. למשל:

Dim {Variable} as Date

פקודות נפוצות

• הפקודה Date ברצף הקוד (בשונה מסוג משתנה בהכרזה) מחזירה את התאריך הנוכחי של היום, בדומה לפונקציה Today() בתוכנה.
• הפקודה Now ברצף הקוד מחזירה את השעה הנוכחית, בדומה לפונקציה Now() בתוכנה.
• קוד ה-VBA כולל פונקציות פנימיות המאפשרות לבצע חישובים. פונקציות אלו אינן קשורות לסל הפונקציות החישוביות הקיימות בתוכנה, שכאמור, כדי לעשות בהן שימוש צריך לקרוא למאפיין WorksheetFunction של האובייקט Application (ר' עמ' 36). פונקציות נפוצות לעבודה עם תאריכים ושעות:
  • Day() – לחילוץ היום מתוך תאריך נתון. למשל, הפקודה Day(03/09/2028) תחזיר 3.
  • Month() – לחילוץ התאריך מתוך תאריך נתון. למשל, הפקודה Month(03/09/2028) תחזיר 9.
  • Year() – לחילוץ השנה מתוך תאריך נתון. למשל, הפקודה Year(03/09/2028) תחזיר 2028.

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

עבודה עם מחרוזות

כללי בסיס

• בציון מחרוזת טקסט יש להשתמש בגרשיים כפולים בתחילת ובסוף המחרוזת. למשל:

NewInput = "shalom"

• לחיבור מחרוזות ניתן להשתמש בסימן החיבור &. למשל:

Range("D9").Value = NewInput & " " & Application.Username & " and welcome"

פקודות נפוצות

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

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

• Len(String) – להחזרת אורך המחרוזת, כלומר מספר התווים במחרוזת.
• Left() \ Right(String, No. of characters) – לחילוץ חלק ממחרוזת, מהצד הימני או השמאלי, לפי מספר תווים נתון.
• Mid(String, Point of start, Character No. of characters) – חילוץ חלק ממחרוזת, החל ממספר תו נתון ועד מספר תווים נתון.
• Instr(String, Inner String) החזרת מקומה של מחרוזת בתוך מחרוזת.
• Replace(String, Find, Replacement) – החלפת מחרוזת פנימית במחרוזת אחרת.

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

פונקציות מותאמות אישית

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

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

מבנה קוד לפונקציה

המבנה של קוד ליצירת פונקציה, שונה ממבנה קוד לריצת רוטינה:

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

Function Example(A, B, C)

Example = (A + B) * C

End Function

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

TotalRate – שם הפונקציה

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

Nper – ארגומנט של הפונקציה. כמות התשלומים הכוללת.

PV – ארגומנט של הפונקציה. גובה ההלוואה הכולל.

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

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

תנאי If

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

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

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

מבנה If

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

להלן מבנה If:

אין חובה להגדיר רצף פקודות במקרה של False. במקרה זה, אין להגדיר Else ורצף פקודות שאחריו. לדוגמה:

הצורך בהגדרת End IF קיים רק אם אחרי Then או Else קיים בלוק של פקודות. אם מוגדרת רק פקודה אחת לביצוע מיד אחרי  Thenבהמשך אותה השורה, אין לציין End If. אם הפקודה האחת לביצוע כתובה בשורה מתחת לשורת ה-Then (או מספר פקודות) אז חובה לציין End If. לדוגמה:

בדוגמה הבאה ניתן לראות טבלת נבחנים והציונים שלהם:

מטרת הקוד הבא היא לצבוע באדום את הטבלה אם 40% ויותר מהנבחנים קיבלו פחות מ-60 בבחינה (כלומר, נכשלו):

GradesCount – משתנה שמוכנס לתוכו מספר הנבחנים בקורס. החישוב מתבצע באמצעות הפונקציה Count() ממאגר הפונקציות שבתוכנה.

1. PassCount– משתנה שמוכנס לתוכו מספר הנבחנים שעברו את הציון 60. החישוב מתבצע באמצעות הפונקציה CountIf() ממאגר הפונקציות שבתוכנה.
2. כדי לבדוק אם מספר הנבחנים שעברו את הציון 60 (PassCount) הוא גדול מ-40% מתוך כלל הנבחנים (GradesCount), מופיע תנאי If במבנה ללא Else. אם התנאי מתקיים צבע הרקע של כל התאים בטבלה יהיה אדום.ניתן לראות את תת האובייקט CurrentRegion שמסמן את כל התאים המצויים בטווח נתון, ובמקרה הנוכחי תא 1A. CurrentRegion שקול לפקודה המתקבלת בצירוף המקשים Ctrl + A בתוכנה, לאחר בחירת תא או טווח.
3. בתא שנמצא שני תאים מתחת לתא האחרון בטבלה יוזן הביטוי Course Average.
4. בתא שנמצא שלושה תאים מתחת לתא האחרון בטבלה יוזן הממוצע של ציוני הסטודנטים. החישוב מתבצע באמצעות הפונקציה Average() ממאגר הפונקציות שבתוכנה.

תוצאת הרצת הקוד על הטבלה הנתונה בדוגמה:

תנאי מקונן ו-ElseIf

כדי להוסיף בדיקה של תנאי נוסף, למקרה שתוצאת התנאי הראשי היא False, יש להחליף את הביטוי Else בביטוי ElseIf. ביטוי זה מתפקד כמבנה If פנימי (כלומר, בתוך מבנה ה-If הראשי), ובהתאם הוא מכיל את הביטויים Then, Else ו-End If (End If פנימי נדרש רק במקרה שלאחר התנאי ישנו רצף של יותר מפקודה אחת).

הדוגמה הבאה מרחיבה את התנאי שבדוגמה הקודמת: אם פחות מ-40% מהנבחנים קיבלו 60, כלומר תוצאת התנאי הראשי היא False, אז תתבצע בדיקה של תנאי נוסף – האם ממוצע הציונים של כל הנבחנים קטן מ-70. אם התשובה היא True אז הטבלה תצבע בצהוב. אם התשובה היא False, כלומר, אם ממוצע הציונים הכולל גדול מ-70 ופחות מ-40% מהתלמידים קיבלו פחות מ-60, לא יחול שום שינוי בטבלה.

GradesAverage – משתנה שמוכנס לתוכו ממוצע הציונים בקורס. החישוב מתבצע באמצעות הפונקציה Average() ממאגר הפונקציות שבתוכנה.

תוצאת הרצת הקוד על הטבלה הנתונה בדוגמה:

האופרטורים המתמטיים לבדיקת תנאי

<          גדול

=<        גדול או שווה

>          קטן

=>        קטן או שווה

=          שווה

<>        שונה

NOT    בודק אם תנאי אינו מתקיים. לדוגמא, התנאי If NOT Application.UserName = "Carmel" זהה לתנאי If Application.UserName <> "Carmel".

האופרטורים AND ו-OR

AND ו-OR הם אופרטורים נוספים, המאפשרים בדיקה של מספר תנאים במקביל ביחסים של חיתוך או הוספה (או / וגם).

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

• OR – ביצוע בדיקה של שני תנאים ויותר במקביל. מספיק שתוצאת אחד מהתנאים היא True כדי שתוצאת כל הבדיקה תהיה True. למשל:

A = 1

B = 2

C = 3

If A =1 0 OR B = 2 OR C = 30

בדוגמה זו שני תנאים לא מתקיימים, אך תנאי אחד כן מתקיים (B = 2), כלומר תוצאת כל שורת הבדיקה היא True ולכן הקוד ימשיך לשלב ה-Then ורצף הפקודות שלו.

• AND – ביצוע בדיקה של שני תנאים ויותר במקביל. תוצאת כל אחד מהתנאים בנפרד חייבת להיות True כדי שתוצאת כל הבדיקה תהיה True. למשל:

A = 1

B = 2

C = 3

If A =1 0 AND B = 2 AND C = 30

בדוגמה זו שני תנאים לא מתקיימים, כלומר תוצאת כל שורת הבדיקה היא False ולכן הקוד ימשיך לשלב ה-Else ורצף הפקודות שלו, אם הוא קיים, או יקפוץ לסיום המבנה ב-End If.

ניתן לשלב את האופרטורים AND ו-OR גם לבדיקות מורכבות, כלומר, לשילוב של שני האופרטורים בבדיקה אחת. למשל:

בדוגמה זו התוצאה תהיה True.

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

• (A = 1 AND B = 2)– ביטוי זה נמצא בתוך סוגריים כך שהוא נבדק כביטוי אחד, על אף שבתוכו מתבצעת בדיקה בין שני ביטויים. תוצאת הבדיקה הפנימית היא True ולכן כל הביטוי הממוסגר הוא True.
• C = 30. ביטוי זה הוא False.
• A + C < 3. ביטוי זה הוא False.

כיוון שהביטוי הראשון הוא True, תוצאת כל הבדיקה היא True.

דילוג על פקודות

Go To

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

לתיוג שלב רצוי בקוד יש לכתוב שם רצוי ואחריו נקודתיים. למשל, Stage1: או ConditionLocation:.

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

בדוגמה הבאה ניתן לראות טבלת נבחנים והציונים שלהם:

מטרת הקוד הבא היא לצבוע באדום את הטבלה אם 40% ויותר מהנבחנים קיבלו פחות מ-60 בבחינה (כלומר, נכשלו). אם פחות מ-40% מהנבחנים קיבלו 60, כלומר תוצאת התנאי הראשי היא False, אז תתבצע בדיקה של תנאי נוסף – האם ממוצע הציונים של כל הנבחנים קטן מ-70. אם התשובה היא True אז הטבלה תצבע בצהוב. אם התשובה היא False, כלומר, אם ממוצע הציונים הכולל גדול מ-70 כי פחות מ-40% מהתלמידים קיבלו ציון נמוך מ-60, לא יחול שום שינוי בטבלה:

GradesCount – משתנה שמוכנס לתוכו מספר הנבחנים בקורס. החישוב מתבצע באמצעות הפונקציה Count() ממאגר הפונקציות שבתוכנה.

1. PassCount– משתנה שמוכנס לתוכו מספר הנבחנים שעברו את הציון 60. החישוב מתבצע באמצעות הפונקציה CountIf() ממאגר הפונקציות שבתוכנה.
2. GradesAverage – משתנה שמוכנס לתוכו ממוצע הציונים בקורס. החישוב מתבצע באמצעות הפונקציה Average() ממאגר הפונקציות שבתוכנה.
3. כדי לבדוק אם מספר הנבחנים שעברו את הציון 60 (PassCount) הוא גדול מ-40% מתוך כלל הנבחנים (GradesCount), מופיע תנאי If במבנה ללא Else. אם התנאי מתקיים צבע הרקע של כל התאים בטבלה יהיה אדום.ניתן לראות את תת האובייקט CurrentRegion שמסמן את כל התאים הסמוכים לטווח נתון, ובמקרה הנוכחי תא 1A. CurrentRegion שקול לפקודה המתקבלת בצירוף המקשים Ctrl + A בתוכנה, לאחר בחירת תא או טווח.
4. בתא שנמצא שני תאים מתחת לתא האחרון בטבלה יוזן הביטוי Course Average.

תוצאת הרצת הקוד על הטבלה הנתונה בדוגמה:

הדוגמה הבאה מרחיבה את הקוד ומתווספת בדיקה מקדימה, לפיה אם ממוצע הציונים גדול מ-73, ניתן לדלג על הבדיקה של ה-IF המקונן והתוצאות הצבעוניות שיחולו על הטבלה, ישירות לשלבים האחרונים של הקוד:

ניתן לראות את התגית Summary: לפני שתי הפקודות האחרונות של הקוד, ואת ההפניה אליה בתנאי If הראשון לאחר הפקודה GoTo.

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

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

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

תוצאת הרצת הקוד תהיה:

אוסף של אובייקטים

Collection הוא אוסף של אובייקטים.

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

Sheets("Sheet3") – פנייה לשם הגיליון. מכאן ניתן ללמוד שלכל אובייקט באוסף ניתן לתת שם.

Sheets(3) – פנייה לפריט השלישי באוסף הגיליונות. מכאן ניתן ללמוד שהאובייקטים המצויים באוסף מאורגנים בצורה כרונולוגית ומקבלים גם את המספר הכרונולוגי שלהם באוסף.

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

יצירת אוספים מותאמים אישית

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

למשל, Collection של רכבים והתכונות שלהם: האוסף יקרא Cars וכל אובייקט בתוכו יהיה מסוג Car. לאובייקטים מסוג זה יש מאפיינים ושיטות משלהם:

X = Cars(1).Model                                          שורת קוד זו תכניס אל תוך המשתנה X את ההגדרה של מאפיין Model של האובייקט הראשון באוסף Cars. כלומר, לכל אובייקט מסוג Car יש מספר מאפיינים, אשר אחד מהם הוא שם המודל של הרכב.

Debug.Print Cars(3).Paint                              הפקודה Debug מאפשרת לבדוק הצהרות לצורך ניפוי באגים בקוד. אחת האפשרויות לעשות זאת היא באמצעות הדפסה ((Print של תוצאת ההצהרה אל תוך חלונית התצוגה Immediate Window. את חלונית זו ניתן להציג דרך בחירתה בתפריט View. שורת קוד הזו תציג את הביטוי המשויך למאפיין של צבע הרכב השלישי באוסף.

Range("A1").Value = Cars(6).ManYear         שורת קוד זו תזין לערך התא 6A את שנת הייצור של האובייקט השישי באוסף.

ליצירת Collection חדש יש להכריז עליו בתחילת הקוד באופן הבא:

Dim {Collection's Name} As New Collection

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

ניתן לראות בדוגמה גם פקודות נפוצות נוספות:

• Remove – להסרת פריט מהאוסף. אחריה יש לציין את מספר הפריט הרצוי להסרה.
• Count – לספירט כמות הפריטים באוסף.
• Before \ After – למיקום פריטים חדשים במקום רצוי בסדר הכרונולוגי של הפריטים

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

מודול עריכה Class Module

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

הוספת Class Module

הוספת Class Module חדש מתבצעת דרך תפריט Insert. מודול עריכה זה כולו מייצג את האובייקט החדש ובתוכו יש להכריז על המאפיינים הרצויים של האובייקט. לשם כך יש להעניק ל-Class Module את שם האובייקט החדש באמצעות חלונית Properties בעורך ה-VBA:

1. בחירת Class Moule בסייר הפרויקטים או יצירת Class Module חדש באמצעות תפריט Insert.
2. בשדה (Name) בחלונית המאפיינים יש להגדיר את שם האובייקט. אם חלונית המאפיינים אינה מופיעה, יש להוסיפה באמצעות תפריט View.

הדוגמה הבאה מציגה יצירת אובייקט חדש מסוג Car:

יצירת מאפיינים לאובייקט חדש ב-Class Module

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

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

• Public – לפתיחת ההכרזה על המאפיין כציבורי (ר' עמ' 100).
• שם המאפיין – Model הוא המודל של הרכב, Paint הוא הצבע של הרכב ו-ManYear הוא שנת הייצור של הרכב.
• סוג המאפיין – בדומה להכרזה על משתנים (ר' עמ' 36). String למחרוזת טקסטואלית, Integer למספרים בלבד בין 32,768- עד 32,768.

שימוש באובייקט מותאם אישית בקוד

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

הדוגמה הבאה מציגה את הגדרת המידע של כל אחד ממאפייני המשתנה, שהוכרז מסוג Car:

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

Dim X As New CAR.

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

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

לולאות

לולאות מאפשרות לחזור על אותו רצף של פקודות מספר פעמים. ישנם שני סוגים של לולאות:

• For Loops – חזרה על רצף פקודות מספר מוגדר של פעמים:
  • For-Next – מספר החזרות נקבע מראש וניתן לקבוע גם את האינטרוולים בין החזרות.
  • For-Each – לולאות המבוצעות על Collections (ר' עמ' 62). מספר החזרות תלוי במספר האובייקטים באוסף.

• Do Loops – חזרה על רצף פקודות כל עוד מתקיים תנאי מסוים, כלומר, מספר פעמים לא ידוע מראש:
  • Do-While – לולאה שתמשיך לרוץ ולבצע חזרות כל עוד תנאי מוגדר מתקיים ותפסיק כאשר הוא לא יתקיים.
  • Do-Until – לולאה שתמשיך לרוץ ולבצע חזרות כל עוד תנאי מוגדר לא מתקיים ותפסיק כאשר הוא יתקיים.

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

לולאת For-Next

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

For RunsCounter = {Start Number} To {End Number}

Command

Command

Command

Next RunsCounter

RunsCounter – דוגמה למשתנה שסופר את הריצות, עבורו יש להגדיר את המספרים ביניהם ירוץ הקוד (Start Number ו-End Number).

הדוגמה הבאה מציגה לולאה המוגדרת לחזור חמש פעמים:

תוצאת ריצת הקוד בגיליון:

הדוגמה הבאה מציגה קוד אשר בונה בגיליון את לוח הכפל עד 5, באמצעות לולאת For–Next:

תוצאת ריצת הקוד בגיליון:

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

הדוגמה הבאה מציגה לולאה של מונה הצועד מ-1 ועד 20 בקפיצות של 2:

תוצאת ריצת הקוד בגיליון:

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

תוצאת ריצת הקוד בגיליון:

ניתן להריץ את המונה גם לאחור. הדוגמה הבאה מציגה לולאה של מונה הצועד מ-50 ועד 1 בקפיצות של 3:

תוצאת ריצת הקוד בגיליון:

לולאת For-Each

כאמור, גם לולאת For–Each מבצעת מספר מוגדר של חזרות, אך ההגדרה הזו היא לא מספרית, אלא נובעת מכמות האובייקטים או הפריטים ב- Collection(ר' עמ' 62). כלומר, לולאת For–Each רצה על כל האובייקטים או הפריטים באוסף ומבצעת פעולות עבור כל אחד מהם. כזכור, האובייקטים או הפריטים באוסף מסודרים ברצף כרונולוגי, בהתאם לסדר זה תרוץ הלולאה על האוסף.

להלן המבנה התחבירי של הלולאה:

For Each {Object} in {Collection}

Command

Command

Command

Next

הדוגמה הבאה מציגה את יצירת לוח הכפל, הפעם באמצעות For–Each במקום For–Next (כפי שבוצע בסעיף הקודם):

• ניתן לראות כי האוסף עליו רצה הלולאה היא של Range בין התאים A1:E5.
• TA– משתנה עזר שמציין את האובייקט הנוכחי (תא בטווח שבין A1:A5) בו נמצאת הלולאה בכל חזרה.
• הפקודה המתבצעת בכל חזרה, היא הזנה לתוך ערך התא הנוכחי את המכפלה של מספר השורה ומספר העמודה של אותו התא:
  • Row – מאפיין השייך לאובייקט מסוג Cell (תא) המציין את מספר השורה.
  • Column – מאפיין השייך לאובייקט מסוג Cell (תא) המציין את מספר העמודה.

תוצאת ריצת הקוד בגיליון:

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

היתרונות העיקריים של לולאת For–Each על פני לולאות אחרות:

• For–Each מתאימה לריצה על אובייקטים.
• זמן הריצה של הקוד קצר בהרבה מאשר לולאת For–Next.

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

להלן הקוד ליצירת האוסף:

• ניתן להבחין בשורת הקוד Set {Collection} = Nothing. פקודה זו מנקה אוסף מכל הפריטים שבו.
• רשימת הפריטים בגיליון היא אוסף הנמצא בטווח תאים. עבור כל אובייקט באוסף Range שבין תא A1 בגיליון ועד התא האחרון ברשימה, הוא Range("A1").End(xlDown), מתבצעות חזרות הלולאה.
• VegItem– משתנה עזר שמציין את האובייקט הנוכחי בו נמצאת הלולאה בכל חזרה.
• בכל חזרה נכנס הערך שבתוך התא כפריט חדש לתוך אוסף אחר הוא VeggieColl.

להלן תצוגת חלונית Locals לאחר ריצת הקוד:

לולאות Do-Loop

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

להלן המבנה התחבירי של הלולאות:

Do While {Condition}                                                              Do Until {Condition}

Command                                                                               Command

Command                                                                               Command

Command                                                                               Command

Loop                                                                                       Loop

הדוגמה הבאה מציגה טבלה של מוצרים (מק"ט, שם ומחיר), שיש להוזיל ב-2%:

להלן הקוד עם לולאת Do–While שעוברת בכל תא בטבלה שיש בו מחיר ומוזילה אותו ב-2% (לכדי 0.98 מתוך המחיר):

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

תוצאת ריצת הקוד בגיליון:

הדוגמה הבאה מציגה את הקוד עבור אותה הפעולה עם לולאת Do–Until:

ניתן לראות כי ההבדל בתנאי הוא באופרטור: במקום כל עוד (While) ערך התא הפעיל שונה מריק (" "), התנאי הוא עד ((Until שערך התא הפעיל שווה לריק.

הדוגמה הבאה מציגה ציונים של סטודנטים במבחן, שנבחנו במועדים A ו-B:

להלן קוד להוספת 7 נקודות לציוני כל הסטודנטים שנבחנו במועד A בלבד:

תוצאת ריצת הקוד בגיליון:

אינטראקציה עם המשתמש

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

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

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

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

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

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

מבנה הפקודה המלא

להלן התחביר של הפקודה:

MsgBox( prompt [, buttons] [, title] [, helpfile, context] )

• Prompt – זהו שדה חובה. בפרמטר זה יש להזין את ההודעה למשתמש, שתופיע בגוף החלון. ניתן לשלב גם ערכים מתוך משתנים ופקודות נוספות המטפלות בטקסט כמו: יישור הטקסט (מועיל במיוחד לטקסט בעברית), יצירת שורה חדשה ועוד.
• Buttons – אפשרויות הכפתורים שניתן להוסיף לחלון. את הבחירה של המשתמש (תוכן הכפתור שעליו לחץ) ניתן לאחסן בתוך משתנה. מספר אפשרויות כפתורים לדוגמא:
  • vbOK
  • vbOKCancel
  • vbAbortRetryIgnore
  • vbYesNo
  • vbYesNoCancel
  • vbRetryCancel
• Title – להזנת את הכותרת של החלון.
• Helpfileו-Context – לשיוך קובץ עזרה, אם המשתמש בחר ללחוץ על כפתור מסוג עזרה.

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

להלן הקוד ליצירת החלון:

הדוגמה הבאה מציגה שימוש בMsgBox– עם כפתורים מותאמים אישית. תוכן הכפתור, שעליו ילחץ המשתמש, בתוך יאחסן משתנה:

UserAnswer הוא המשתנה שלתוכו יאוחסן תוצאות הבחירה של המשתמש.

vbYesNo – סוג הכפתור הרצוי שמאפשר למשתמש לבחור בין לחיצה על כפתור "כן" וכפתור "לא".

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

להלן חלון ה-MsgBox שיוצר קוד זה:

להלן תצוגת חלונית Locals לאחר בחירת המשתמש בכפתור "כן":

ניתן לראות שבחירת המשתמש בכפתור "כן", הזינה לתוך המשתנה UserAnswer את הערך 6.

בחירה בכפתור "לא" הייתה מזינה אל תוך המשתנה את הערך 7. כלומר, לכל אחד מהכפתורים ב-VBA יש ערך קבוע:

OK                  1

Cancel            2

Abort              3

Retry               4

Ignore             5

Yes                  6

No                   7

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

הקוד הבא מציג בדיקה על מכירות מתחת ל-200 $. באמצעות לולאה (ר' עמ' 68) הקוד רץ על כל רשומה בטבלה ובודק באמצעות תנאי If (ר' עמ' 51) אם ערך המכירה קטן מ-200. אם ימצא ערך כזה מיד יקפוץ חלון הודעה למשתמש:

ניתן לראות כי המשתמש יכול לבחור בין OK ו-Cancel בחלון ההודעה. אם הוא בחר ב-Cancel, כלומר לתוך המשתנה ANS יכנס הערך 2, מיד תופסק ריצת הקוד, באמצעות GoTo (ר' עמ' 58) אל סוף הקוד.

להלן חלון ה-MsgBox שיוצר קוד זה:

מבנה הפקודה המקוצר

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

MsgBox "{Text}"

לדוגמה:

MsgBox "Welcome"

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

הדוגמה הבאה מציגה שרשור של תוכן הודעה מילולי לשם המשתמש ושם הגיליון:

להלן חלון ה-MsgBox שיוצר קוד זה:

InputBox

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

להלן התחביר של הפקודה:

InputBox( prompt [, title] [, default value] )

• Prompt – זהו שדה חובה. בפרמטר זה יש להזין את ההודעה למשתמש, שתופיע בגוף החלון. ניתן לשלב גם ערכים מתוך משתנים ופקודות נוספות המטפלות בטקסט כמו: יישור הטקסט (מועיל במיוחד לטקסט בעברית), יצירת שורה חדשה ועוד.
• Title – להזנת את הכותרת של החלון.
• Default Value – אפשרות להצגת ערך ברירת מחדל, שיופיע בשדה ההזנה. פרמטר זה אינו חובה.

הדוגמה הבאה מציגה אחסון של המידע, שהמשתמש מזין בשדה ההזנה:

להלן חלון ה-InputBox שיוצר קוד זה:

להלן תצוגת חלונית Locals לאחר בחירת המשתמש בכפתור :

אירועים

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

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

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

התיבה השמאלית – בוררת בין קטגוריות האובייקטים באותו איזור.

התיבה הימנית – בוררת בין כל הפקודות השייכות לאותו האובייקט.

תיבות הבחירה באיזורי העריכה של אובייקטים בחלונית הפרויקטים

איזור העריכה של מודול רגיל

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

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

אירועים באיזור העריכה של הגיליון

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

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

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

אירועים באיזור העריכה של חוברת העבודה

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

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

יצירת אירוע

הדוגמה הבאה מציגה יצירת אירוע ברמת חוברת העבודה. לפי קוד זה, בעת אירוע של פתיחת חוברת העבודה יקפוץ חלון MsgBox למשתמש (ר' עמ' 79):

1. יש לפתוח את איזור העריכה של חוברת העבודה באמצעות חלונית הפרויקטים.
2. יש לבחור את האפשרות Workbook בחלונית השמאלית העליונה באיזור העריכה.
3. יש לבחור את האפשרות Open בחלונית הימנית העליונה באיזור העריכה.

באופן אוטומטי יופיע מבנה קוד להפעלה באמצעות אירוע לפי המבנה הבא:

Private Sub {Event} ()

End Sub

להלן הקוד במסגרת הפקודה לאירוע הפתיחה, המציג את חלון ההודעה:

להלן חלון ה-MsgBox שיוצר קוד זה מיד עם פתיחת חוברת העבודה:

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

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

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

מרכז יחסי האמון ורמות אבטחה

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

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

ניהול ההתראות ורמת האבטחה של ה-Excel

1. יש לבחור בתפריט קובץ.
2. יש לבחור באפשרויות. יפתח החלון אפשרויות Excel.
3. יש לבחור מרכז יחסי האמון.
4. יש ללחוץ על הכפתור הגדרות מרכז יחסי האמון…

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

הגדרת התראת האבטחה הראשית

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

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

הגדרות רמת חסימה או אישור של תוכן חיצוני

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

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

הגנה ונעילה

הגנת הקוד בעורך ה-VBA מפני פתיחה ועריכה

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

1. יש לבחור בתפריט Tools בעורך.
2. יש לבחור באפשרות VBAProject Properties. יפתח חלון עם השם התואם.
3. יש לבחור בלשונית Protection.
4. יש לסמן את תיבת הבחירה של האפשרות Lock project for viewing.
5. תחת הסעיף Password to view project properties יש להזין סיסמה לבחירה ווידוא הסיסמה.
6. אישור.

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

הגנה על גיליון

הגנת גיליון היא פעולה מובנית בתוכנה, המכילה שתי תכונות חשובות:

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

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

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

1. יש לבחור את כל התאים בגיליון.
2. יש לפתוח את החלון עיצוב תאים.
3. בלשונית הגנה יש לבטל את הסימון בתיבת הבחירה של האפשרות נעול.
4. אישור.
5. יש לבחור את טווח התאים הרצוי לנעילה מפני עריכה.
6. יש לפתוח את החלון עיצוב תאים.
7. בלשונית הגנה יש לסמן את תיבת הבחירה של האפשרות נעול.

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

הפעלת הגנת גיליון

1. יש לבחור את התאים לנעילה ו/או להסתרה.
2. יש לפתוח את החלון עיצוב תאים.
3. בלשונית הגנה יש לסמן את תיבות הבחירה של האפשרויות הרצויות נעול ו/או מוסתר.
4. אישור.
5. יש ללחוץ על הגנת גיליון בתפריט סקירה.

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

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

הגנת גיליון ב-VBA

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

{Sheet Object}.Protect "{Password}"

לדוגמה:

ActiveSheet.Protect "1234"

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

AllowFormattingCells          עצב שורות

AllowFormattingColumns   עצב עמודות

AllowFormattingRows         עצב תאים

AllowInsertingColumns       הוסף עמודות

AllowInsertingRows             הוסף שורות

AllowInsertingHyperlinks    הוסף היפר-קישורים

AllowDeletingColumns        מחק עמודות

AllowDeletingRows              מחק שורות

AllowSorting                         מיין

AllowFiltering                        השתמש בסינון אוטומטי

AllowUsingPivotTables       השתמש ב-PivotTable וב-PivotChart

DrawingObjects                    ערוך אובייקטים

Scenarios                               ערוך תרחישים

Contents                                (אם אפשרות זו מוגדרת כ-True, התכנים בכל התאים יהיו מוגנים מפני עריכה או שינוי למעט, תאים שאינם נעולים.)

UserInterfaceOnly    (אם אפשרות זו מוגדרת כ-True, ממשק המשתמש מוגן אך לא פקודות מאקרו. אם אפשרות זו מושמטת זה מושמטת, ההגנה חלה הן על פקודות מאקרו והן על ממשק המשתמש

הדוגמה הבאה מציגה נעילת הגיליון הנוכחי, באמצעות הסיסמה 1234, ללא שינוי ברירות המחדל של אפשור בחר תאים נעולים ובחר תאים לא נעולים:

הפקודה להסרת הגנת הגיליון יש להשתמש בפקודה Unprotect. להלן התחביר של הפקודה:

{Sheet Object}.Unprotect "{Password}"

לדוגמה:

ActiveSheet.Unprotect "1234"

 הגנה על חוברת עבודה

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

כדי להפעיל את הגנת חוברת עבודה:

1. יש ללחוץ על הכפתור הגנת חוברת עבודה בתפריט סקירה.

2. בחלון הגנה על מבנה וחלונות שיפתח יש לוודא שתיבת הבחירה של האפשרות מבנה מסומנת. (האפשרות חלונות מאפשרת להגן מפני שינוי של תצורות פריסה של חלונות מרובים. אפשרות זו זמינה רק בגרסאות Excel 2007, Excel 2010, Excel 2011            למחשבי Mac ו-Excel 2016 למחשבי Mac). כמו כן, רצוי להזין סיסמה נבחרת לשחרור מצב הנעילה באמצעות סיסמה:

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

הגנת חוברת עבודה ב-VBA

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

{Workbook Object}.Protect "{Password}", Structure:=True, Windows:=False

לדוגמה:

ActiveWorkbook.Protect "1234", Structure:=True, Windows:=False

רשימת פקודות שימושיות