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

[ל13]

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

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

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

2) רשמתי קוד VBA שאמור לעשות 2 פעולות ויש לי בו שגיאות תחביר או אחרות שאני לא מצליח ולא יודע איך לתקן ושמונעות מהפעולות להתבצע באופן תקין:

א. להסתיר כל שורה בגיליון שהתאריך הרשום בה בעמודה קבועה הינו ישן יותר מהיום הראשון של 2 שבועות העבודה הקודמים >>> המאקרו לא מבצע הסתרה כלל...
(דוגמא: היום יום ג' 12/6, הסתר כל רשומה שהתאריך שלה ישן מתאריכי 2 שבועות העבודה הקודמים, כלומר כל מה שקטן מיום א' 27/5).

ב. להפוך כל שורה בגיליון מנוסחה לערך (copy & paste values) שהתאריך הרשום בה בעמודה קבועה הינו ישן יותר מהיום הראשון של השבוע הנוכחי, במטרה להפחית את כמות הנוסחאות המיותרת בקובץ >>> המאקרו עושה את הפעולה אבל גם עבור היום הראשון של השבוע הנוכחי, לא כפי הנדרש.
(דוגמא: היום יום ג' 12/6, הפוך כל רשומה עם נוסחאות לערכים שהתאריך שלה ישן מתאריך היום הראשון של שבוע העבודה הנוכחי, כלומר כל מה שקטן מיום א' 10/6).

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

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

 

[u333]

תשובתי מתייחסת לשאלה 2

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

Sub Hide_and_copy_paste_values_OldWW()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    Dim cell As Range
    For Each cell In Range("F4:F500")
    '''''Copy & Paste Values for all previous work weeks lines
    If cell.Value < Now() - Weekday(Now(), 1) + 1 And cell.Value > 0 Then
        cell.EntireRow.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
    '''''Hide all previous weeks lines Excluding last 2 work weeks
    If cell.Value < Now() - (Weekday(Now(), 1) + 1) - 13 And cell.Value > 0 Then
    cell.EntireRow.Hidden = True
    End If
    Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[מיכאל אבידן]

ראיתי Select

‏ . . .

 

[ל13]

מצטער, לא מבין למה אתה מתכוון בשאלתך...

אני לא אמור להשתמש ב-select?

 

[מיכאל אבידן]

פנייתי לא כוונה אליך (התמקד בהיררכיית ההודעות) . . .

 

[ל13]

OK

 

[u333]

בעוונותי - פיפסתי

 

[ל13]

המון תודה, ההסתרה עובדת תקין, אבל עדיין יש בעיה בהדבקה

הי,
תודה רבה על העזרה!
ההסתרה עובדת מצוין. בדיוק כנדרש.
לגבי ה-copy + paste values עדיין לא נפתרה הבעיה.
בדיוק כמו קודם, למרות כל השורות המיותרות שהיו לי,
ההחלפה של נוסחאות בערכים פועלת, שלא כנדרש, גם על היום הראשון של השבוע הנוכחי (10/6/18), כאשר אני מבקש שהנוסחאות יישארו בכל השורות עם התאריכים של השבוע הנוכחי...
תוכל להגיד לי איזה עוד תיקון נדרש בקוד?
תודה.

 

[u333]

כעת נסה את הקוד הבא:

Sub Hide_and_copy_paste_values_OldWW()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
    Dim cell As Range
    For Each cell In Range("F4:F500")
    '''''Copy & Paste Values for all previous work weeks lines
    If cell.Value < Now() - Weekday(Now(), 1) And cell.Value > 0 Then
        cell.EntireRow.Copy
        cell.EntireRow.PasteSpecial Paste:=xlPasteValues
    End If
    '''''Hide all previous weeks lines Excluding last 2 work weeks
    If cell.Value < Now() - (Weekday(Now(), 1) + 1) - 13 And cell.Value > 0 Then
    cell.EntireRow.Hidden = True
    End If
    Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ל13]

עכשיו עובד מעולה! תודה רבה

 

[ל13]

ואגב...

הורדת לי את השורה בהתחלה שבוחרת את הגיליון שעליו המאקרו אמור לעבוד...
Sheets("Tool").Select
הוספתי אותה בחזרה כי יש עוד גיליונות בקובץ המקור...
צריך להגדיר לאיזה גיליון להתייחס... לא?
או שאני לא מבין כאן משהו עקרוני הרבה יותר?

 

[מיכאל אבידן]

אם כמות הרשומות עשויה להשתנות . . .

וחשובה לך הדינאמיות - נסה להריץ את הקוד שלהלן:

Sub Hide_and_copy_paste_values_OldWW_Micky()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = 0
    Dim cell As Range
    LR = Cells(Rows.Count, 5).End(xlUp).Row
    For Each cell In Range("F5:F" & LR)
          If cell.Value >= (Date - 14) - Application.Weekday((Date - 14), 1) + 1 And cell.Value > 0 Then
              cell.EntireRow.Copy
              cell.EntireRow.PasteSpecial Paste:=xlPasteValues
          Else
              cell.EntireRow.Hidden = 1
          End If
    Next
    Application.ScreenUpdating = 1
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[ל13]

המון תודה מיכאל. שאלה קטנה...

כשאתה מתשתמש בקוד שכתבת ב-Date, לדוגמא:
If cell.Value >= (Date - 14)
האם ה-Date הזה משקף את התאריך של היום ומחליף את ה-
Now()
בקוד שאני כתבתי?

 

[מיכאל אבידן]

DATE מייצג תאריך בלבד (מספר שלם) . . .

NOW מייצג תאריך+שעה?

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

כמבקשים לבדוק הפרש/פאזה/מרחק בין תאריכים אני

משתמש ב- DATE ולא ב- NOW.

 

[ל13]

אם הבנתי נכון, DATE ב-vba זהה ל-()TODAY בנוסחת אקסל?

כלומר בכל מקום שתכתוב DATE בקוד, הוא יתייחס לתאריך של היום הנוכחי?

 

[מיכאל אבידן]

לפני שבכלל מתחילים "להתעסק" עם VBA . . .‏

חשוב להכיר את המקש: [F1].

מציבים את הסמן על מילה (כמו: Date) > לוחצים [F1] ומתחילים לקרוא/ללמוד...