[mso] FW: Re: add in vba problems (this time without the =20)

Hi Jim,

Thanks for the tips.

The fixYear sub is designed to manipulate dates in fact much longer and has a 
long list of elseif statements. I did not use the case statement, because some 
if conditions evaluate more than one expression (not sure how you would do this 
with select). Although as you rightly guessed, it is some of the first code I 
ever wrote.

When you say declare all variables in module, do you mean in the declarations 
section before I even write any subs/functions?

If it helps this is the sub used to create the menu bar in my add in. I can't 
see anything wrong with how the fixYear sub is called here either(see below)



Sub AddMenu()

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer

''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Location for menu data
    Set MenuSheet = ThisWorkbook.Sheets(1)

'   Make sure the menus aren't duplicated
    Call DeleteMenu
   

'create main toolbar option
Set MenuObject = Application.CommandBars(1). _
                 Controls.Add(Type:=msoControlPopup, _
                 Before:=10, _
                 Temporary:=True)
                 MenuObject.Caption = "&RMS Agg Tools"
                
'create first button
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = "deleteEmptyRows" 'call macro to be used
                    MenuItem.Caption = "Delete Empty Rows"
                   
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
               MenuItem.Caption = "Fix"
              
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Fix Year"
                SubMenuItem.OnAction = "fixYear"
               
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Fix Zip"
                SubMenuItem.OnAction = "fixZIP"
               
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
               MenuItem.Caption = "Remove"
               
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Remove Wildcards"
                SubMenuItem.OnAction = "removeWildcards"
               
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Remove Returns"
                SubMenuItem.OnAction = "removeReturns"
               
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Remove SQuote"
                SubMenuItem.OnAction = "SQUOTE_remove"
               
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
               MenuItem.Caption = "Clean"
                              
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Trim All"
                SubMenuItem.OnAction = "trimALL"
               
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Clean Values"
                SubMenuItem.OnAction = "ValuesCleanUp"

               
 Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
               MenuItem.Caption = "Replace"
              
 Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = "Replace Street Name to Number Order"
                SubMenuItem.OnAction = "StreetNameToNumber"
           
                          
End Sub



Sub DeleteMenu()
'   This sub should be executed when the workbook is closed
'   Deletes the Menus
    Dim MenuSheet As Worksheet
    Dim Row As Integer
    Dim Caption As String
   
    On Error Resume Next
    Set MenuSheet = ThisWorkbook.Sheets(1)
            Application.CommandBars(1).Controls("&RMS Agg Tools").Delete
     
    On Error GoTo 0
End Sub




Thanks for the tips


Clint

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [ mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Jim Pettit
Sent: 27 July 2005 15:40
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: add in vba problems


Clint--

I ran both subs and had no problems (although it seems the fixZip sub should
also have the capacity to fix even two digit numbers). I'm not completely
sure what fixYear is supposed to do other than convert cells with years
stored in any format to a NumberFormat of "@", while blanking out
non-numeric cells; is that about it? Anyway, if you set a breakpoint then
step through the code, which line is throwing the error?

Two uninvited notes on good VBA programming practice:

1) Get in the habit of setting 'Option Explicit' at the top of every module,
then explicit declaring all variables in that module (ie, 'Dim item'. It
makes for a bit more programming, but you'll catch a ton of errors that way,
and the system won't need to guess your intentions.
2) Get rid of all those 'ElseIf' statements and go with the much easier to
read and faster-executing 'Select Case' statement.

--Jim

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [ mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Clint Graham
Sent: Wednesday, July 27, 2005 4:59 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] add in vba problems

Hi all,

Over time I have compiled a list of macros to speed up my work. Now I want
to export these macros to all my team so I decided to create an add in with
menu bar. The problem is that one of my macros no longer works (see below).
The error message tells me that the argument is not optional, even though
the macro works if run direct from the macro list.

Public Sub fixYear()

'converts year into type RMS likes ie 01/01/yyyy or will blank non numeric
entries or if 'blank leave blank

Dim findYear        'declare variable

   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

selection.NumberFormat = "@"

For Each item In selection

findYear = item    'match variable to cell

'ignore all empty rows in selection

If IsEmpty(findYear) Then
IsEmpty (item.Offset(0, 0))

'if cell does not contain any numerical values then set cell value to ""

ElseIf Not findYear Like "*#*" Then
item.Offset(0, 0) = ""

End If

Next item

 Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True

End Sub

However, I have built another macro which uses identical construction and it
works without any problems(see below).

Public Sub fixZip()

Dim zip

Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

selection.NumberFormat = "@"

For Each item In selection

zip = item

If zip Like "####" Then
item.Offset(0, 0) = "0" & zip

ElseIf zip Like "###" Then
item.Offset(0, 0) = "00" & zip

ElseIf zip Like "#########" Then
item.Offset(0, 0) = Left(zip, 5)

ElseIf zip Like "#####-####" Then
item.Offset(0, 0) = Left(zip, 5)

ElseIf zip Like "####-####" Then
item.Offset(0, 0) = "0" & Left(zip, 4)

ElseIf zip Like "#####*" Then
item.Offset(0, 0) = Left(zip, 5)

End If
Next
 Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Can anyone offer a solution, this is driving me mad.

Thanks in advance

Clint



This message is intended only for the use of the person(s) ("the intended
recipient(s)") to whom it is addressed.  It may contain information that is
privileged and confidential within the meaning of applicable law.  If you
are not the intended recipient, please contact the sender as soon as
possible.  The views expressed in this communication may not necessarily be
the views held by Liberty Syndicate Management Limited.

This message may contain material non-public information.  As such it should
not be used for the purchase or sale of any securities issued by either the
company or the Liberty Mutual Group, or their respective subsidiaries or
affiliates.  Such prohibited use may subject one to both criminal and civil
penalties under governing laws.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************



This message is intended only for the use of the person(s) ("the intended 
recipient(s)") to whom it is addressed.  It may contain information that is 
privileged and confidential within the meaning of applicable law.  If you are 
not the intended recipient, please contact the sender as soon as possible.  The 
views expressed in this communication may not necessarily be the views held by 
Liberty Syndicate Management Limited.

This message may contain material non-public information.  As such it should 
not be used for the purchase or sale of any securities issued by either the 
company or the Liberty Mutual Group, or their respective subsidiaries or 
affiliates.  Such prohibited use may subject one to both criminal and civil 
penalties under governing laws.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: