מבוא

זו הדרכה בנושא כתיבת קוד בגליונות אלקטרוניים של Excel באמצעות Visual Basic for Applications (VBA).

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

• בשנת 1996 היו למעלה מ- 30 מיליון משתמשים ב- Microsoft Excel (מקור).
• כיום יש כ- 750 מיליון משתמשים ב- Microsoft Excel. זה קצת יותר מאוכלוסיית אירופה ופי 25 יותר משתמשים מאשר היו ב -1996.

אנחנו משפחה מאושרת אחת גדולה!

במדריך זה תלמדו על VBA וכיצד לכתוב קוד בגליון אלקטרוני של Excel באמצעות Visual Basic.

תנאים מוקדמים

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

• היכרות בסיסית עד בינונית עם Microsoft Excel
• אם ברצונך לעקוב אחר דוגמאות ה- VBA במאמר זה, תזדקק לגישה ל- Microsoft Excel, רצוי הגרסה האחרונה (2019) אך Excel 2016 ו- Excel 2013 יעבדו בסדר גמור.
• נכונות לנסות דברים חדשים

מטרות למידה

במהלך מאמר זה תוכלו ללמוד:

1. מה זה VBA
2. למה שתשתמש ב- VBA
3. כיצד להגדיר ב- Excel לכתוב VBA
4. כיצד לפתור כמה בעיות בעולם האמיתי עם VBA

מושגים חשובים

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

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

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

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

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

הערה: יכולות להיות לך פונקציות הפועלות בתוך תת-נוהל. תראה בהמשך.

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

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

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

מה זה VBA?

Visual Basic for Applications היא שפת תכנות שפותחה על ידי מיקרוסופט. כל תוכנה בחבילת Microsoft Office כלולה בשפת VBA ללא עלות נוספת. VBA מאפשר למשתמשי Microsoft Office ליצור תוכניות קטנות הפועלות בתוך תוכנות Microsoft Office.

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

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

יאם.

מדוע להשתמש ב- VBA ב- Excel?

כי פיצה עם עצים זה הכי טוב!

אבל ברצינות.

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

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

כדאי לשקול להשתמש ב- VBA אם אתה צריך:

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

מתארגן לכתיבת VBA ב- Excel

כרטיסיית מפתח

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

כדי להוסיף את הכרטיסייה מפתח לסרט:

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

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

עורך VBA

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

דוגמאות VBA של Excel

ראשית, בואו ניצור קובץ שנוכל לשחק בו.

1. פתח קובץ Excel חדש
2. שמור אותו כחוברת עבודה המאפשרת מאקרו (. Xlsm)
3. בחר בכרטיסיה מפתח
4. פתח את עורך ה- VBA

בואו נסתובב עם כמה דוגמאות קלות כדי לגרום לכם לכתוב קוד בגליון אלקטרוני באמצעות Visual Basic.

דוגמה מס '1: הצג הודעה כאשר משתמשים פותחים את חוברת העבודה של Excel

בעורך VBA, בחר הוסף -> מודול חדש

כתוב קוד זה בחלון המודול (אל תדביק!):

תת אוטומטי_פתיחה ()

MsgBox ("ברוך הבא לחוברת העבודה של XYZ.")

סיום משנה

שמור, סגור את חוברת העבודה ופתח מחדש את חוברת העבודה. תופיע תיבת דו-שיח זו.

טא דה!

איך זה עושה את זה?

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

• Sub (קיצור של "Subroutine): זכרו מההתחלה," קבוצה של הצהרות VBA שמבצעת פעולה אחת או יותר. "
• Auto_Open: זהו תת-הפעולה הספציפית. זה מפעיל באופן אוטומטי את הקוד שלך כשקובץ ה- Excel נפתח - זה האירוע שמפעיל את ההליך. Auto_Open יפעל רק כאשר חוברת העבודה נפתחת באופן ידני; היא לא תפעל אם חוברת העבודה נפתחת באמצעות קוד מחוברת עבודה אחרת (Workbook_Open תעשה זאת, למידע נוסף על ההבדל בין השניים).
• כברירת מחדל, הגישה של תת-תוכנית היא ציבורית. המשמעות היא שכל מודול אחר יכול להשתמש בתת-דרך זו. כל הדוגמאות במדריך זה יהיו תוכניות משנה ציבוריות. במידת הצורך, אתה יכול להכריז על תכניות משנה כפרטיות. ייתכן שיהיה צורך בכך במצבים מסוימים. למידע נוסף על משתני גישה לתת-דרך.
• msgBox: זוהי פונקציה - קבוצה של הצהרות VBA שמבצעת פעולה אחת או יותר ומחזירה ערך. הערך המוחזר הוא ההודעה "ברוך הבא לחוברת העבודה של XYZ."

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

מתי אוכל להשתמש בזה?

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

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

דוגמאות של העולם האמיתי

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

דוגמה מס '2: אפשר למשתמש לבצע הליך אחר

בעורך VBA, בחר הוסף -> מודול חדש

כתוב קוד זה בחלון המודול (אל תדביק!):

תת UserReportQuery ()

קלט משתמש קלוש כל עוד

תשובה עמומה כשלם

UserInput = vbYesNo

תשובה = MsgBox ("עיבוד דוח XYZ?", קלט משתמש)

אם תשובה = vb כן אז ProcessReport

סיום משנה

דיווח על תהליך משנה ()

MsgBox ("תודה שעיבדת את דו"ח ה- XYZ.")

סיום משנה

שמור ונווט חזרה לכרטיסיה מפתח ב- Excel ובחר באפשרות "כפתור". לחץ על תא והקצה את כפתור המאקרו UserReportQuery.

כעת לחץ על הכפתור. הודעה זו אמורה להציג:

לחץ על "כן" או הקש Enter.

שוב טאדה!

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

איך זה עושה את זה?

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

• קלט משתמש קלוש כל עוד: קיצור של Dim הוא "ממד" ומאפשר לך להכריז על שמות משתנים. במקרה זה, UserInput הוא שם המשתנה ו- Long הוא סוג הנתונים. באנגלית רגילה פירוש השורה היא "הנה משתנה שנקרא" UserInput ", וזה סוג משתנה ארוך."
• תשובה עמומה כשלם: מכריז על משתנה אחר הנקרא "תשובה", עם סוג נתונים של מספר שלם. למידע נוסף על סוגי נתונים כאן.
• UserInput = vbYesNo: מקצה ערך למשתנה. במקרה זה, vbYesNo, המציג לחצני כן ולא. ישנם סוגים רבים של כפתורים, למידע נוסף כאן.
• תשובה = MsgBox ("עיבוד דוח XYZ?", UserInput): מקצה את הערך של המשתנה Answer להיות פונקציית MsgBox ומשתנה UserInput. כן, משתנה בתוך משתנה.
• אם תשובה = vb כן אז ProcessReport: זוהי "אם משפט", משפט מותנה, המאפשר לנו לומר אם x נכון, אז תעשה y. במקרה זה, אם המשתמש בחר באפשרות "כן", בצע את תוכנית המשנה של ProcessReport.

מתי אוכל להשתמש בזה?

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

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

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

דוגמאות של העולם האמיתי

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

דוגמה מס '3: הוסף מספרים לטווח עם לולאה למסלול הבא

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

בעורך VBA, בחר הוסף -> מודול חדש

כתוב קוד זה בחלון המודול (אל תדביק!):

דוגמה לולאה משנה ()

עמום X כשלם

עבור X = 1 עד 100

טווח ("A" ו- X). ערך = X

ה- X הבא

סיום משנה

שמור ונווט חזרה לכרטיסיה מפתח ב- Excel ובחר בלחצן המאקרו. הפעל את המאקרו LoopExample.

זה אמור לקרות:

וכו ', עד השורה ה -100.

איך זה עושה את זה?

• עמום X כשלם: מצהיר על המשתנה X כסוג נתונים של מספר שלם.
• עבור X = 1 עד 100: זו ההתחלה של לולאת ה- For. במילים פשוטות, זה אומר לולאה להמשיך לחזור עד X = 100. X הוא הדלפק . הלולאה תמשיך לפעול עד X = 100, תבצע בפעם האחרונה, ואז תפסיק.
• טווח ("A" ו- X). ערך = X: זה מצהיר על טווח הלולאה ומה להכניס לטווח זה. מכיוון ש- X = 1 בתחילה, התא הראשון יהיה A1, ובשלב זה הלולאה תכניס את X לתא זה.
• X הבא: זה אומר לולאה לרוץ שוב

מתי אוכל להשתמש בזה?

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

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

עליכם להבין מה אמור להיות המחיר הקמעונאי של כל מוצר. אתה חושב שזה צריך להיות עלות המרכיבים בתוספת 20%, אך גם 1.2% מתחת לממוצע בשוק אם אפשר. לולאת For-Next תאפשר לך לבצע חישוב מסוג זה.

דוגמאות של העולם האמיתי

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

סיכום

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

• מה זה VBA?
• כיצד אוכל להגדיר את השימוש ב- VBA ב- Excel?
• מדוע ומתי תשתמש ב- VBA?
• מהן כמה בעיות שיכולתי לפתור עם VBA?

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

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

קידוד שמח!

משאבי למידה

• תכנות VBA של Excel עבור Dummies, ג'ון וולקנבך
• התחל בעבודה עם VBA, Microsoft Documentation
• לימוד VBA באקסל, לינדה

קצת עלי

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