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

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

רענון המסך

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

Application.ScreenUpdating = False

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

כיבוי החישובים האוטומטיים

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

Application.Calculation = xlCalculationManual

וכדי להחזיר את החישוב, נשתמש ב

Application.Calculation = xlCalculationAutomatic

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

בחירת תאים (select/Activate) יכולה להאט מאוד את ריצת הקוד
הקוד הבא, שכותב ב 100,000 השורות הראשונות בעמודה A את המספרים מ-1 ועד 100,000:

For i = 1 To 100000
      Cells(i, 1).Select
      ActiveCell = i
Next

רץ אצלי במשך 4 דקות ו-9 שניות!
ואילו הקוד הזה, שעושה את אותה הפעולה, רק בלי לבחור את התא קודם:

For i = 1 To 100000
      Cells(i, 1) = i
Next

רץ במשך… תחזיקו חזק…
5 שניות בלבד!

אירועים

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

Application.EnableEvents = False

ולהחזיר מיד בסיומה:

Application.EnableEvents = True

השתמשו במבנה With

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

Range("a1").Select
ActiveCell = 1
ActiveCell.Font.Color = vbRed
ActiveCell.Borders.LineStyle = xlSolid

השתמשו ב:

With Range("a1") 
      .Select 
      .Value= 1 
      .Font.Color = vbRed 
      .Borders.LineStyle = xlSolid 
End With

שימוש יעיל במשתנים ולולאות

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

שימוש ב vbNullString במקום שימוש ב ""

vbNullString נחשב קבוע, לעומת "" שהוא מחרוזת באורך אפס, ולכן לאקסל קל יותר להשתמש ב vbnullstring מאשר במחרוזת באורך אפס.

סוג המשתנה

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

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

השתמשו ככל הניתן בפונקציות המובנות של אקסל

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

הפחיתו פעולות כתיבה וקריאה לקובץ

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

ולסיכום,

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