[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
*************************************************************

Other related posts: