Can someone help me speed up this code???
Posted by Duane Kennerson on December 15, 2000 6:37 PM
Here is the code. I know its long but can anyone help me
speed it up? It creates a new toolbar in excel at start up.
Sub NewToolBar()
Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
'This will create a custom command bar for the program
On Error Resume Next
Application.CommandBars("Daily Sheet Toolbar").Delete
Set cbrCommandBar = _
Application.CommandBars.Add
cbrCommandBar.Name = "Daily Sheet Toolbar"
cbrCommandBar.Position = msoBarTop
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
'Set properties of command buttons
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 71
.TooltipText = _
"Week 1"
.OnAction = "One"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 72
.TooltipText = _
"Week 2"
.OnAction = "Two"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 73
.TooltipText = _
"Week 3"
.OnAction = "Three"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 74
.TooltipText = _
"Week 4"
.OnAction = "Four"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 75
.TooltipText = _
"Week 5"
.OnAction = "Five"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 76
.TooltipText = _
"Week 6"
.OnAction = "Six"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 77
.TooltipText = _
"Week 7"
.OnAction = "Seven"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 78
.TooltipText = _
"Week 8"
.OnAction = "Eight"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 578
.TooltipText = _
"Sort Worksheets"
.OnAction = "AlphaSort"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 928
.TooltipText = _
"Sort Names on Sheet"
.OnAction = "sort"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 283
.TooltipText = _
"Calculator"
.OnAction = "Calculator"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 3
.TooltipText = _
"Save"
.OnAction = "savebook"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 4
.TooltipText = _
"Print"
.OnAction = "printsheet"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 276
.TooltipText = _
"Feedback Form"
.OnAction = "Feedback"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 353
.TooltipText = _
"Training Log Update"
.OnAction = "TLog"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 1671
.TooltipText = _
"Delete Trainee"
.OnAction = "delete_click"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 236
.TooltipText = _
"Settings"
.OnAction = "Settings"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 183
.TooltipText = _
"Search for Employee"
.OnAction = "opensearch"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 133
.TooltipText = _
"Next Book"
.OnAction = "nextbook"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 132
.TooltipText = _
"Previous Book"
.OnAction = "previousbook"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 3004
.TooltipText = _
"About"
.OnAction = "About_Click"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 200
.TooltipText = _
"30 Minute Stamina"
.OnAction = "Thirty"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 200
.TooltipText = _
"60 Minute Stamina"
.OnAction = "Sixty"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 200
.TooltipText = _
"120 Minute Stamina"
.OnAction = "OneTwenty"
.Tag = ""
End With
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = ""
.FaceId = 200
.TooltipText = _
"240 Minute Stamina"
.OnAction = "TwoForty"
.Tag = ""
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
End With
cbrCommandBar.Visible = True
End Sub
Posted by greg on December 18, 2000 10:39 PM
End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With cbrCommandBar.Visible = True
Start deleting lines that look like they do nothing. I am guessing you recorded most of the code. I have done things through record macro and you get a bunch of stuff that really doesn't do anything. Try and find the lines that look like they are doing something unnecesary and delete them. Probably not much help, but I hope it helps some.
Posted by Duane Kennerson on December 19, 2000 2:55 PM
End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With End With cbrCommandBar.Visible = True
I have taken out all of the captions and tags since I posted this which helped a little.
I'm wondering if there is a way to loop the with statements so I don't have to call all
of those procedures everytime I create a new toolbar button.
Thanks for your suggestion though.
Duane
Posted by Tim Francis-Wright on December 21, 2000 10:34 AM
(snip)
It looks like you only need the first
with cbrCommandBar.Controls loop;
the others are extraneous.
Hope that helps.
