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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• עבור לכרטיסייה מפתחים.
• לחץ על מאקרו.

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

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

הדרכה קשורה: דרכים שונות להפעלת מאקרו ב- Excel.

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

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

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

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

דוגמאות למאקרו של Excel

להלן דוגמאות מאקרו מכוסות במאמר זה:

הסר את כל גליונות העבודה ב- One Go

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

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

'קוד זה יסיר את כל הגיליונות בחוברת העבודה Sub UnhideAllWoksheets () Dim ws as גליון עבודה עבור כל ws ב- ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible הבא ws סוף תת

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

להלן הדרכה מפורטת כיצד להשתמש בשיטות שונות לביטול הסתרת גיליונות ב- Excel.

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

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

'מאקרו זה יסתיר את כל גליון העבודה למעט הגיליון הפעיל Sub HideAllExceptActiveSheet () Dim ws as גליון עבודה עבור כל ws ב- ThisWorkbook.Worksheets אם ws.Name ActiveSheet.Name ואז ws.Visible = xlSheetHidden הבא ws סוף תת

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

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

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

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

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

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

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

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

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

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

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

הסר את כל השורות והעמודות

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

'קוד זה ישמור כל גליון עבודה כ- PDF משנה נפרד SaveWorkshetAsPDF () Dim ws as גליון עבודה עבור כל ws בגליונות ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub 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

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

הגן/נעל תאים בעזרת נוסחאות

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

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

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

הדרכה קשורה: כיצד לנעול תאים ב- Excel.

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

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

'קוד זה יגן על כל הגיליונות בחוברת העבודה Sub ProtectAllSheets () Dim ws as גליון עבודה עבור כל ws בגליונות ws.Protect הבא 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). בחר הבא i סוף Sub

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

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

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

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

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

'קוד זה יכניס חותמת זמן לתא הסמוך גליון עבודה פרטי משנה_שינוי (ByVal Target as Range) על שגיאה GoTo Handler If Target.Column = 1 And Target.Value "" ואז Application.EnableEvents = False Target.Offset (0, 1) = פורמט (עכשיו (), "dd-mm-yyyy hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub

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

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

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

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

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

'קוד זה יסמן שורות חלופיות בבחירה Sub HighlightAlternateRows () Dim Myrange כטווח Dim Myrow כטווח Set Myrange = בחירה לכל Myrow ב- Myrange.Rows אם Myrow.Row Mod 2 = 1 ואז Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

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

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

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

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

'קוד זה ידגיש את התאים עם מילים שגיאות כתיב Sub HighlightMisspelledCells () Dim cl כטווח עבור כל cl ב- ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) ואז cl.Interior.Color = vbRed סוף אם הבא cl סיום תת

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

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

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

'קוד זה ירענן את כל טבלת הצירים בחוברת המשנה RefreshAllPivotTables () Dim PT כ- PivotTable עבור כל PT ב- ActiveSheet.PivotTables PT.RefreshTable הבא PT End Sub

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

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

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

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

'קוד זה ישנה את הבחירה לרשת משנה גדולה () ריג' עמום כטווח עבור כל Rng בבחירה. תאים אם Rng.HasFormula = False ואז Rng.Value = UCase (Rng.Value) מסתיים אם תת Rng הקצה הבא

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

הדגש את כל התאים עם הערות

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

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

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

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

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

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

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

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

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

כיצד למיין נתונים לפי טור יחיד

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

תת SortDataHeader () טווח ("DataRange"). מפתח מיון 1: = טווח ("A1"), Order1: = xlAscending, Header: = xlYes End Sub Sub

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

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

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

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

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

נניח שיש לך מערך נתונים כפי שמוצג להלן:

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

תת SortMultipleColumns () עם ActiveSheet.Sort .SortFields.Add Key: = טווח ("A1"), סדר: = xlAscending .SortFields.Add Key: = טווח ("B1"), סדר: = xlAscending .SetRange טווח ("A1 : C13 ") .Header = xlYes. החל על סוף עם קצה משנה

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

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

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

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

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

משהו כפי שמוצג להלן:

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

קוד VBA זה ייצור פונקציה בכדי לקבל את החלק המספרי ממחרוזת פונקציה GetNumeric (CellRef כמחרוזת) Dim StringLength כמספר שלם StringLength = Len (CellRef) For i = 1 ל- StringLength If IsNumeric (Mid (CellRef, i, 1) ) ואז תוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i GetNumeric = פונקציית סיום התוצאה

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

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

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

קוד VBA זה ייצור פונקציה כדי לקבל את חלק הטקסט ממחרוזת פונקציה GetText (CellRef כמחרוזת) Dim StringLength כמספר שלם StringLength = Len (CellRef) For i = 1 ל- StringLength אם לא (IsNumeric (Mid (CellRef, i, 1))) ואז תוצאה = תוצאה ואמצע (CellRef, i, 1) הבא i GetText = פונקציית סיום התוצאה

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