TRANSPOSE Changes Array Dimension

Here’s an odd one, I think. I have code that creates a two-dimensional array and I want to use Application.WorksheetFunction.Transpose to switch the rows and columns.

Sub TestTrans()
   
    Dim aTest(0 To 3, 0 To 1) As Long
    Dim vaTrans As Variant
    Dim i As Long, j As Long
   
    For i = 0 To 3
        For j = 0 To 1
            aTest(i, j) = (10 ^ i) * (j + 1)
        Next j
    Next i
   
    vaTrans = Application.WorksheetFunction.Transpose(aTest)
   
    Stop
   
End Sub

No problem. I check the locals window at Stop and it seems to have done the trick. I didn’t know it converted it to one-based arrays, but I guess that’s OK.

I ran into a situation today where my two-dimensional array only had one column dimension. When I ran it through Transpose, it came back as a one-dimensional array.

That’s not what I want. And it broke my code. I can’t tell you how much code I have that uses Transpose. Honestly, I can’t! That’s the problem. If I knew, I would go fix it.

Although the above simple example doesn’t demonstrate it, I need to Transpose because I’m using a dynamic array. I have to keep the last dimension variable or Redim Preserve won’t work. But in reality the dynamic portion of my array is the “row” portion, so I transpose it at the end. I guess I’ll have to go steal Chip’s transpose code and try to find all the places I’ve used this method.

So who’s got code that searches all the VBA on my hardrive?

Posted in Uncategorized

19 thoughts on “TRANSPOSE Changes Array Dimension

  1. >>So who’s got code that searches all the VBA on my hardrive?

    I think I have seen something like that – not that me thinking it is going to help you! Maybe someone else will know

  2. >>So who’s got code that searches all the VBA on my hardrive?
    Me.
    I wrote a library harvester back in 2001 – it trawls your hard drive(s) looking for DOT, DOC etc XLS, XLA etc BAS, CLS, FRM etc.
    Builds it into a Looong string (think of library MARC records) which is then searchable (by Instr) and pops up a 2-pane dialogue, hits in left-pane, clicked-on procedure in right pane.

    Probably do a good trick of locating your errant codes.
    Email me if you’d like to try it and I’ll dig it up.
    Last time I ran it it created a 22MB BLOB, but the search of that Blob takes < 1 second.
    I use it when I’m thinking “I *know* I used that once, but where?”

    Try the YouTube video at http://www.youtube.com/watch?v=nsP9eLACk04
    This is one of series of videos on my “Proje”ct-Management application.
    Full details at http://www.ChrisGreaves.com, Resources, Videos, scroll down to “Proje”.

  3. John: Interesting, that nested array stuff. The only reason I use arrays is to write to a range or to fill a listbox. I wonder how the nested ones would work in those situations. I’m guessing they won’t.

    Chris: Email on the way.

  4. Dick,

    True, but as long as your “inner” arrays are the same length, you can get Excel to convert the array-of-arrays to a plain 2-D array for you by using Application.Index().

    If I’m building up a table to be written to a range, I have an easier time if I can think of it as a list of rows rather than a literal 2-D array, even if it has to become a 2-D array eventually. It also lets me separate the logic for building a row from the logic for compiling the rows into a table.

  5. I can’t believe I’ve overlooked the use of “Stop” all this time.

    You’ll laugh, but I’ve been setting a breakpoint on Debug.Print.

    If condition Then
    Debug.Print “asd”
    End If

    Could have been doing
    If condition Then Stop

    Longcuts… opposite of shortcuts

  6. Andy: Funny thing is I’m aware of Assert, but don’t use it because it works the opposite to the way I’d expect it to work – so usually spend twice the amount of time I intended. Every time I think I’ve got the rule sussed in my head, I get it wrong. Longcuts to the rescue.

  7. “I ran into a situation today where my two-dimensional array only had one column dimension. When I ran it through Transpose, it came back as a one-dimensional array.”

    That has always been the behavior of WorksheetFunction.Transpose. I rely on it to convert a N rows by 1 column cell range to a 1D array of N elements. And, if the user provides the data in a 1 row by N columns range, use .Transpose(.Transpose(…)) to get the 1D array! {grin}

    “That’s not what I want. And it broke my code. I can’t tell you how much code I have that uses Transpose. Honestly, I can’t! That’s the problem. If I knew, I would go fix it.

    Although the above simple example doesn’t demonstrate it, I need to Transpose because I’m using a dynamic array. I have to keep the last dimension variable or Redim Preserve won’t work. But in reality the dynamic portion of my array is the “row” portion, so I transpose it at the end.”

    I don’t know how it broke existing code because, as already mentioned, it has always worked that way. To resize the ‘row’ dimension of an array, use transpose twice as in

    Option Explicit

    Function ArrLen(X, Optional NbrDim As Integer = 1)
        On Error Resume Next
        ArrLen = UBound(X, NbrDim) – LBound(X, NbrDim) + 1
        End Function
    Sub doTranspose()
        Dim X: X = Selection.Value          ‘X is (1 to 8, 1 to 1)
       With Application.WorksheetFunction
        Dim Y: Y = .Transpose(X)            ‘Y is (1 to 8)
       ReDim Preserve Y(ArrLen(Y) * 2 – 1) ‘Y is (0 to 15)
       Dim Z: Z = .Transpose(Y)            ‘Z is (1 to 16, 1 to 1)
           End With
        Stop
        End Sub

    On the subject of ‘array of arrays,’ from an initial cut in August 2010:
    The power of variants
    http://www.tushar-mehta.com/publish_train/book_vba/08_variants.htm
    particularly the sections
    Understanding a variant – implementing an array of arrays
    and
    Preserve existing content while resizing any dimension of a matrix

  8. So who’s got code that searches all the VBA on my hardrive?

    That would be me: kludgemeister to the rich and famous.

    The code snippet below was ripped straight out of an existing workbook. It is not ‘clean’ code, it’s heavily linked to a specific spreadsheet with:

    A named range called StartFolder, which might be (say) “C:DEV”;
    A named range called SearchSubFolders, which might be the Linked Cell of a checkbox control;
    A named range called SearchString, which might be (say) “WorksheetFunction.Transpose”
    A named range called DataAnchor, marking the cell at the top left of the output range displaying a table of results;
    A reference to Microsoft Visual Basic for Applications Extensibility;

    I used the following column headings for the output:

    Filename
    Full Path
    Object
    Line
    Additional Information

    Feel free to use your own.

    Private Sub SearchFolder()

    Dim rng As Excel.Range
    Dim rngHlink As Excel.Range
    Dim sFolder As String
    Dim sFilter As String
    Dim sSearch As String
     
    Dim i As Long
    Dim iCount As Long

    Dim wkb As Workbook
    Dim vbc As VBComponent
    Dim strFile As String
    Dim lngStartLine As Long
    Dim lngStartCol As Long
    Dim xCalc As XlCalculation
    Dim blnEvents As Boolean
    Dim bWasOpen As Boolean
    Dim lType As vbext_ComponentType
    Dim sType As String
    Dim myReference As Reference

        sFolder = ThisWorkbook.Names!StartFolder.RefersToRange.Value
        sFilter = “*.xls; *.xla”
        sSearch = ThisWorkbook.Names!SearchString.RefersToRange.Value
       
        If sSearch = “” Then
           
            With ThisWorkbook.Names!SearchString.RefersToRange
           
                .Interior.Color = 255
                .Select
                MsgBox “Please fill in a word or phrase to search on.”, vbExclamation, “Cannot search the files…”
               
                For i = 0 To 255 Step 4
                    Application.Wait Now + 1 / 24 / 2600 / 100
                    .Interior.Color = (i * 256 * 256) + (i * 256) + 255
                Next i
               
                .Interior.Color = &HFFFFFF
               
            End With
            GoTo ExitSub
           
        End If
     
        Set rng = ThisWorkbook.Names!DataAnchor.RefersToRange
        rng.Worksheet.Range(rng.Cells(2, 1), rng.Cells(1025, 24)).ClearContents
        rng.Worksheet.Range(rng.Cells(2, 1), rng.Cells(1025, 24)).Hyperlinks.Delete
       
        Set rng = rng.Cells(1, 1)
       
        With Application.FileSearch
       
            .NewSearch
            .LookIn = sFolder
            .Filename = sFilter
            .SearchSubFolders = ThisWorkbook.Names!SearchSubFolders.RefersToRange.Value
            .MatchTextExactly = True
            .FileType = msoFileTypeAllFiles
           
            Application.StatusBar = “Searching for files… Please wait”
     
            If .Execute() > 0 Then
     
                ‘Store old Calculation and Events state
               xCalc = Application.Calculation
                Application.Calculation = xlCalculationManual
               
                blnEvents = Application.EnableEvents
                Application.EnableEvents = False
               
                ‘Application.Interactive = false
               ‘Application.screenupdating=false
               
                On Error GoTo ErrWkb
               
                iCount = .FoundFiles.Count
                For i = 1 To iCount
               
                    Application.StatusBar = “Searching file “ & i & ” of “ & iCount & “… “
                   
                    strFile = “”
                    strFile = .FoundFiles(i)
                   
                    Set rng = rng.Offset(1, 0)
                    rng.Offset(0, 0).Value = “(Not opened)”
                    rng.Offset(0, 1).Value = strFile
                    Set rngHlink = rng.Offset(0, 1)
                   
                    ‘Test it isn’t this workbook
                   If ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name = strFile Then
                       
                   Else
                   
                        bWasOpen = False
                        If WorkbookIsOpen(strFile) Then
                            bWasOpen = True
                        Else
                            Set wkb = Workbooks.Open(strFile, 0, True, , , , True, , , , False, , , , xlNormalLoad)
                            wkb.Windows(1).WindowState = xlMinimized
                        End If
                       
                        If WorkbookIsOpen(strFile) Then
                       
                            rng.Offset(0, 0).Value = wkb.Name
                            rng.Offset(0, 2).Value = “Project: “ & wkb.VBProject.Name
                           
                            If wkb.VBProject.Protection = vbext_pp_locked Then
                                rng.Offset(0, 4).Value = “Cannot read a password-protected VBA project”
                            Else
                           
                               rng.Offset(0, 4).Value = “File “ & i & ” of “ & iCount & “: “ & wkb.VBProject.VBComponents.Count & ” VBA components”
                               
                                ‘Inspect each VB component for Target string
                              For Each vbc In wkb.VBProject.VBComponents
                               
                                   If InStr(vbc.Name, sSearch) Then
                                   
                                       ‘Target string was found in the name
                                     
                                        If rngHlink.Hyperlinks.Count < 1 Then
                                            rngHlink.Hyperlinks.Add rngHlink, strFile, , “Click here to open the file”
                                        End If
                                       
                                        Set rng = rng.Offset(1, 0)
                                       
                                        Select Case vbc.Type
                                        Case vbext_ct_ActiveXDesigner
                                           sType = “ActiveX Designer:”
                                        Case vbext_ct_ClassModule
                                           sType = “Class Module: “
                                        Case vbext_ct_Document
                                           sType = “Document: “
                                        Case vbext_ct_MSForm
                                           sType = “MS Form: “
                                        Case vbext_ct_StdModule
                                           sType = “Standard Module: “
                                        Case Else
                                           sType = “Unknown object type: “
                                        End Select
                                       
                                        rng.Offset(0, 2).Value = sType & vbc.Name
                                        rng.Offset(0, 3).Value = 0
                                        rng.Offset(0, 4).Value = “(Matching component name)”
                                       
                                   End If
                                   
                                   lngStartLine = 1
                                   lngStartCol = 1
                                   
                                   Application.StatusBar = “Searching file “ & i & ” of “ & iCount & “… “ & vbc.Name
                                   
                                   Do Until Not vbc.CodeModule.Find(sSearch, lngStartLine, lngStartCol, -1, -1)
                                   
                                       ‘Target string was found in the code
                                     
                                        If rngHlink.Hyperlinks.Count < 1 Then
                                            rngHlink.Hyperlinks.Add rngHlink, strFile, , “Click here to open the file”
                                        End If
                                       
                                       Set rng = rng.Offset(1, 0)
                                       
                                        Select Case vbc.Type
                                        Case vbext_ct_ActiveXDesigner
                                           sType = “ActiveX Designer:”
                                        Case vbext_ct_ClassModule
                                           sType = “Class Module: “
                                        Case vbext_ct_Document
                                           sType = “Document: “
                                        Case vbext_ct_MSForm
                                           sType = “MS Form: “
                                        Case vbext_ct_StdModule
                                           sType = “Standard Module: “
                                        Case Else
                                           sType = “Unknown object type: “
                                        End Select

  9. I’m with you Rob. Assert uses too many brain cycles. So I put in a If something Then Stop.

    I realize that it hasn’t changed. The existing code hasn’t broken yet because it’s never been passed a “one row” array. Regardless, it seems like an odd design choice to make.

  10. VBA is stored as plain text within XLS workbooks, so just searching .xls files in all directories for text .Transpose( using Windows Explorer should be sufficient to find all XLS workbooks with VBA Transpose calls.

    XLS[XMB] present interesting problems. I haven’t checked, but I suspect XLSB workbooks would be the same as XLS ones. For XLSX and XLSM, you’d need to treat them as zip files and search the files within them. Not so easy with Windows’s own search tools, but there are 3rd party disk file search tools which access files inside zip files and other archive formats.

  11. Execellent, I am a VBA lover form China, and I learn a lot from your website. thank you!

  12. Wouldn’t this suffice ?

    Sub files_001()
     Shell Environ(“comspec”) & ” /c Dir E:*.bas  /s /b  > E:files.txt”
     
     Do
      DoEvents
     Loop Until FileLen(“E:files.txt”) > 0
       
     Workbooks.Open “E:files.txt”
    End Sub
  13. @hans,

    My recollection from days gone by was that it is not a good idea to run a tight loop of DoEvents. Here is a more system friendly method to introduce the delay you are looking to implement…

    Private Declare Function OpenProcess _
    Lib “kernel32? _
    (ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long

    Private Declare Function CloseHandle _
    Lib “kernel32? _
    (ByVal hObject As Long) As Long

    Private Declare Function WaitForSingleObject _
    Lib “kernel32? _
    (ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) As Long

    Sub files_001()
    Dim PID As Long, hProcess
    PID = Shell(Environ(“comspec”) & ” /c Dir E:*.bas /s /b > E:files.txt”)
    If PID = 0 Then

    ‘ Shell Didn’t Work — Handle the error here

    Else
    hProcess = OpenProcess(&H100000, True, PID)
    WaitForSingleObject hProcess, -1
    CloseHandle hProcess
    End If
    Workbooks.Open “E:files.txt”
    End Sub

  14. @Rick

    Thank you for sharing, but I never came across any problems using DoEvents; if you can explain more what problems could arise you may be able to convince me. Now the only thing i’ve got is ‘the colour of your eyes’ to believe that what you are asserting is correct.

  15. I know that this is an old post but I tied Tushar’s Transpose Transpose idea and it seems to break if the array has an element with a string with more than 255 characters. This happens in both 2003 & 2010. Any ideas?

    Dick your original Local view shows aTest as Long(0 to 3)(0 to 0). I would think that it is setting a zero element dimension which caused the problem.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.