הבנת סוגי הנתונים של Excel VBA (משתנים וקבועים)

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

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

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

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

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

למה להשתמש במשתנים ב- VBA?

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

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

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

Sub AddFirstTenNumbers () Dim Var As Integer Dim i As Integer Dim k as Integer For i = 1 to 10 k = k + i Next i MsgBox k End Sub

ישנם שלושה משתנים בקוד לעיל - Var, אני, ו ק.

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

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

להלן מספר כללים שיש לזכור בעת מתן שמות למשתנים ב- VBA:

1. אתה יכול להשתמש באותיות, מספרים ופיסוק, אך המספר הראשון חייב להיות אלפבית.
2. אינך יכול להשתמש ברווח או נקודה בשם המשתנה. עם זאת, באפשרותך להשתמש בתו תחתון כדי להפוך את שמות המשתנים לקריאים יותר (כגון Interest_Rate)
3. אינך יכול להשתמש בתווים מיוחדים (#, $, %, &, או!) בשמות משתנים
4. VBA אינה מבחינה בין המקרה בשם המשתנה. אז 'ריבית' ו'ריבית 'זהים ל- VBA. אתה יכול להשתמש באותיות מעורבות כדי להפוך את המשתנים לקריאים יותר.
5. ל- VBA יש כמה שמות שמורים שבהם תוכלו להשתמש בשם משתנה. לדוגמה, אינך יכול להשתמש במילה 'הבא' כשם משתנה, מכיוון שהוא שם שמור ללולאה של ההמשך.
6. אורכו של שם המשתנה יכול להיות עד 254 תווים.

סוג נתונים של משתנים

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

סוג הנתונים שתקצה למשתנה יהיה תלוי בסוג הנתונים שאתה רוצה שהמשתנה הזה יחזיק.

להלן טבלה המציגה את כל סוגי הנתונים הזמינים שבהם תוכל להשתמש ב- Excel VBA:

סוג מידע	בתים בשימוש	טווח ערכים
בייט	1 בתים	0 עד 255
בוליאני	2 בתים	אמת או שקר
מספר שלם	2 בתים	-32,768 עד 32,767
ארוך (מספר שלם ארוך)	4 בתים	-2,147,483,648 עד 2,147,483,647
יחיד	4 בתים	-3.402823E38 עד -1.401298E -45 לערכים שליליים; 1.401298E-45 עד 3.402823E38 לערכים חיוביים
לְהַכפִּיל	8 בתים	-1.79769313486231E308 עד -4.94065645841247E-324 לערכים שליליים; 4.94065645841247E-324 עד 1.79769313486232E308 לערכים חיוביים
מַטְבֵּעַ	8 בתים	-922,337,203,685,477.5808 עד 922,337,203,685,477.5807
נקודה	14 בתים	+/- 79,228,162,514,264,337,593,543,950,335 ללא נקודה עשרונית; +/- 7.9228162514264337593543950335 עם 28 מקומות מימין לעשרוני
תַאֲרִיך	8 בתים	1 בינואר 100 עד 31 בדצמבר 9999
לְהִתְנַגֵד	4 בתים	כל התייחסות לאובייקט
מחרוזת (באורך משתנה)	10 בתים + אורך מחרוזת	0 עד כ -2 מיליארד
מחרוזת (באורך קבוע)	אורך חוט	1 עד כ- 65,400
וריאנט (עם מספרים)	16 בתים	כל ערך מספרי עד הטווח של כפול
וריאנט (עם תווים)	22 בתים + אורך מחרוזת	אותו טווח כמו למחרוזת באורך משתנה
משתמש הוגדר	משתנה	הטווח של כל אלמנט זהה לטווח סוג הנתונים שלו.

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

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

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

הצהרת סוגי נתונים משתנים

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

להלן דוגמה שבה הכרזתי סוגי נתונים שונים למשתנים שונים:

תת הצהרת משתנים () Dim X כמספר שלם Dim Email As String Dim First Name As String Dim Row מספר כמו Long Dim היום

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

ב 'Dim X כמספר שלם', הכרזתי על המשתנה X כסוג נתונים שלם.

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

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

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

הצהרת משתנה חובה (אופציה מפורשת)

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

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

כדי לוודא שאתה נאלץ להכריז על משתנים, הוסף את השורה הבאה לראש המודול שלך.

אפשרות מפורשת

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

יש יתרון עצום בשימוש באפשרות Explicit.

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

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

להלן דוגמה שבה שימוש ב'אופציה מפורשת 'מזהה את השגיאה (שלא הייתה יכולה להיצמד לולא השתמשתי ב'אופציה מפורשת')

Sub CommissionCalc () עמול שיעור דירוג ככפולה אם טווח ("A1"). ערך> 10000 ואז עמלת ערך = 0.1 אחרת עמלה Rtae = 0.05 קצה אם מס. בוקס "סך העמלה:" וטווח ("A1").

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

אם אני לא משתמש באפשרות Explicit, הקוד הזה יפעל וייתן לי את ערך העמלה הכולל הלא נכון (במקרה שהערך בתא A1 קטן מ -10000).

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

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

1. בסרגל הכלים של עורך VB, לחץ על כלים.
2. לחץ על אפשרויות.
3. בתיבת הדו -שיח אפשרויות, לחץ על הכרטיסייה עורך.
4. סמן את האפשרות - "דרוש הצהרה משתנה".
5. לחץ על אישור.

לאחר שהפעלת אפשרות זו, בכל פעם שאתה פותח מודול חדש, VBA הייתה מוסיפה אליו את השורה 'אפשרות מפורשת' באופן אוטומטי.

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

היקף המשתנים

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

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

היקף המשתנה קובע היכן ניתן להשתמש במשתנה ב- VBA,

ישנן שלוש דרכים להיקף משתנה ב- Excel VBA:

1. בתוך שגרת משנה אחת (משתנים מקומיים)
2. בתוך מודול (משתנים ברמת המודול)
3. בכל המודולים (משתנים ציבוריים)

הבה נבחן כל אחד מאלה בפירוט.

בתוך שגרת משנה אחת (משתנים מקומיים)

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

אינך יכול להשתמש בו בתת -שורות אחרות במודול.

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

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

בתוך מודול (משתנים ברמת המודול)

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

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

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

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

להלן דוגמה שבה יש לי שני קודים. כשאני מריץ את ההליך הראשון ולאחר מכן מפעיל את השני, הערך של 'i' הופך ל -30 (מכיוון שהוא נושא את הערך 10 מההליך הראשון)

בכל המודולים (משתנים ציבוריים)

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

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

 הנציבות הציבורית דירוג כפול

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

משתנים סטטיים (זה שומר על הערך)

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

אם אתה רוצה שהמשתנה ישמור על הערך, עליך להשתמש ב- סטָטִי מילת מפתח.

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

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

נוהל משנה 1 () Dim i כמספר שלם i = i + 10 MsgBox i End Sub

כעת אם אשתמש במילת המפתח הסטטית במקום ב- DIM, ומריץ את ההליך מספר פעמים, הוא ימשיך להציג ערכים במרווחים של 10. זה קורה כאשר המשתנה 'i' שומר על ערכו ומשתמש בו בחישוב.

נוהל משנה 1 () סטטי i כמספר שלם i = i + 10 MsgBox i End Sub

הצהרת קבועים ב- Excel VBA

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

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

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

לדוגמה, אם אתה מחשב עמלות ושיעור העמלה הוא 10%, תוכל ליצור קבוע (CommissionRate) ולהקצות לו את הערך 0.1.

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

להלן דוגמת קוד שבה הקצתי ערך לקבוע:

תת חישוב ועדת () עמלת עמלה ערך כעמלת קבוע כפולה שיעור כפול = 0.1 עמלה ערך = טווח ("A1") * עמלה דירוג מס נתוני עמלה ערך תת סוף

השורה הבאה משמשת להצהרת הקבוע:

שיעור העמלה ככפול = 0.1

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

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

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

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

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

אולי תאהב גם את מדריכי ה- VBA הבאים:

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