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

Other related posts: