Return Command bar info in a VBA function
February 3, 2010 • JP • 2 Comments • Rate This Article
• Links to this article
In the previous post I showed a simple sub for listing most of the CommandBar properties. But we can also return this information using a function.
You might do this (in Excel versions prior to 2007) when working with a command bar and need to view its properties before making a change. Here is a function that returns a string array of properties for each Command Bar Object.
Dim cBar As Office.CommandBar
Dim cBarCount As Long
Dim tempArray() As String
Dim i As Long
' the number of properties we're going to extract
Const NUMBER_OF_PROPERTIES As Long = 8
' number of command bars is needed for array and look
cBarCount = CommandBars.Count
' resize array accordingly
ReDim tempArray(1 To cBarCount, 1 To NUMBER_OF_PROPERTIES)
For i = 1 To cBarCount
Set cBar = CommandBars(i)
tempArray(i, 1) = cBar.Name
tempArray(i, 2) = cBar.NameLocal
tempArray(i, 3) = cBar.Visible
tempArray(i, 4) = cBar.Index
tempArray(i, 5) = cBar.BuiltIn
tempArray(i, 6) = cBar.Controls.Count
tempArray(i, 7) = cBar.Enabled
tempArray(i, 8) = cBar.Type
Next i
GetCommandBarInfo = tempArray
End Function
Sample usage:
In order to use the above function, the bare minimum code is:
Dim commandBarInfo() As String
commandBarInfo = GetCommandBarInfo
End Sub
How do we extract the information from the resulting commandBarInfo array? Obviously a loop is required. But we need to determine the upper bounds of the (two-dimensional array) array. Here's how we do it, under the assumption that the array is two-dimensional.
Dim commandBarInfo() As String
Dim numRows As Long
Dim numCols As Long
Dim i As Long, j As Long
commandBarInfo = GetCommandBarInfo
'loop through commandBarInfo array
' first, determine number of rows
numRows = UBound(commandBarInfo)
' next, get number of columns
numCols = NumberOfColumns(commandBarInfo)
For i = 1 To numRows
For j = 1 To numCols
Debug.Print commandBarInfo(i, j)
Next j
Next i
End Sub
The number of rows (the first dimension) is easy. The UBound function will tell us that. But how do we determine the number of columns (the second dimension)? Unfortunately, we'll need to use an error-causing loop (ECL). The NumberOfColumns function (adapted from a MSKB article) does that for us. It simply loops through the maximum number of available dimensions and stops when an error occurs. The last non-error loop determines the largest dimension.
' adapted from http://support.microsoft.com/kb/152288
Dim dimnum As Long
Dim errorcheck As Variant
'Sets up the error handler.
On Error GoTo FinalDimension
'Visual Basic for Applications arrays can have up to 60000
'dimensions; this allows for that.
For dimnum = 1 To 60000
'It is necessary to do something with the LBound to force it
'to generate an error.
errorcheck = arr(1, dimnum)
Next dimnum
Exit Function
' The error routine.
FinalDimension:
NumberOfColumns = (dimnum - 1)
End Function
Extract Nth Command Bar info
Now let's say we want to extract information on the 10th command bar (which we know about because we already ran ListCommandBars to find out which one it is). We now know that there are eight columns in the array (thanks to the NumberOfColumns function). So to get the 10th command bar out of the array, our loop must start at index 73 ((8 * 9) + 1) and loop through the 8 columns in that index.
Debug.Print commandBarInfo(73, j)
Next j
In other words, to get the name, visibility, index, and so on, for the nth CommandBar, multiply the number of columns by n – 1, add 1, and loop once for each column. In fact, we can functionize this:
numCols As Long) As String()
Dim tempCmdBar() As String
Dim j As Long
Dim ndx As Long
' size the array accordingly
ReDim tempCmdBar(1 To 1, 1 To numCols)
' figure out the starting index
ndx = (numCols * (CommandBarNumber - 1)) + 1
' loop through each column for the given index
For j = 1 To numCols
tempCmdBar(1, j) = arr(ndx, j)
Next j
Return_Nth_CommandBar = tempCmdBar
End Function
We can then modify the ExtractCommandBarInfo procedure as follows:
Dim commandBarInfo() As String
Dim numRows As Long
Dim numCols As Long
Dim i As Long, j As Long
Dim Nth_CommandbarInfo() As String
commandBarInfo = GetCommandBarInfo
'loop through commandBarInfo array
' first, determine number of rows
numRows = UBound(commandBarInfo)
' next, get number of columns
numCols = NumberOfColumns(commandBarInfo)
For i = 1 To numRows
For j = 1 To numCols
Debug.Print commandBarInfo(i, j)
Next j
Next i
Nth_CommandbarInfo = Return_Nth_CommandBar(commandBarInfo, 10, numCols)
For i = 1 To 1
For j = 1 To numCols
Debug.Print Nth_CommandbarInfo(i, j)
Next j
Next i
End Sub
Notice that the Return_Nth_CommandBar takes the number of columns as a third parameter, which we calculated previously to loop through the entire array. We could also refine Return_Nth_CommandBar to calculate the number of columns on its own, using the NumberOfColumns procedure we reviewed earlier.
Dim tempCmdBar() As String
Dim j As Long
Dim ndx As Long
' get number of columns from NumberOfColumns procedure
' instead of as a parameter
numCols = NumberOfColumns(arr)
' size the array accordingly
ReDim tempCmdBar(1 To 1, 1 To numCols)
' figure out the starting index
ndx = (numCols * (CommandBarNumber - 1)) + 1
' loop through each column for the given index
For j = 1 To numCols
tempCmdBar(1, j) = arr(ndx, j)
Next j
Return_Nth_CommandBar = tempCmdBar
End Function
Note that this would mean that NumberOfColumns would be called twice in the ExtractCommandBarInfo procedure, so it's up to you to decide how to implement these functions. I would probably choose the second way.
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: List all Command Bar Properties in Excel
Next Post: ShipTrack 4.1.2 update available
Hi JP,
please enlighten me, why can't we just do
For j = LBound(commandBarInfo, 2) To UBound(commandBarInfo, 2)
Debug.Print commandBarInfo(i, j)
Next j
Next i
when looping through the commandBar info array?
Or to be a bit more correct:
Dim firstCol As Long
Dim numRows As Long
Dim numCols As Long
firstRow = LBound(commandBarInfo, 1)
firstCol = LBound(commandBarInfo, 2)
numRows = UBound(commandBarInfo, 1)
numCols = UBound(commandBarInfo, 2)
For i = firstRow To numRows
For j = firstCol To numCols
Debug.Print commandBarInfo(i, j)
Next j
Next i
Are there some kind of limitations to the LBound/UBound functions?
Or are you just doing this to be compatible with Excel 97 and earlier?
Good call! I couldn't figure it out, so I went with the brute force method.