תכנות מאקרו באקסל: המדריך המלא שלב אחר שלב

אינפורמטק דיגיטל » תכנות » תכנות מאקרו באקסל: המדריך המלא שלב אחר שלב

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

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

מהו בעצם מאקרו באקסל?

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

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

למה להשתמש במאקרו? יתרונות אמיתיים

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

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

הפעל את הכרטיסייה 'מפתחים' (Windows ו-Mac)

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

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

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

מכשיר ההקלטה מושלם להתחלה. תחת מפתחים > הקלטת מאקרו (או Alt+T+M+R ב-Windows), הקצה שם תיאורי. זכור: השם חייב להתחיל באות, ללא רווחים או סמלים.

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

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

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

לאחר שתסיים, הפסק את ההקלטה. כדי להפעיל אותה, עבור אל מפתחים > פקודות מאקרו (או Alt+F8), בחר את המאקרו שלך ולחץ על הפעלה. תראה את Excel חוזר על השלבים באותו סדר בדיוק.

הפעל, ערוך ונהל את פקודות המאקרו שלך

מתוך מפתח > מאקרו (Alt+F8) ניתן להפעיל, לערוך או למחוק מאקרו. אם תבחרו "עריכה", עורך Visual Basic ייפתח עם הקוד המוקלט, אותו תוכלו לנקות ולמטב (לעתים קרובות הוא כולל שלבים מיותרים).

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

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

אם עליך לעשות שימוש חוזר במאקרו בחוברת עבודה אחרת, תוכל להעתיק את המודול מעורך Visual Basic. פתח את VBE (Alt+F11), גרור את המודול לחוברת העבודה הפתוחה האחרת, או השתמש בקובץ ייצוא/ייבוא ​​.bas.

תכנות מאקרו באקסל בעזרת עורך Visual Basic (VBA)

כדי לעבור מעבר למערכת ההקלטה, פתחו את העורך באמצעות Alt+F11. הוסיפו מודול (Insert > Module) וצרו את תת-השגרה הראשונה שלכם. התבנית הבסיסית היא Sub Name() … End Sub.

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

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

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

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

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

הפניות מוחלטות לעומת הפניות יחסיות

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

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

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

טעויות נפוצות בעת רישום תהליכים

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

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

שיטות עבודה מומלצות בעת הקלטה

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

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

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

בנוסף לתיבת הדו-שיח פקודות מאקרו (Alt+F8), ניתן להפעיל פקודות מאקרו באמצעות צורה, לחצן בסרגל הכלים לגישה מהירה או על ידי פתיחת חוברת העבודה. ניתן אפילו להפעיל את המאקרו מאפליקציות Office אחרות, מה שיאפשר לכם להפוך משימות בין יישומים לאוטומטיות (לדוגמה, עדכון טבלה ושליחת דוא"ל דרך Outlook).

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

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

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

אבטחה והפעלת פקודות מאקרו

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

הקצאת מאקרו לכפתורים, צורות ופקדים

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

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

דוגמאות מאקרו שימושיות (VBA)

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

העתק את התוכן שנבחר למיקום אחר באותו גיליון: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

הדפס את הגיליון הפעיל: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

שמור את הקובץ כחוברת עבודה המותאמת למאקרו (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

החלת עיצוב על טווח (התאמת הטווח לצרכים שלך): בעזרת טווח(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

מצא ערך בטווח והצג את ההתאמה הראשונה: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

מקרה מעשי: עיצוב כאחוז

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

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

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

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

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

עריכה וניקוי של קוד מוקלט

המקליט לוכד "הכל": בחירות, לחיצות ושינויים בטאבים. כאשר ה-VBE נפתח, הוא מסיר בחירות מיותרות (Select/Selection) על ידי החלפתן בהפניות ישירות (לדוגמה, Range("A1").Value = ... ) ומקבץ פורמטים לבלוקים של With/End With.

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

שאלות נפוצות

כיצד ניתן ליצור UserForm באקסל? פתחו את עורך Visual Basic (Alt+F11), עבור אל Insert > UserForm והוסיפו פקדים (תיבות טקסט, כפתורים וכו'). ניתן להציג אותו באמצעות מאקרו.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

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

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

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

כיצד ניתן למחוק מאקרו? עבור אל Developer > Macros, בחר את המאקרו ולחץ על Delete. אם ברצונך למחוק את כל הקוד ממודול, פתח את קובץ ה-VBE (Alt+F11), אתר את המודול בפרויקט, לחץ לחיצה ימנית ובחר Remove Module (ניתן לייצא אותו תחילה אם ברצונך לשמור אותו).

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