תוכן
גם אם אינכם יודעים כיצד לתכנת, ישנם מקומות רבים (ספרים, אתרים, פורומים) בהם תוכלו למצוא קוד מאקרו VBA מוכן למספר עצום של משימות טיפוסיות באקסל. מניסיוני, רוב המשתמשים אוספים במוקדם או במאוחר את אוסף פקודות המאקרו האישי שלהם כדי להפוך תהליכים שגרתיים לאוטומטיים, בין אם זה תרגום נוסחאות לערכים, הצגת סכומים במילים או סיכום תאים לפי צבע. וכאן נוצרת הבעיה - קוד המאקרו ב-Visual Basic צריך להיות מאוחסן איפשהו על מנת שישמש אותו בהמשך העבודה.
האפשרות הקלה ביותר היא לשמור את קוד המאקרו ישירות בקובץ העבודה על ידי מעבר לעורך Visual Basic באמצעות קיצור המקלדת אחר+F11 והוספת מודול ריק חדש דרך התפריט הכנס - מודול:
עם זאת, ישנם מספר חסרונות בשיטה זו:
- אם יש הרבה קבצי עבודה, ויש צורך במאקרו בכל מקום, כמו מאקרו להמרת נוסחאות לערכים, אז תצטרך להעתיק את הקוד בכל ספר.
- אסור לשכוח לשמור קובץ בפורמט המאפשר מאקרו (xlsm) או בפורמט ספר בינארי (xlsb).
- כשפותחים קובץ כזה הגנת מאקרו יוציא בכל פעם אזהרה שצריך להכיר בה (טוב, או להשבית את ההגנה לחלוטין, מה שאולי לא תמיד רצוי).
פתרון אלגנטי יותר יהיה ליצור תוסף משלך (תוסף Excel) - קובץ נפרד בפורמט מיוחד (xlam) המכיל את כל פקודות המאקרו ה"מועדפות" שלך. היתרונות של גישה זו:
- זה יספיק חבר את התוספת פעם אחת ב-Excel - ואתה יכול להשתמש בהליכי ה-VBA והפונקציות שלו בכל קובץ במחשב זה. שמירה מחדש של קבצי העבודה שלך בפורמטים xlsm-ו-xlsb, לפיכך, אינה נדרשת, מכיוון. קוד המקור לא יאוחסן בהם, אלא בקובץ התוספת.
- הֲגָנָה גם פקודות מאקרו לא יפריעו לך. תוספות הן, בהגדרה, מקורות מהימנים.
- יכול לעשות לשונית נפרדת ברצועת Excel עם כפתורים נחמדים להפעלת פקודות מאקרו של תוספים.
- התוספת היא קובץ נפרד. שֶׁלוֹ קל לנשיאה ממחשב למחשב, שתף אותו עם עמיתים או אפילו תמכור אותו 😉
בואו נעבור על כל התהליך של יצירת תוסף Microsoft Excel משלכם שלב אחר שלב.
שלב 1. צור קובץ תוספת
פתח את Microsoft Excel עם חוברת עבודה ריקה ושמור אותה בכל שם מתאים (לדוגמה MyExcelAddin) בפורמט תוספת עם הפקודה קובץ - שמור בשם או מפתחות F12, ציון סוג הקובץ תוסף אקסל:
שימו לב שכברירת מחדל, Excel מאחסן תוספות בתיקייה C:UsersYour_nameAppDataRoamingMicrosoftAddIns, אך באופן עקרוני, תוכלו לציין כל תיקיה אחרת שנוחה לכם.
שלב 2. אנו מחברים את התוספת שנוצרה
כעת התוספת שיצרנו בשלב האחרון MyExcelAddin חייב להיות מחובר לאקסל. כדי לעשות זאת, עבור לתפריט קובץ – אפשרויות – תוספות (קובץ - אפשרויות - תוספות), לחץ על הכפתור אודות (ללכת) בתחתית החלון. בחלון שנפתח, לחץ על הכפתור סקירה (לְדַפדֵף) וציין את המיקום של קובץ התוספת שלנו.
אם עשית הכל נכון, אז שלנו MyExcelAddin אמור להופיע ברשימת התוספות הזמינות:
שלב 3. הוסף פקודות מאקרו לתוספת
התוסף שלנו מחובר לאקסל ועובד בהצלחה, אבל אין בו עדיין מאקרו אחד. בואו נמלא אותו. כדי לעשות זאת, פתח את עורך Visual Basic עם קיצור המקלדת אחר+F11 או על ידי כפתור Visual Basic כרטיסייה מפתחים (מפתח). אם כרטיסיות מפתחים לא נראה, ניתן להציג אותו באמצעות קובץ – אפשרויות – הגדרת סרט (קובץ - אפשרויות - התאמה אישית של סרט).
צריך להיות חלון בפינה השמאלית העליונה של העורך פרויקט (אם זה לא נראה, הפעל אותו דרך התפריט תצוגה - סייר פרויקטים):
חלון זה מציג את כל חוברות העבודה הפתוחות והפעלת תוספות Microsoft Excel, כולל שלנו. VBAProject (MyExcelAddin.xlam) בחר אותו עם העכבר והוסף לו מודול חדש דרך התפריט הכנס - מודול. במודול זה, נאחסן את קוד ה-VBA של פקודות המאקרו של התוספות שלנו.
אתה יכול להקליד את הקוד מאפס (אם אתה יודע לתכנת), או להעתיק אותו ממקום מוכן (וזה הרבה יותר קל). בואו, לבדיקה, נזין את הקוד של מאקרו פשוט אך שימושי למודול הריק שנוסף:
לאחר הזנת הקוד, אל תשכח ללחוץ על כפתור השמירה (דיסקט) בפינה השמאלית העליונה.
המאקרו שלנו FormulasToValues, כפי שאתה יכול בקלות לדמיין, ממיר נוסחאות לערכים בטווח שנבחר מראש. לפעמים גם פקודות מאקרו אלו נקראות נהלים. כדי להפעיל אותו, עליך לבחור תאים עם נוסחאות ולפתוח תיבת דו-שיח מיוחדת מאקרו מהכרטיסייה מפתחים (מפתח - מאקרו) או קיצור מקלדת אחר+F8. בדרך כלל, חלון זה מציג פקודות מאקרו זמינות מכל חוברות העבודה הפתוחות, אך פקודות מאקרו של תוספים אינן גלויות כאן. למרות זאת, נוכל להזין בשטח את שם ההליך שלנו שם מאקרו (שם מאקרו)ואז לחץ על הכפתור הפעלה (לָרוּץ) - והמאקרו שלנו יעבוד:
| |
כאן אתה יכול גם להקצות קיצור מקלדת כדי להפעיל במהירות מאקרו - הכפתור אחראי לכך פרמטרים (אפשרויות) בחלון הקודם מאקרו:
בעת הקצאת מקשים, זכור שהם רגישים לאותיות גדולות ורגישות לפריסת המקלדת. אז אם אתה מקצה שילוב כמו Ctrl+Й, אז, למעשה, בעתיד תצטרך לוודא שהפריסה מופעלת וללחוץ בנוסף משמרתכדי לקבל את האות הגדולה.
מטעמי נוחות, נוכל גם להוסיף כפתור עבור המאקרו שלנו לסרגל הכלים של גישה מהירה בפינה השמאלית העליונה של החלון. כדי לעשות זאת, בחר קובץ - אפשרויות - סרגל הכלים של גישה מהירה (קובץ - אפשרויות - התאמה אישית של סרגל הכלים של גישה מהירה), ולאחר מכן ברשימה הנפתחת בראש החלון את האפשרות מאקרו. אחרי זה המאקרו שלנו FormulasToValues ניתן למקם על הפאנל עם הכפתור להוסיף (הוסף) ובחר סמל עבורו עם הכפתור שינוי (לַעֲרוֹך):
שלב 4. הוסף פונקציות לתוספת
אבל פרוצדורות מאקרו, יש גם פקודות מאקרו של פונקציות או כפי שהם נקראים UDF (פונקציה מוגדרת על ידי משתמש = פונקציה מוגדרת על ידי משתמש). בואו ניצור מודול נפרד בתוסף שלנו (פקודה בתפריט הכנס - מודול) והדבק שם את הקוד של הפונקציה הבאה:
קל לראות שפונקציה זו נחוצה כדי לחלץ מע"מ מהסכום כולל מע"מ. לא הבינומי של ניוטון, כמובן, אבל זה יעזור לנו כדוגמה להראות את העקרונות הבסיסיים.
שימו לב שהתחביר של פונקציה שונה מהליך:
- נעשה שימוש בבנייה פונקציה…. סיום פונקציה במקום משנה... סיום משנה
- אחרי שם הפונקציה, הארגומנטים שלה מסומנים בסוגריים
- בגוף הפונקציה מתבצעים החישובים הדרושים ולאחר מכן התוצאה מוקצית למשתנה עם שם הפונקציה
כמו כן, שימו לב שאין צורך בפונקציה זו, ואי אפשר לרוץ כמו הליך המאקרו הקודם דרך תיבת הדו-שיח מאקרו ולחצן הפעלה. יש להשתמש בפונקציית מאקרו כזו כפונקציית גליון עבודה רגילה (SUM, IF, VLOOKUP...), כלומר פשוט הזן בכל תא, תוך ציון ערך הסכום עם מע"מ כארגומנט:
... או היכנס דרך תיבת הדו-שיח הרגילה להוספת פונקציה (לחצן fx בשורת הנוסחאות), בחירת קטגוריה משתמש הוגדר (משתמש הוגדר):
הרגע הלא נעים היחיד כאן הוא היעדר התיאור הרגיל של הפונקציה בתחתית החלון. כדי להוסיף אותו תצטרך לעשות את הפעולות הבאות:
- פתח את עורך Visual Basic עם קיצור מקלדת אחר+F11
- בחר את התוספת בחלונית Project ולחץ על המקש F2כדי לפתוח את חלון דפדפן האובייקטים
- בחר את פרויקט התוספת שלך מהרשימה הנפתחת בחלק העליון של החלון
- לחץ לחיצה ימנית על הפונקציה שמופיעה ובחר פקודה מאפיין.
- הזן תיאור של הפונקציה בחלון תיאור
- שמור את קובץ התוספת ולהפעיל מחדש את אקסל.
לאחר הפעלה מחדש, הפונקציה אמורה להציג את התיאור שהזנו:
שלב 5. צור לשונית הרחבה בממשק
המגע הסופי, אם כי לא חובה, אך נעים יהיה יצירת לשונית נפרדת עם כפתור להפעלת המאקרו שלנו, שיופיע בממשק האקסל לאחר חיבור התוסף שלנו.
מידע על הכרטיסיות המוצגות כברירת מחדל כלול בספר ויש לעצב אותו בקוד XML מיוחד. הדרך הקלה ביותר לכתוב ולערוך קוד כזה היא בעזרת תוכניות מיוחדות - עורכי XML. אחת הנוחות (והחינמיות) היא התוכנית של מקסים נוביקוב עורך XML של סרט.
האלגוריתם לעבודה איתו הוא כדלקמן:
- סגור את כל חלונות האקסל כדי שלא תהיה התנגשות בקבצים כאשר אנו עורכים את קוד ה-XML של התוספת.
- הפעל את תוכנית עורך ה-XML של Ribbon ופתח בה את הקובץ MyExcelAddin.xlam שלנו
- עם כפתור כרטיסיות בפינה השמאלית העליונה, הוסף את קטע הקוד עבור הכרטיסייה החדשה:
- אתה צריך לרשום מרכאות ריקות id הכרטיסייה והקבוצה שלנו (כל מזהים ייחודיים), וכן ב תווית – שמות הכרטיסייה שלנו וקבוצת לחצנים עליה:
- עם כפתור לַחְצָן בחלונית השמאלית, הוסף קוד ריק עבור הכפתור והוסף לו תגים:
- תווית הוא הטקסט על הכפתור
- imageMso - זהו השם המותנה של התמונה על הכפתור. השתמשתי בסמל כפתור אדום בשם AnimationCustomAddExitDialog. ניתן למצוא את שמות כל הכפתורים הזמינים (ויש כמה מאות מהם!) במספר רב של אתרים באינטרנט אם תחפשו את מילות המפתח "imageMso". בתור התחלה, אתה יכול ללכת לכאן.
- onAction – זה השם של הליך ה-callback – מאקרו קצר מיוחד שיריץ את המאקרו הראשי שלנו FormulasToValues. אתה יכול לקרוא להליך הזה איך שאתה רוצה. נוסיף את זה קצת מאוחר יותר.
- אתה יכול לבדוק את נכונות כל מה שנעשה באמצעות הכפתור עם סימן ביקורת ירוק בחלק העליון של סרגל הכלים. באותו מקום, לחץ על הכפתור עם תקליטון כדי לשמור את כל השינויים.
- סגור את עורך ה-XML של הסרט
- פתח את Excel, עבור אל עורך Visual Basic והוסף הליך התקשרות חזרה למאקרו שלנו KillFormulasכך שהוא מפעיל את המאקרו הראשי שלנו להחלפת נוסחאות בערכים.
- אנו שומרים את השינויים ובחזרה לאקסל, בודקים את התוצאה:
זה הכל - התוסף מוכן לשימוש. מלא אותו עם נהלים ופונקציות משלך, הוסף לחצנים יפים - ויהיה הרבה יותר קל להשתמש בפקודות מאקרו בעבודה שלך.
- מהן פקודות מאקרו, איך להשתמש בהן בעבודה שלך, היכן להשיג קוד מאקרו ב-Visual Basic.
- כיצד ליצור מסך פתיחה בעת פתיחת חוברת עבודה באקסל
- מהו ספר מאקרו אישי וכיצד להשתמש בו