PRODUCTION ISSUE with Macro performance

  • Hi,


    I've developed 2 macros that switches data changes between SheetOld & SheetNew. We have a need to embed these macros into macro holders provided by our application which is SAP.


    Macro1 formats SheetOld into SheetNew by reducing number of columns drastically. our custom macro formats each row of SheetOld into 10 rows of sheetNew by reducing 40 columns into 4 (columns is what matters for our users but not rows).


    Macro2 reflects the changes from SheetNew to SheetOld as per macro coding.


    initially, we've around 11 key fields, 40 data fields.
    so, SheetOld will look as follows


    K1 K2 K3........K11...D1 D2 D3....D40


    where K = Keyfields D = data fields


    after embedding custom code into SAP macros, We tried this for 40 rows of data. (means, 40 rows 51 columns). Our Custom macro formed 400 rows and 15 columns (11 keyfields + 4 data fields) into SheetNew. here, we can execute further calculations. fine till this point.



    Now, we have a problem when dealing with 100 rows. (means, 100 rows 51 columns).
    Our custom macro had formed 1000 rows and 15 columns as expected with accurare data.

    at this point, When we start executing further calculations, system is freezing up doing nothing. each time, we had to kill the process. when we looked into CPU usage, we can see that excel.exe process is using 99% of CPU.
    Hope it is clear.


    Am not an expert in VB coding but the code does what we need.
    Is this performance problem ? Does code need to be tweaked to improve performance?


    Greatly Appreciate your help.


    Note: 1. By disabling custom code in SAP macros, we executed 100 rows 40 columns w/o any issues.
    2. when executed custom macros outside of SAP for 1000 rows 4 columns, both macros took just 2 seconds to switch data between sheets.
    3. Whenever we start a further calculation, SAP 1st runs these macros


    Here is the SAP macro holders where we need embed custom code.


    [VBA]


    Sub SAPAfterDataPut()
    'Performance Optimizing
    Dim calc_mode, update_mode
    calc_mode = Application.Calculation
    update_mode = Application.ScreenUpdating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


    'Protection
    Dim protection As Boolean
    protection = Sheets("SEM-BPS 1").ProtectContents
    If protection = True Then
    Sheets("SEM-BPS 1").Unprotect ("SAP")
    End If



    '
    'put your own coding here
    'If you are using own modules for your coding do NOT
    'start the name of the module with "SAP_"!


    ' [This is the place holder for Macro1 that formats data and puts in SheetNew from SheetOld
    ' I use "Application.Calculation = false" here at the starting of my custom code

    '
    '


    'Performance Optimizing
    Application.Calculation = calc_mode
    Application.ScreenUpdating = update_mode
    'Protection
    If protection = True Then
    Sheets("SEM-BPS 1").Protect ("SAP")
    End If


    End Sub


    Sub SAPBeforeDataGet()
    'Performance Optimizing
    Dim calc_mode, update_mode
    calc_mode = Application.Calculation
    update_mode = Application.ScreenUpdating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False


    'Protection
    Dim protection As Boolean
    protection = Sheets("SEM-BPS 1").ProtectContents
    If protection = True Then
    Sheets("SEM-BPS 1").Unprotect ("SAP")
    End If



    '
    'put your own coding here
    'If you are using own modules for your coding do NOT
    'start the name of the module with "SAP_"!


    ' This is the place holder for Macro2 that reflects changes from SheetNew to SheetOld


    '
    '


    'Performance Optimizing
    Application.Calculation = calc_mode
    Application.ScreenUpdating = update_mode
    'Protection
    If protection = True Then
    Sheets("SEM-BPS 1").Protect ("SAP")
    End If


    End Sub


    Sub SAPSetSaved()
    ' sets the saved flag to true after writing data
    ' this macro is used by the bps - do not delete it!
    Application.ThisWorkbook.Saved = True
    End Sub


    Sub SAPCreateCode(Name)


    Dim BPSTable As Object
    Dim BPSTableWa As Object
    Dim cont As Object
    Dim RowIndex As Integer
    Dim code As String
    Dim vbproject As Object
    Dim vbcomponent As Variant
    Dim vbcomponents As Object
    Dim codemodule As Object

    Set cont = ThisWorkbook.Container
    Set BPSTable = cont.Tables("CODE").Table
    Set vbproject = ThisWorkbook.vbproject
    Set vbcomponents = vbproject.vbcomponents


    ' delete the existing module
    For Each vbcomponent In vbcomponents
    If vbcomponent.Name = Name Then
    vbcomponents.Remove (vbcomponent)
    Exit For
    End If
    Next


    ' create the module
    Set vbcomponent = vbproject.vbcomponents.Add(1)

    On Error GoTo on_error
    vbcomponent.Name = Name

    Set codemodule = vbcomponent.codemodule


    For RowIndex = 1 To BPSTable.Rows.Count
    Set BPSTableWa = BPSTable.Rows(RowIndex)
    code = code + Format(BPSTableWa(1)) + Chr(10)
    Next

    codemodule.AddFromString code


    'just in case
    ThisWorkbook.Sheets("SEM-BPS 1").Activate

    on_error:
    ' do nothing
    End Sub
    [/VBA]

  • Re: PRODUCTION ISSUE with Macro performance - **URGENT**


    Hi Clemson,


    Please use Code tags(read the posting rules).


    Before the code type: ['vba']...here type vba without quotes


    ....your code here


    [/'vba'].....here again type vba without quotes.


    Before someone can look into your problem this is necessary..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!