youmayhaveaSubprocedurethatbinesvariouspartsofaworkbookintoaconcisereport

 

FunctionproceduresAfunctionisasetofinstructionsthatreturnsasinglevalueoran

arrayYoucanuseFunctionproceduresinwiorksehepetformulas

 

DeclarationsAdeclarationisinformationaboutavariablethatyouprovidetoVBAFor

exampleyoucandeclarethedatatypeforvariablesthatyouplantouseDeclarationsgoat

thetopofthemodule

 

AsingleVBAmodulecanstoreanynumberofproceduresanddeclarations

 

NoteThisbookfocusesexclusivelyonFunctionprocedureswhicharetheonlytypeof

procedurethatyoucanuseinwiorksehepetformulas

 

 

EnteringVBACode

ThissectiondescribesthevariouswaysofenteringVBAcodeinacodewindowForFunction

proceduresthecodewindowwillalwaysbeaVBAmoduleYoucanaddcodetoaVBAmodulein

threeways

 

Useyourkeyboardtotypeit

 

UsetheExcelmacrorecorderfeaturetorecordyouractionsandconvertthemintoVBAcode

 

Copythecodefromanothermoduleandpasteitintothemodulethatyouareworkingon

 

ENTERINGCODEMANUALLY

SometimesthemostdirectrouteisthebestoneTypethecodebyusingyourkeyboard

EnteringandeditingtextinaVBAmoduleworksjustasyouexpectYoucanselecttextand

copyitorcutandpasteittoanotherlocation

 

UsetheTabkeytoindentthelinesthatlogicallybelongtogetherforexamplethe

conditionalstatementsbetweenanIfandanEndIfstatementIndentationisnotnecessary

butitmakesthecodeeasiertoread

 

AsingleinstructioninVBAcanbeaslongasyouwantForthesakeofreadabilityhowever

youmaywanttobreakalengthyinstructionintotwoormorelinesTodosoendtheline

withaspacefollowedbyanunderscorecharacterandthenpressEnterandcontinuethe

instructiononthefollowinglineThefollowingcodeforexampleisasinglestatement

splitoverthreelines

 

IfIsNumericMyCellThen

Result="Number"Else

Result="NonNumber"

 

NoticethatIindentedthelasttwolinesofthisstatementDoingthisisoptionalbutit

helpstoclarifythefactthatthesethreelinesmakeupasinglestatement

 

AfteryouenteraninstructiontheVBEditorperformsthefollowingactionstoimprove

readability

 

ItinsertsspacesbetweenoperatorsIfyouenterAns=12withoutanyspacesforexample

VBAconvertsitto

 

Ans=12

 

TheVBEditoradjuststhecaseofthelettersforkeywordspropertiesandmethodsIfyou

enterthefollowingtext

 

user=applice

 

VBAconvertsitto

 

user=ApplicationUserName

 

BecausevariablenamesarenotcasesensitivetheVBEditoradjuststhenamesofall

variableswiththesameletterssothattheircasematchesthecaseoflettersthatyoumost

recentlytypedForexampleifyoufirstspecifyavariableasmyvaluealllowercaseand