קורס 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, או ישירות מתוך סרגל הכלים: