בקרה ב VBA
בעת יצירת מאקרו אירוע, אנו יכולים לשלוף את ערך התא שהצית את הקוד, באמצעות השימוש במילה השמורה Target.
אחת הבקשות הנפוצות מלקוחות היא יצירת גיליון בקרה ב VBA,
אשר מציג עבור כל שינוי בטבלת המקור את הערך הישן, הערך החדש, שמו של מבצע השינוי ותאריך השינוי.
זוהי טבלת המקור:
וכך נראית טבלת הבקרה:
אז איך ניגשים לפרויקט הזה?
ראשית, עלינו לדעת באיזה מבין הסוגים השונים של מאקרו אירוע נבחר.
מכיוון שטבלת התיעוד והבקרה מבקשת לדעת על ערכים שהשתנו,
ברור לנו לגמרי, שיש לבחור במאקרו אירוע מסוג Worksheet_Change.
הקוד שיכתב במאקרו ידע לקחת את שם המשתמש (מהאקסל או ממערכת ההפעלה), את התאריך, את כתובת התא ואת הערך החדש.
אך מכיוון שעלינו למלא גם את הערך הישן, אנחנו צריכים לקבל גם אותו, ואת זה מאקרו אירוע מסוג שינוי לא יודע לעשות,
משום שהוא מופעל אחרי שינוי התא.
אז מה עושים?
מפעילים מאקרו אירוע נוסף מסוג SelectionChange שמופעל בכל פעם שאנחנו בוחרים תא.
אז יש לנו שני מאקרואים – האחד שמופעל בכל פעם שאנחנו בוחרים תא, והשני שמופעל בכל פעם שאנחנו משנים ערך.
איך נראה המאקרו שמופעל בכל פעם שאנחנו בוחרים תא?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyVal = Target
End Sub
בכל פעם שאנחנו בוחרים תא בגיליון, אל תוך המשתנה MyVal נכנס הערך הנוכחי.
עכשיו ניגש לכתיבת המאקרו שיופעל בכל פעם שנערוך שינוי בערך התא (בניגוד למאקרו הקודם שמופעל בכל פעם שנערכת בחירה בתא) –
המאקרו הוא מאקרו אירוע מסוג Change שאמור להשתמש בערך שאוחסן במשתנה MyVal בעת שינוי הבחירה.
כדי שהמשתנה MyVal יוכר גם ברוטינה הנוכחית, נכריז על המשתנה MyVal כ Public בראש המודול:
Public MyVal
ועכשיו ניגש לכתוב את הקוד שיזין את הנתונים מטבלת המכירות (Data) לטבלת הבקרה (Audit).
אנחנו מעוניינים לתעד רק שינויים שנעשית בעמודת המחיר (B), ובתנאי שבתא היה קודם ערך,
ולכן, נבדוק ראשית האם הערך הוזן בעמודה B, ורק אם כן, נבדוק האם יש הבדל בין הערך שמאוחסן ב MyVal לבין הערך הנוכחי שמאוחסן ב Target.
רק במידה שכל התנאים התקיימו, נזין את המידע הדרוש לטבלה שנמצאת בגיליון Audit:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If MyVal <> Target And MyVal <> "" Then
With Sheets("Audit").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0) = Application.UserName
.Offset(1, 1) = Date
.Offset(1, 2) = Target.Address
.Offset(1, 3) = MyVal
.Offset(1, 4) = Target
End With
End If
End If
End Sub
אז מה בעצם אומר הקוד?
אל תוך גיליון Audit, מתחת לתא המלא האחרון, הזן את שם המשתמש, כפי שמופיע בתוכנת אקסל
אל התא שלידו, הזן את התאריך הנוכחי, לאחר מכן את כתובת התא שהצית את האירוע,
ואז את הערך שנמצא במשתנה MyVal, שמכיל את את הערך הקודם ובתא שלידו את הערך הנוכחי
וזו התוצאה:
מיטיבי הלכת יכולים לקצר את הקוד לקוד הבא:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If MyVal <> Target And MyVal <> "" Then
MyData = Application.UserName & "," & Date & "," & Target.Address & "," & MyVal & "," & Target
Sheets("Audit").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(, 5) = Split(MyData, ",")
End If
End If
End Sub
וכך נראה הפרויקט כולו בעורך:
