✅ עבודה עם גליונות עבודה באמצעות Excel VBA (מוסבר עם דוגמאות)

תוכן העניינים

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

מלבד תאים וטווחים, עבודה עם דפי עבודה היא תחום נוסף שכדאי שתדע על השימוש בו ב- VBA ביעילות ב- Excel.

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

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

אז בואו נתחיל.

כל הקודים שאני מזכיר במדריך זה צריכים להיות ממוקמים בעורך VB. עבור לסעיף 'היכן לשים את קוד VBA' כדי לדעת כיצד הוא פועל.

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

ההבדל בין דפי עבודה לגיליונות ב- VBA

ב- VBA יש לך שני אוספים שיכולים להיות קצת מבלבלים לפעמים.

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

ב- Excel VBA:

אוסף 'דפי העבודה' יתייחס לאוסף של כל אובייקטים של דף העבודה בחוברת עבודה. בדוגמה שלמעלה, אוסף גליונות העבודה יכלול שלושה גליונות עבודה.
אוסף 'גיליונות' יתייחס לכל גליונות העבודה וכן לגיליונות התרשימים בחוברת העבודה. בדוגמה שלעיל, הוא יכלול ארבעה אלמנטים - 3 דפי עבודה + דף תרשים אחד.

אם יש לך חוברת עבודה שיש בה רק דפי עבודה וללא דפי תרשים, אוסף 'דפי עבודה' ו'גיליונות 'זהה.

אך כשיש לך גיליון תרשים אחד או יותר, אוסף 'גיליונות' יהיה גדול יותר מאוסף 'דפי העבודה'

גיליונות = גליונות עבודה + גליונות תרשים

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

אז אם עליך להתייחס לדפי עבודה בלבד, השתמש באוסף 'דפי העבודה', ואם עליך להתייחס לכל הגיליונות (כולל דפי תרשים), השתמש באוסף 'דפי עבודה'.

במדריך זה אשתמש באוסף 'דפי העבודה' בלבד.

הפניה לדף עבודה ב- VBA

ישנן דרכים רבות ושונות שבהן תוכל להשתמש כדי להפנות לדף עבודה ב- VBA.

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

שימוש בשם גליון העבודה

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

לדוגמה, נניח שיש לך חוברת עבודה עם שלושה דפי עבודה - גיליון 1, גיליון 2, גיליון 3.

ואתה רוצה להפעיל את גיליון 2.

תוכל לעשות זאת באמצעות הקוד הבא: Sub ActivateSheet () גליונות עבודה ("Sheet2"). הפעל End Sub

הקוד לעיל מבקש מ- VBA להתייחס ל- Sheet2 באוסף גליונות העבודה ולהפעיל אותו.

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

Sub ActivateSheet () Sheets ("Sheet2"). הפעל End Sub

שימוש במספר האינדקס

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

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

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

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

הקוד שלהלן יפעיל את גיליון 2:

Sub ActivateSheet () גליונות עבודה (2). הפעל End Sub

שים לב שהשתמשנו באינדקס מספר 2 ב דפי עבודה (2). זה יתייחס לאובייקט השני באוסף דפי העבודה.

עכשיו, מה קורה כאשר אתה משתמש ב -3 כמספר האינדקס?

הוא יבחר את גיליון 3.

אם אתה תוהה מדוע הוא בחר ב- Sheet3, מכיוון שהוא בבירור האובייקט הרביעי.

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

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

להיפך, אם אתה משתמש ב- Sheets, Sheets (1) יתייחס ל- Sheets1, Sheets (2) יתייחס ל- Sheet2, Sheets (3) יתייחס לתרשים 1 ו- Sheets (4) יתייחס ל- Sheet3.

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

הערה: מספר האינדקס עובר משמאל לימין. אז אם אתה מעביר את גיליון 2 משמאל לגיליון 1, אז גליונות עבודה (1) יתייחסו לגיליון 2.

שימוש בשם קוד גליון העבודה

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

ואם שם הגיליון השתנה, הקוד שלך לא יפעל עד שתשנה את שם גליון העבודה גם בקוד VBA.

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

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

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

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

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

כעת תוכל להשתמש באוסף גליונות העבודה כדי להתייחס לדף העבודה או להשתמש בשם הקוד.

לדוגמה, שני השורות יפעילו את גליון העבודה.

דפי עבודה ("שם גיליון"). הפעל את שם הקוד. הפעל

ההבדל בשני אלה הוא שאם תשנה את שם גליון העבודה, הראשון לא יעבוד. אבל השורה השנייה תמשיך לעבוד אפילו עם השם שהשתנה. השורה השנייה (באמצעות ה- CodeName) היא גם קצרה יותר וקלה יותר לשימוש.

הפניה לדף עבודה בחוברת עבודה אחרת

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

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

Sub SheetActivate () חוברות עבודה ("דוגמאות.קסלסקס"). גליונות עבודה ("גיליון 1"). הפעל קצה משנה

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

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

הוספת דף עבודה

הקוד שלהלן יוסיף גליון עבודה (כגיליון העבודה הראשון - כלומר, כגיליון השמאלי ביותר בכרטיסיית הגיליון).

Sub AddSheet () גליונות עבודה .הוסף Sub Sub

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

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

Sub AddSheet () גליונות עבודה.הוסף לפני: = גליונות עבודה ("גיליון 2") סיום תת

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

באופן דומה, אתה יכול גם להוסיף גיליון אחרי גליון עבודה (נניח גיליון 2), באמצעות הקוד שלהלן:

Sub AddSheet () גליונות עבודה.הוסף לאחר: = גליונות עבודה ("גיליון 2") סיום תת

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

Sub AddSheet () Dim SheetCount As Integer SheetCount = Sheets.Count Sheets.Add After: = גליונות עבודה (SheetCount) End Sub

מחיקת דף עבודה

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

תת מחיקת גיליון () ActiveSheet. מחק סיום משנה

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

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

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = תת סוף סוף

כאשר Application.DisplayAlerts מוגדר כ- False, הוא לא יראה לך את הודעת האזהרה. אם אתה משתמש בו, זכור להחזיר אותו ל- True בסוף הקוד.

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

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

Sub DeleteSheet () גליונות עבודה ("Sheet2"). מחק Sub Sub

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

תת מחיקת גיליון () גיליון 5. מחק סיום משנה

שינוי שם דפי העבודה

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

הקוד הבא ישנה את שם גיליון 1 ל'סיכום '.

Sub RenameSheet () גליונות עבודה ("גיליון 1"). שם = "סיכום" קצה משנה

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

לדוגמה, אם ברצונך להכניס ארבעה גיליונות עם השם2021-2022 Q1,2021-2022 Q2,2021-2022 Q3, ו-2021-2022 Q4, תוכל להשתמש בקוד שלהלן.

Sub RenameSheet () Dim Dimheets Sheets As Integer Countsheets = גליונות עבודה.ספור עבור i = 1 עד 4 גליונות עבודה.הוסף לאחר: = גליונות עבודה (ספירות + i - 1) גליונות עבודה (ספירות + i) .Name = "2018 Q" & i הבא i סיום תת

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

הקצאת אובייקט גליון עבודה למשתנה

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

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

להלן הקוד שיוסיף 2021-2022 כקידומת לכל שמות גליון העבודה.

Sub RenameSheet () Dim Ws as גליון עבודה עבור כל Ws בגליונות עבודה Ws.Name = "2018 -" & Ws.Name הבא Ws End Sub

הקוד לעיל מכריז על משתנה Ws כסוג גליון העבודה (באמצעות השורה 'Dim Ws as גליון עבודה').

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

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

בקוד שלהלן, אנו מקצים את המשתנה Ws ל- Sheet2 ומשתמשים בו כדי לגשת לכל המאפיינים של Sheet2.

Sub RenameSheet () Dim Ws as Set גליון עבודה Ws = גליונות עבודה ("גיליון 2") Ws.Name = "סיכום" Ws.Protect End Sub

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

שים לב שהקוד מכריז על האובייקט Ws כמשתנה מסוג גליון עבודה (באמצעות השורה Dim Ws as גליון עבודה).

הסתר גליונות עבודה באמצעות VBA (מוסתר + מוסתר מאוד)

הסתרת וסתירת גליונות עבודה ב- Excel היא משימה פשוטה.

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

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

אתה יכול לעשות זאת באמצעות VBA.

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

Sub HideAllExcetActiveSheet () Dim Ws כגיליון עבודה עבור כל Ws ב- ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name ואז Ws.Visible = xlSheetVeryHidden הבא Ws End Sub

בקוד לעיל, המאפיין Ws.Visible משתנה ל- xlSheetVeryHidden.

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

אם אתה רק רוצה להסתיר גיליונות שאפשר להסתיר אותם בקלות, השתמש בקוד שלהלן:

Sub HideAllExceptActiveSheet () Dim Ws כגיליון עבודה עבור כל Ws ב- ThisWorkbook.Worksheets אם Ws.Name ActiveSheet.Name ואז Ws.Visible = xlSheetHidden הבא Ws סוף תת

הקוד שלהלן יבטל את הסתרת כל גליונות העבודה (מוסתרים ומוסתרים מאוד).

Sub UnhideAllWoksheets () Dim Ws as גליון עבודה עבור כל Ws ב- ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible הבא Ws End Sub

מאמר קשור: הסרת כל הגיליונות ב- Excel (בבת אחת)

הסתר גיליונות על סמך הטקסט שבו

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

אתה יכול לעשות זאת באמצעות פונקציית VBA INSTR.

הקוד להלן יסתיר את כל הגיליונות למעט אלה שיש בהם את הטקסט2021-2022.

Sub HideWithMatchingText () Dim Ws כגיליון עבודה עבור כל Ws בגליונות עבודה אם InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 ואז Ws.Visible = xlSheetHidden End אם Ws הבא מסתיים תת

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

הקוד לעיל בודק אם יש את השם text2021-2022 בשם. אם זה קורה, שום דבר לא קורה, אחרת גליון העבודה מוסתר.

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

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

באמצעות 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 True

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

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

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

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

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

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

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

יצירת תוכן עניינים של כל גליונות העבודה (עם היפר -קישורים)

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

Sub AddIndexSheet () גליונות עבודה.הוספת ActiveSheet.Name = "אינדקס" עבור i = 2 ל- Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = גליונות עבודה (i) .Name & "! A1", _ TextToDisplay: = גליונות עבודה (i) .Name הבא i End Sub

הקוד לעיל מוסיף דף עבודה חדש ושמו אינדקס.

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

היכן לשים את קוד ה- VBA

תוהה לאן קוד VBA הולך בחוברת העבודה שלך ב- Excel?

ל- Excel יש תומך תומך ב- VBA שנקרא עורך VBA. עליך להעתיק ולהדביק את הקוד בחלון קוד המודול של עורך VB.

להלן השלבים לביצוע פעולה זו:

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