התקשרו אלינו: 052-2928949
ימים א'-ה' בין השעות 9:00-17:00

התקשרו אלינו: 052-2928949 א-ה 9:00-17:00

יצירת גיליון בקרה באמצעות מאקרו אירוע

VBA Tip

יצירת גיליון בקרה באמצעות מאקרו אירוע

VBA Tip

בקרה ב 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

וכך נראה הפרויקט כולו בעורך:

קוד בקרה

 

 

 

 

 

 

 

 

רוצים ללמוד VBA מספר? לחצו כאן

מעוניינים ללמוד בנוחות ביתכם בקורס מקוון? לחצו כאן

דילוג לתוכן