[mso] Re: add in vba problems

Hi=20Jim,

Thanks=20for=20the=20tips.

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

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

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



Sub=20AddMenu()

Dim=20MenuSheet=20As=20Worksheet
Dim=20MenuObject=20As=20CommandBarPopup

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

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

'=20=20=20Make=20sure=20the=20menus=20aren't=20duplicated
=20=20=20=20Call=20DeleteMenu
=20=20=20=20

'create=20main=20toolbar=20option
Set=20MenuObject=20=3D=20Application.CommandBars(1).=20_
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Controls.Add(Type:=3Dms=
oControlPopup,=20_
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Before:=3D10,=20_
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Temporary:=3DTrue)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuObject.Caption=20=3D=
=20"&RMS=20Agg=20Tools"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
'create=20first=20button
Set=20MenuItem=20=3D=20MenuObject.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.OnAct=
ion=20=3D=20"deleteEmptyRows"=20'call=20macro=20to=20be=20used
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.Capti=
on=20=3D=20"Delete=20Empty=20Rows"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20MenuItem=20=3D=20MenuObject.Controls.Add(Type:=3DmsoControlPopup)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.Caption=20=3D=20"Fix=
"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Fix=20Year"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"fixYear"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Fix=20Zip"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"fixZIP"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20MenuItem=20=3D=20MenuObject.Controls.Add(Type:=3DmsoControlPopup)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.Caption=20=3D=20"Rem=
ove"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Remove=20Wildcards"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"removeWildcards"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Remove=20Returns"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"removeReturns"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Remove=20SQuote"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"SQUOTE_remove"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20MenuItem=20=3D=20MenuObject.Controls.Add(Type:=3DmsoControlPopup)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.Caption=20=3D=20"Cle=
an"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Trim=20All"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"trimALL"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButton)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Clean=20Values"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"ValuesCleanUp"

=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20Set=20MenuItem=20=3D=20MenuObject.Controls.Add(Type:=3DmsoControlPopup)=

=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20MenuItem.Caption=20=3D=20"Rep=
lace"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20Set=20SubMenuItem=20=3D=20MenuItem.Controls.Add(Type:=3DmsoControlButto=
n)
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.Caption=20=3D=20=
"Replace=20Street=20Name=20to=20Number=20Order"
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20SubMenuItem.OnAction=20=3D=
=20"StreetNameToNumber"
=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
End=20Sub



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




Thanks=20for=20the=20tips


Clint

-----Original=20Message-----
From:=20mso-bounce@xxxxxxxxxxxxx=20[mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf=20Of=20Jim=20Pettit
Sent:=2027=20July=202005=2015:40
To:=20mso@xxxxxxxxxxxxx
Subject:=20[mso]=20Re:=20add=20in=20vba=20problems


Clint--

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


Two=20uninvited=20notes=20on=20good=20VBA=20programming=20practice:

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

--Jim=20

-----Original=20Message-----
From:=20mso-bounce@xxxxxxxxxxxxx=20[mailto:mso-bounce@xxxxxxxxxxxxx]=20On=20=
Behalf
Of=20Clint=20Graham
Sent:=20Wednesday,=20July=2027,=202005=204:59=20AM
To:=20mso@xxxxxxxxxxxxx
Subject:=20[mso]=20add=20in=20vba=20problems

Hi=20all,=20
=20
Over=20time=20I=20have=20compiled=20a=20list=20of=20macros=20to=20speed=20=
up=20my=20work.=20Now=20I=20want
to=20export=20these=20macros=20to=20all=20my=20team=20so=20I=20decided=20t=
o=20create=20an=20add=20in=20with
menu=20bar.=20The=20problem=20is=20that=20one=20of=20my=20macros=20no=20lo=
nger=20works=20(see=20below).
The=20error=20message=20tells=20me=20that=20the=20argument=20is=20not=20op=
tional,=20even=20though
the=20macro=20works=20if=20run=20direct=20from=20the=20macro=20list.
=20
Public=20Sub=20fixYear()
=20
'converts=20year=20into=20type=20RMS=20likes=20ie=2001/01/yyyy=20or=20will=
=20blank=20non=20numeric
entries=20or=20if=20'blank=20leave=20blank
=20
Dim=20findYear=20=20=20=20=20=20=20=20'declare=20variable
=20
=20=20=20Application.ScreenUpdating=20=3D=20False
=20=20=20Application.Calculation=20=3D=20xlCalculationManual
=20
selection.NumberFormat=20=3D=20"@"
=20
For=20Each=20item=20In=20selection
=20
findYear=20=3D=20item=20=20=20=20'match=20variable=20to=20cell
=20
'ignore=20all=20empty=20rows=20in=20selection
=20
If=20IsEmpty(findYear)=20Then
IsEmpty=20(item.Offset(0,=200))
=20
'if=20cell=20does=20not=20contain=20any=20numerical=20values=20then=20set=20=
cell=20value=20to=20""
=20
ElseIf=20Not=20findYear=20Like=20"*#*"=20Then
item.Offset(0,=200)=20=3D=20""
=20
End=20If
=20
Next=20item
=20
=20Application.Calculation=20=3D=20xlCalculationAutomatic
=20=20=20Application.ScreenUpdating=20=3D=20True
=20
End=20Sub
=20
However,=20I=20have=20built=20another=20macro=20which=20uses=20identical=20=
construction=20and=20it
works=20without=20any=20problems(see=20below).
=20
Public=20Sub=20fixZip()
=20
Dim=20zip
=20
Application.ScreenUpdating=20=3D=20False
=20=20=20Application.Calculation=20=3D=20xlCalculationManual
=20
selection.NumberFormat=20=3D=20"@"
=20
For=20Each=20item=20In=20selection
=20
zip=20=3D=20item
=20
If=20zip=20Like=20"####"=20Then
item.Offset(0,=200)=20=3D=20"0"=20&=20zip
=20
ElseIf=20zip=20Like=20"###"=20Then
item.Offset(0,=200)=20=3D=20"00"=20&=20zip
=20
ElseIf=20zip=20Like=20"#########"=20Then
item.Offset(0,=200)=20=3D=20Left(zip,=205)
=20
ElseIf=20zip=20Like=20"#####-####"=20Then
item.Offset(0,=200)=20=3D=20Left(zip,=205)
=20
ElseIf=20zip=20Like=20"####-####"=20Then
item.Offset(0,=200)=20=3D=20"0"=20&=20Left(zip,=204)
=20
ElseIf=20zip=20Like=20"#####*"=20Then
item.Offset(0,=200)=20=3D=20Left(zip,=205)
=20
End=20If
Next
=20Application.Calculation=20=3D=20xlCalculationAutomatic
=20=20=20=20Application.ScreenUpdating=20=3D=20True
End=20Sub
=20
Can=20anyone=20offer=20a=20solution,=20this=20is=20driving=20me=20mad.
=20
Thanks=20in=20advance
=20
Clint
=20
=20

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

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

To=20send=20mail=20to=20the=20group,=20simply=20address=20it=20to=20mso@fr=
eelists.org

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

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

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

*************************************************************
You=20are=20receiving=20this=20mail=20because=20you=20subscribed=20to=20ms=
o@xxxxxxxxxxxxx=20or=20MicrosoftOffice@yahoogroups.com.

To=20send=20mail=20to=20the=20group,=20simply=20address=20it=20to=20mso@fr=
eelists.org

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

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

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

This=20message=20is=20intended=20only=20for=20the=20use=20of=20the=20perso=
n(s)=20("the=20intended=20recipient(s)")=20to=20whom=20it=20is=20addressed=
.=20=20It=20may=20contain=20information=20that=20is=20privileged=20and=20c=
onfidential=20within=20the=20meaning=20of=20applicable=20law.=20=20If=20yo=
u=20are=20not=20the=20intended=20recipient,=20please=20contact=20the=20sen=
der=20as=20soon=20as=20possible.=20=20The=20views=20expressed=20in=20this=20=
communication=20may=20not=20necessarily=20be=20the=20views=20held=20by=20L=
iberty=20Syndicate=20Management=20Limited.

This=20message=20may=20contain=20material=20non-public=20information.=20=20=
As=20such=20it=20should=20not=20be=20used=20for=20the=20purchase=20or=20sa=
le=20of=20any=20securities=20issued=20by=20either=20the=20company=20or=20t=
he=20Liberty=20Mutual=20Group,=20or=20their=20respective=20subsidiaries=20=
or=20affiliates.=20=20Such=20prohibited=20use=20may=20subject=20one=20to=20=
both=20criminal=20and=20civil=20penalties=20under=20governing=20laws.
*************************************************************
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: