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.
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?
Dick,
Why not use an array of arrays rather than a 2-D array? Then you can ReDim all you want in the “row” dimension. I wrote a Stackoverflow answer along these lines:
http://stackoverflow.com/questions/3992717/how-can-i-redim-preserve-a-2d-array-in-excel-2007-vba-so-that-i-can-add-rows-n/3993119#3993119
>>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
>>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”.
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.
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.
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
So who’s got code that searches all the VBA on my hardrive?
I posted a search VBA article some years ago here:
Searching Files in Subfolders for VBA code string
@Rob, you might also want to check out Debug.Assert
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.
“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
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
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.
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
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.
Nigel: Pity Excel 2007/2010 no longer have the FileSearch object, rendering your code useless for those versions…
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.
Execellent, I am a VBA lover form China, and I learn a lot from your website. thank you!
Wouldn’t this suffice ?
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
@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
@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.
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.