24 דוגמאות מאקרו שימושיות של Excel למתחילים ב-VBA (מוכן לשימוש)

שימוש בפקודות מאקרו של Excel יכול להאיץ את העבודה ולחסוך זמן רב.

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

אז שווה להחזיק קבוצה שימושית של פקודות מאקרו VBA שתוכלו לשמור בכיס האחורי ולהשתמש בהן כשתזדקקו לה.

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

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

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

אתה יכול לסמן דף זה לעיון עתידי.

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

שימוש בקוד מדוגמת המאקרו של Excel

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

• פתח את חוברת העבודה שבה ברצונך להשתמש במאקרו.
• החזק את מקש ALT לחוץ והקש F11.זה ייפתחעורך VB.
• לחץ לחיצה ימנית על כל אובייקט בסייר הפרויקטים.
• עבור אל הוספה -> מודולים.
• העתק והדבק את הקוד בחלון קוד המודול.

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

לאחר הכנסת הקוד לחוברת העבודה, עליך לשמור אותו עם הסיומת .XLSM או .XLS.

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

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

• עבור ללשונית מפתחים.
• לחץ על מאקרו.

• בתיבת הדו-שיח מאקרו, בחר את המאקרו להפעלה.
• לחץ על כפתור הפעל.

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

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

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

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

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

דוגמה למקרו של אקסל

מאמר זה מתאר את דוגמאות המאקרו הבאות:

בטל הסתרה של כל גליונות העבודה בבת אחת

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

הנה הקוד להצגת כל הגיליונות בחוברת העבודה.

'קוד זה יחשוף את כל הגיליונות בחוברת העבודה Sub UnhideAllWoksheets() Dim ws As Workheet For Every ws In ActiveWorkbook. גיליונות עבודה ws. Visible = xlSheetVisible Next ws End Sub

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

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

הסתר את כל גליונות העבודה מלבד גליון העבודה הפעיל

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

'מאקרו זה יסתיר את כל גליון העבודה מלבד הגיליון הפעיל Sub HideAllExceptActiveSheet() Dim ws As Workheet For Every ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

מיין את גליון העבודה בסדר אלפביתי באמצעות VBA

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

'קוד זה ימיין את גליונות העבודה בסדר אלפביתי Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

הגן על כל גליונות העבודה בבת אחת

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

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

'קוד זה יגן על כל הגיליונות בבת אחת Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'החלף את Test123 בסיסמה הרצויה עבור כל ws In Worksheets ws.Protect password:=password Next ws סיום משנה

בטל את ההגנה על כל גליונות העבודה בבת אחת

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

'קוד זה יגן על כל הגיליונות בבת אחת Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'החלף את Test123 בסיסמה הרצויה עבור כל ws In Worksheets ws.Unprotect password:=password Next ws סיום משנה

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

בטל הסתרת כל השורות והעמודות

קוד מאקרו זה יחשוף את כל השורות והעמודות המוסתרות.

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

'קוד זה יציג את הסתרת כל השורות והעמודות בתת גיליון העבודה UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

בטל את המיזוג של כל התאים הממוזגים

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

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

'קוד זה יבטל את המיזוג של כל התאים הממוזגים Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

שימו לב שאני ממליץ להשתמש באפשרות "מרכז על פני בחירה" במקום אפשרות "מיזוג ומרכז".

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

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

תרגול טוב הוא לשמור את הקובץ עם חותמת זמן.

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

הנה הקוד ששומר אוטומטית את חוברת העבודה בתיקייה שצוינה ומוסיף חותמת זמן בעת ​​השמירה.

'קוד זה ישמור את הקובץ עם חותמת זמן בשמו Sub SaveWorkbookWithTimeStamp() חותמת זמן עמומה כחותמת זמן של מחרוזת = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & חותמת זמן סיום משנה

עליך לציין את מיקום התיקיה ואת שם הקובץ.

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

שמור כל גליון עבודה כקובץ PDF נפרד

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

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

להלן קוד VBA ששומר כל גליון עבודה כקובץ PDF נפרד.

'קוד זה ישמור כל גיליון רע בתור PDF Sub SaveWorkshetAsPDF() Dim ws As Workheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" הבא ws End Sub

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

שימו לב שקוד זה עובד רק עבור גליונות עבודה (לא עבור גליונות תרשים).

שמור כל גליון עבודה כקובץ PDF נפרד

הנה הקוד לשמירת חוברת העבודה כולה כקובץ PDF בתיקייה שצוינה.

'קוד זה ישמור את חוברת העבודה כולה בתור PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

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

המר את כל הנוסחאות לערכים

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

'קוד זה ימיר את כל הנוסחאות לערכים Sub ConvertToValues() עם ActiveSheet.UsedRange .Value = .Value End With End Sub

קוד זה מזהה אוטומטית את התא המשמש וממיר אותו לערך.

הגן/נעול תאים באמצעות נוסחאות

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

הנה הקוד שינעל את כל התאים שיש להם נוסחאות וישאיר את כל שאר התאים לא נעולים.

'קוד המאקרו הזה ינעל את כל התאים עם נוסחאות Sub LockCellsWithFormulas() עם ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows עם End:=True

מדריך קשור: כיצד לנעול תאים באקסל.

הגן על כל הגיליונות בחוברת עבודה

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

'קוד זה יגן על כל הגיליונות בחוברת העבודה Sub ProtectAllSheets() Dim ws As Workheet For Every ws In Worksheets ws. Protect Next ws End Sub

קוד זה יעבור על כל הגיליונות אחד אחד ויגן עליהם.

אם אתה רוצה לבטל את ההגנה על כל הגיליונות, השתמש ב-ws.Unprotect במקום ב-ws.Protect בקוד שלך.

הכנס שורה אחרי כל שורה של הבחירה

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

'קוד זה יוסיף שורה אחרי כל שורה בבחירה Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. הכנס ActiveCell.Offset(2, 0). בחר ב- Next i End Sub

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

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

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

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

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

'קוד זה יוסיף חותמת זמן בתא הסמוך Private Sub Worksheet_Change(ByVal Target As Range) בשגיאה GoTo Handler אם Target.Column = 1 ו-Target.Value <> "" ואז Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

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

כמו כן, קוד זה פועל כאשר הזנת הנתונים מתבצעת בעמודה A (שים לב שלקוד יש Target.Column = 1 שורה).אתה יכול לשנות את זה בהתאם.

הדגש שורות חלופיות בבחירה

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

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

'קוד זה ידגיש שורות חלופיות בבחירה Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Every Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

שימו לב שציינתי את הצבע כ-vbCyan בקוד.אתה יכול גם לציין צבעים אחרים (למשל vbRed, vbGreen, vbBlue).

סמן תאים באיות שגוי

לאקסל אין את בדיקת האיות ב-Word או PowerPoint.בעוד שאתה יכול להפעיל בדיקת איות על ידי לחיצה על F7, אין רמז חזותי לשגיאות כתיב.

השתמש בקוד זה כדי להדגיש באופן מיידי את כל התאים שגוי איות.

'קוד זה ידגיש את התאים עם מילים שגוי. cl End Sub

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

רענן את כל טבלאות הציר בחוברת עבודה

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

'קוד זה ירענן את כל טבלת Pivot בחוברת העבודה Sub RefreshAllPivotTables() Dim PT As PivotTable עבור כל PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

תוכל לקרוא עוד על רענון טבלאות ציר כאן.

שנה אותיות גדולות בתאים שנבחרו לאותיות גדולות

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

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

'קוד זה ישנה את הבחירה לאותיות גדולות משנה ChangeCase() Dim Rng As Range For Every Rng In Selection.Cells אם Rng.HasFormula = False אז Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

שימו לב שבמקרה הזה אני משתמש ב-UCase כדי להגדיר את האותיות האותיות של הטקסט לאותיות רישיות.אתה יכול להשתמש ב-LCase לאותיות קטנות.

סמן את כל התאים עם הערות

השתמש בקוד הבא כדי להדגיש את כל התאים עם הערות בתוכם.

'קוד זה ידגיש תאים שיש להם הערות' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

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

הדגש תאים ריקים עם VBA

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

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

להלן קוד המאקרו של VBA:

'קוד זה ידגיש את כל התאים הריקים במערך הנתונים Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

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

כיצד למיין נתונים לפי עמודה אחת

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

Sub SortDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

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

ישנם גם שלושה פרמטרים מרכזיים המשמשים כאן:

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

קרא עוד על אופן מיון הנתונים ב-Excel באמצעות VBA.

כיצד למיין נתונים לפי מספר עמודות

נניח שיש לך מערך נתונים שנראה כך:

להלן הקוד למיון נתונים על סמך מספר עמודות:

Sub SortMultipleColumns() עם ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

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

הפלט ייראה כך:

כיצד לקבל רק את החלק המספרי ממחרוזת באקסל

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

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

כפי שמוצג מטה:

הנה קוד VBA שייצור פונקציה המחלצת את החלק המספרי ממחרוזת:

'קוד VBA זה יצור פונקציה כדי לקבל את החלק הנומרי ממחרוזת פונקציה GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) עבור i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) ואז Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

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

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

באופן דומה, הפונקציה שלהלן תקבל רק את חלק הטקסט ממחרוזת ב- Excel:

'קוד VBA זה יצור פונקציה כדי לקבל את חלק הטקסט ממחרוזת פונקציה GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) עבור i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) ואז Result = Result & Mid(CellRef, i, 1) הבא i GetText = Result End Function

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