Best workaround for VBA Transpose array length limit?

by

Calculation is definitely going to be the bottleneck here, so (as RBarryYoung says) transposing the array entry-by-entry won’t really affect the speed at which your macro runs.

That said, there is a way to transpose a 2D row to a column (and vice versa) in constant time:

Private Declare Function VarPtrArray Lib "msvbvm60" Alias _
    "VarPtr" (ByRef Var() As Any) As Long
Private Declare Sub GetMem4 Lib "msvbvm60.dll" (src As Any, dest As Any)
Private Declare Sub GetMem8 Lib "msvbvm60.dll" (src As Any, dest As Any)

Sub test()
    Dim totalgoals() As Single
    Dim f As Single
    Dim i As Long, iter As Long

    'dimension totalgoals() with as many cells as we
    'could possibly need, then cut out the excess
    iter = 100000
    ReDim totalgoals(1 To 1, 1 To iter)
    For iter = iter To 1 Step -1
        f = Rnd
        If f > 0.2 Then
            i = i + 1
            totalgoals(1, i) = f
        End If
    Next iter
    ReDim Preserve totalgoals(1 To 1, 1 To i)

    'transpose by swapping array bounds in memory
    Dim u As Currency
    GetMem8 ByVal VarPtrArray(totalgoals) + 16, u
    GetMem8 ByVal VarPtrArray(totalgoals) + 24, _
            ByVal VarPtrArray(totalgoals) + 16
    GetMem8 u, ByVal VarPtrArray(totalgoals) + 24
End Sub

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  5. Levenshtein Distance in VBA [closed]
  6. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  7. How do I base64 encode a string efficiently using Excel VBA?
  8. Assign event handlers to controls on user form created dynamically in VBA
  9. Programmatic Access To Visual Basic Project Is Not Trusted
  10. Can I make this macro more efficient or faster?
  11. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  12. How to get the background color from a Conditional Formatting in Excel using VBA
  13. VBA Excel Populate ListBox with multiple columns
  14. VBA Integer Overflow at 70,000
  15. How to count up text of a different font colour in excel
  16. How to Lock the data in a cell in excel using vba
  17. How to use Events with Option Button Controls on Userform [duplicate]
  18. How do I use variables to set properties in VBA (Excel)
  19. VBA: Else without If Error
  20. Workbooks.Open Method in VBA
  21. Windows().Activate works on every computer except one
  22. How to highlight a cell using the hex color value within the cell?
  23. Convert RTF (Rich Text Format) code into plain text in Excel
  24. lastrow and excel table.
  25. Open a workbook from VBA and disable Workbook_Open() code?
  26. Creating form programmatically in the module using vba
  27. How to filter listbox values based on a Textbox value
  28. Find and FindNext for Excel VBA
  29. How do I get a list of unique values from a range in Excel VBA?
  30. Query my excel worksheet with VBA

Leave a Comment