Cost-effective toolbar?

[Yarroll]

Hi,

What would be the best way to set up an Excel personal toolbar so that it
doesn't eat lots of memory?
My Excel eats a lot of memory. I noticed this problem occurs especially when
running macros in workbooks with customized toolbars, with frequent "out of
memory" messages when trying to open VBE. No idea if this is caused by the
toolbar though. I use the following macro to create a toolbar on open and
close on exit:

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarButton
Set oCB = Application.CommandBars.Add(Name:="My Macros",
temporary:=True)
With oCB
Set oCtl = .Controls.Add
With oCtl
.Style = msoButtonIcon
.FaceId = 59
.OnAction = "MyStuff1"
.TooltipText = "MyStuff1"
End With
Set oCtl = Nothing

' (and it goes on for some more controls, no more than 15; FaceIds are
standard Excel icons)

Would there be a simpler way to build a toolbar? Or is the above likely to
run into memory problems?

 

[Dave Peterson]

I don't think it's your toolbar that's causing the memory problems. (I don't
have a guess, either.)

But if I had 15 macros to assign to a toolbar, I think I'd make a couple of
arrays and cycle through them.

Option Explicit
Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarButton
Dim myFaces As Variant
Dim myMacs As Variant
Dim myToolTips As Variant
Dim iCtr As Long

On Error Resume Next
Application.CommandBars("my Macros").Delete
On Error GoTo 0

myFaces = Array(71, 72, 33, 24, 35, 56, 67, 78, 89, 90, 11, 22, 53, 74, 25)

myMacs = Array("myMac1", _
"myMac2", _
"myMac3", _
"myMac4", _
"myMac5", _
"myMac6", _
"myMac7", _
"myMac8", _
"myMac9", _
"myMac10", _
"myMac11", _
"myMac12", _
"myMac13", _
"myMac14", _
"myMac15")

myToolTips = Array("mytooltip1", _
"mytooltip2", _
"mytooltip3", _
"mytooltip4", _
"mytooltip5", _
"mytooltip6", _
"mytooltip7", _
"mytooltip8", _
"mytooltip9", _
"mytooltip10", _
"mytooltip11", _
"mytooltip12", _
"mytooltip13", _
"mytooltip14", _
"mytooltip15")


If UBound(myToolTips) <> UBound(myMacs) _
Or UBound(myFaces) <> UBound(myMacs) Then
MsgBox "design error! This shouldn't happen!"
End If

Set oCB = Application.CommandBars.Add(Name:="My Macros", temporary:=True)

With oCB
.Visible = True
For iCtr = LBound(myMacs) To UBound(myMacs)
Set oCtl = .Controls.Add
With oCtl
.Style = msoButtonIcon
.FaceId = myFaces(iCtr)
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.TooltipText = myToolTips(iCtr)
End With
Next iCtr
End With

Set oCB = Nothing
Set oCtl = Nothing

End Sub

I think it makes it easier to see what's happening and easier to update.

And I'd take a look at Charles Williams' site for more info about the memory
stuff:
http://www.decisionmodels.com