Forum Discussion

Sandeeep's avatar
Sandeeep
Brass Contributor
Oct 21, 2022

VBA Optimization help for Large string manipulation related to memory management?

TLDR; The issue I need to solve. How do I optimize the code, so the runtime for each 250 is approximately the same? i.e, about say 15 secs? consistently from 0 - 35,000 conversations. and even if...
excel
Macros and VBA
Office Scripts
JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 21, 2022
If you want us to comment on the performance of your code we need, well, your code. And some nonsense but relevant sample data.
Sandeeep's avatar
Sandeeep
Brass Contributor
Oct 21, 2022
Kay, kinda sent it.
I have to remove tons of stuff tho. But should give a rough idea.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 21, 2022

    Sandeeep 

    If you need this speeded up, the best thing to do is process everything in memory first, by using arrays. Then after it has all been worked through, push out the entire array to the worksheet in a single line of code. This may be orders of magnitudes faster that what you currently have.

    But a very simple first step would be to turn off screen-updating and calculation prior to your loop and turn those back on afterwards:

    '...Code
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Loop here
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'Some more code
    • Sandeeep's avatar
      Sandeeep
      Brass Contributor
      Oct 21, 2022

      JKPieterse 

       

      Right, I did try that 

      Private Sub OptimizedMode(ByVal enable As Boolean)
     Application.EnableEvents = Not enable
     Application.Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)
     Application.ScreenUpdating = Not enable
     Application.EnableAnimations = Not enable
     ' Application.DisplayStatusBar = Not enable
     Application.PrintCommunication = Not enable
End Sub

       

      From the website here.

      https://vbacompiler.com/optimize-vba-code/

       

      The idea you are providing with arrays, is point 6 from this blog post correct?

      https://techcommunity.microsoft.com/t5/excel/9-quick-tips-to-improve-your-vba-macro-performance/m-p/173687

       

      Dim vArray As Variant
Dim iRow As Integer
Dim iCol As Integer
Dim dValue As Double
vArray = Range("A1:C10000").Value2 ‘ read all the values at once from the Excel cells, put into an array 

For iRow = LBound(vArray, 1) To UBound(vArray, 1)
  For iCol = LBound(vArray, 2) To UBound(vArray, 2)
    dValue = vArray (iRow, iCol)
    If dValue > 0 Then 
      dValue=dValue*dValue ‘ Change the values in the array, not the cells 
    vArray(iRow, iCol) = dValue
  End If
Next iCol
Next iRow
Range("A1:C10000").Value2 = vArray ‘ writes all the results back to the range at once

       

      So, I haven't tried this yet.

      I'm confused with this part from above

      For iRow = LBound(vArray, 1) To UBound(vArray, 1)
  For iCol = LBound(vArray, 2) To UBound(vArray, 2)
    dValue = vArray (iRow, iCol)

       

      With regards to Lbound(vArray, 1) & again the loop Lbound(vArray, 2)

      What is 1 & 2 referring to?

      and when we put them back with 

      Range("A1:C10000").Value2 = vArray

       

      is (vArray, 1) referring to A1? & the second loop is B1?

      i.e a1 a2 a3 a4... then b1 b2 b3 b4....

       

      So is C column not being looked at? but I'mma assume it is?

       

      I understood the examples here

      https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lbound-function

       

      and here,

      https://www.automateexcel.com/vba/ubound-lbound-function/

       

      but didn't understand what and where I need to modify.

      as is.

      should I make vArray = Search_Range( entire H column) with tons of conversations,

      then, cell = split(vArray(iRow,iCol), "||") then run my for loops?

       

      if so, how am I putting the values back into the 25 columns the specific data needs to be entered into?

      Cause, I am using cell(1, 20) etc, to reference which column I need to put it in.

       

      and idk how to loop and assign properly.

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 21, 2022
        1 refers to the rows, 2 refers to the columns. So the example loop runs through all rows and for each row through all columns. I agree your macro is quite a lot of work to convert to one that uses arrays.
        I wonder whether this could be pulled off using Data, From Range/Table, as you seem to be splitting up much of the texts into different columns.

Resources