下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 VBA

VBA.pdf

VBA

echoldy
2013-12-13 0人阅读 举报 0 0 暂无简介

简介:本文档为《VBApdf》,可适用于IT/计算机领域

SupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgVBAUserDefinedFunctionsandSubroutinesTherearemanybuiltinfunctionsinExcelForexample,thefunctionnormsdist(value)givesN(value),whilethefunctionaverage(range)givesthemeanoraverageofasetofnumbers(asspecifiedinrange)Somefunctionsacceptnoinput(egpi()returnsthevalueofπ),whilesomefunctionsacceptmultipleinputs(egcorrel(columnvector,columnvector))WhilethelibraryinExcelisnotsmall,itdoesnothaveafinancialmodelinglibraryoffunctionsYoucannotfindafunctiontocomputeoptionpricesundertheBlackScholesframeworkMoreover,sometimesyoumaywanttowritefunctionsforveryspecificpurposesSoitisimportanttoknowhowtodevelopyourownfunctionlibraryInthisshortnoteweintroducehowtowriteuserdefinedfunctionsandsimplesubroutinesOpeningtheExcelVBADeveloperThefirsttaskfornovicesistolocatetheareawheretheprogramcodesareplacedForExcel(orXP),and,usethefollowing:Step:PressALTFtoopentheVisualBasicEditorStep:GototheToolbar,select“Insert”andclick“Module”CodesareplacedinModuleForExcel,youmayliketheVBEditortoshowupintheribbonBydefaultitdoesnotshowupTomakeitavailableasaniconintheribbon,usethefollowing:Step:GotoFile|Options|CustomizetheribbonStep:Ontheright,select“Maintabs”,check“Developer”DeveloperappearsinthetaskbarNowyoucanfindtheVBiconunderDeveloper:StructureofaUserDefinedFunctionThebasicstructureofaVBAuserdefinedfunction(UDF)isasfollows:Functionnameoffunction(inputarguments)(Variabledeclaration)(Mainbodyofprogram,shouldinvolvesomethinglikenameoffunction=foroutput)EndFunctionSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgVariablesandDataTypesInprogramming,variablesareusedtostoreandmanipulatedataConventionally,datacomesindifferentformsor“types”andthisinfluencestheamountofmemoryallocatedforstorageandwhatoperationscanbedoneonthemInmostnumericalcalculation,themaindistinctionsarebetweennumbers(decimalnumbers)andintegersAnotherdatatypecalledVariant,whichcanrepresentanydatatype,isthedefaultdatatypeifavariable’stypeisnotdefinedexplicitlybytheuserConsiderthefollowingverysimpleprogramthatsumstwonumbers:Functionsumtwo(xAsDouble,yAsDouble)AsDoublesumtwo=xyEndFunctionIntheabove,thefunctionsumtwohastwoinputvariablesxandy,bothdefinedasadecimalnumber(asspecifiedbythedatatypeDouble)andreturnsadecimalnumberAfteryouhavetypedtheaboveintheVBeditor,presssave,andthenyoucanusethefunctionasifitisabuiltinfunctionForexample,youcanputincellA,incellB,andtypetheformula=sumtwo(A,B)inCThenCwouldgivetheresultVeryfrequentlyweneedtofurtherdeclarevariablestostoredataorintermediatevaluesintheprogramcalculationConsiderthecalculationofannyeardecreasingannuityimmediatewhichpaysndollarsattime,n−dollarsattime,n−dollarsattime,,dollarattimen,andnothingaftertimenThepresentvalueoftheannuityattime,underaninterestrateofr,isgivenby⎟⎟⎠⎞⎜⎜⎝⎛−−=−rrnrn)(PVWecanprogramtheaboveasfollows:Functiondecannuity(nAsInteger,rAsDouble)AsDoubleDimvAsDouble,PVAsDouble'v=(r)v=(r)PV=–v^ndecannuity=(n–PVr)rEndFunctionIntheabove,theinputargumentsaredeclared:nisaninteger,andrisadecimalnumberThefunctionreturnsadecimalnumberThestatement“DimvAsDouble”isusedtodeclareavariablecalledvwithdatatypeDouble(adecimalnumber)ThestructurefordeclaringavariableisDim(nameofvariabletobedeclared)As(variabletype)Thesymbol“'”before“v=(r)”iscalledaremarksymbol:anythingonalinethat’safteritisnotexecutedItisagoodpracticetogivedefinitionsofvariablesandlogicoftheprogramusingremarksforlaterreferenceSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgYoucanusethecolon“:”tosavethenumberoflines:Functiondecannuity(nAsInteger,rAsDouble)AsDoubleDimvAsDouble,PVAsDoublev=(r):PV=–v^n:decannuity=(n–PVr)rEndFunctionThecolonin“v=(r):PV=–v^n”wouldfunctionasalinebreakTheexcessiveuseofcolons,however,mayreducereadabilityHereisamoreelaboratedlistofdatatypes−Integer(from−to−=),egDimiAsInteger−Long(from−,,,to,,,),thistypeisveryusefulforsimulation−Double(=doubleprecision),egDimuAsDouble−String(=letters),egDimflagAsStringWhenusingStringvariables,noticethatwealwaysneedtoadd“”Egflag=“call”−Boolean(=trueorfalse),egDimCrossAsBooleanWewoulddiscusstheBooleandatatypeshortlyafterwediscusstheconditioningstatementsNoteverythingcanbeusedasthenameofavariableRulesforthenameofthevariableareasfollows:−thefirstpositionmustbealetter,−theotherpositionscanbeletters,numbers,orunderscore(),−cannotbethesameasotherdefinedfunctions,egDimabsAsIntegerisinvalidbecauseabsisamathematicalfunctionStandardMathematicalFunctionsAvailableintheVBEnvironmentManyusualmathematicaloperationsthatareavailableinapocketcalculatorcanbeusedinthemainbodyoftheprogramForexample,,*,performaddition,subtraction,multiplication,anddivisionPowerisperformedby^OthercommonmathematicalfunctionsinVBareSqr,Abs,Exp,Log,Sin,Cos,Tan,Atn,Sgn,Rnd–SqrcomputesthesquarerootofanumberYoucanuse^insteadofSqr,though–AbscomputestheabsolutevalueofanumberEgAbs(–)=–Expcomputestheexponentialofarealnumber–LogcomputesthenaturallogarithmofapositivenumberEgLog()=…–Sin,Cos,TanarestandardtrigonometricfunctionsTheinputsareassumedtobeinradian–Atncomputesthearctangentofanumber–Sgnreturns,,–accordingtothesignofanumberEgSgn()=,Sgn(–)=––RndreturnsarandomnumberinbetweenandIttakesnoinputargumentThefollowingaremathematicalfunctionsusedinformattingnumbersTheyaremoreusefulforsubroutinesorformattingresultsaftercomputingthepreciseresultinafunctionSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgFix,Int,Round,Format–FixreturnstheintegerportionofanumberEgFix()=,Fix(–)=––IntisthegreatestintegervaluefunctionEgInt()=,Int()=,Int(–)=––Round(n,m)roundanumberntomdecimalplacesEgRound(,)=–Format(number,form)returnsaformattednumberintermsofastringTheformatisspecifiedin“form”OnecanuseFormattocontrolthenumberofdecimalplacestoberoundedanddisplayedjustlikeRound(n,m)Ifonewantstoroundtodecimalplaces,onecanuseform=“”EgFormat(,“”)=,Format(,“”)=(whichisastring)However,Round(,)=Onecanalsospecifythethousandseparatorsontheleftofthedecimalplacebyusing“#,##”EgFormat(,“#,##”)=,,Someotherfrequentlyusedformsareasfollows:FormatExplanationExamplePercentExpressanumberinpercentageform,withtwodigitstotherightofthedecimalplaceFormat(,“Percent”)=ScientificScientificnotation,withtwodigitstotherightofthedecimalplaceFormat(,“Scientific”)=ECurrencyDisplaysthedollarsigh,theusualthousandseparatorsaswellastwodecimalplacesFormat(,“Currency”)=$,IfyouwanttousespecialbuiltinfunctionsthatareavailableinExcelbutnotdefinedinVB(suchasthenormalinversefunctionnormsinv),youneedtoadd“WorksheetFunction”or“Application”atthefrontThecode“WorksheetFunction”inz=WorksheetFunctionnormsinv(s)commandstheVBcompliertosearchforthedefinitionofthefunctionfromthefunctionlibraryinExcelworksheetItcomputesz=N−(s)FunctionsthatReturnMultipleOutputsSometimeswemaywantafunctiontoreturnmultipleoutputsForexample,wemaywanttohaveafunctionthatreturnsboththecircumferenceandtheareaofacircle,withtheradiusasinputInthiscase,thefunctionoutputdatatypeisspecifiedasVariant,andweneedtooutputtheresultwiththehelpofthecommandArrayFunctioncir(rAsDouble)AsVariantDimpAsDouble,circumAsDouble,areaAsDoublep=WorksheetFunctionPi()circum=*p*r:area=p*r^cir=Array(circum,area)EndFunctionSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgTheoutputofthisfunctionwouldspantwocellsalignedhorizontallyTousethefunctionwiththeradiusplacedinB,youneedtofirstselecttwocells(sayCandD),typeintheformula“=cir(B)”,andthenpressCtrlShirtEnterNotethatintheformulaarea,theformulashownis{=cir(B)}butnot=cir(B)Theparenthesis{}createdby“CtrlShirtEnter”showsthatthefunctiongivesmultipleoutputsWhatifyouwanttheoutputstobealignedverticallyYoucantakeatransposetotherowvectorbeforeitisassignedtocir:cir=WorksheetFunctionTranspose(Array(circum,area))StructureofaSubroutineFunctionsareusedtocalculatesomethingbasedonsomeinputsbyinsertingintocellsTheinputsaresenttothefunctionsbypassingthemintotheinputargumentsSubroutinesaremuchmorepowerfulSubroutinesaremeanttobeinvokedbyarunbuttonorexplicitlycalled(bypressing“Play”),butnotbyinsertingformulasintocellsTheycanbeusedtochangethecontentofacell,changetheformatofacell,(egmakingtextsitalic,orchangingthenumberofdecimalplacestobeshowntobe,ormakingthecellslookred),emptyingthecontentsofcells,etcYoucanalsoperformcalculationsandthenplacetheresultsintospecifycellsThebasicstructureofasubroutineisthefollowing:Subnameofsubroutine()(Variabledeclaration)(Mainbodyofprogram)EndSubSincesubroutinesoperatesbydirectlymanipulatingcellsWeneedtoknowhowtospecifythepropertiesofcellsMostofthecodesforsubroutinesmacroshavethepatternobjectmethodVeryoftenthe“object”inuseisCells,Range,Sheets,WorkbooksNotethattherearenoinputargumentsbecauseSubgetsitsinput(ifany)bylookingupvaluesfromcellsdirectlySupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgLetusfirstlookatRangeThecodeisintheformRange(groupofcells)actionThegroupofcellscanbeasinglecell,egRange(“A”),itcanalsobeagroupofcells,egRange(“A:B”)Afteronehastargetedatacelloragroupofcells,onecanperform“action”onit,egbychangingitscontentusingtheequalitysign:Range(“A”)=Range(“A”)=“Youlose!”YoucanevenuseasubroutinetowriteaformulatoacellbywritingthewholeformulaasastringConsiderthefollowingSub:Subtest()Range("B")="=sumtwo(C,D)"EndSubAfterclicking“Save”,youcansetthecursortotheSubandpressthe“Play”buttontoimplementit:NowgobacktotheExcelworksheetandobservethattheformulaintheactivesheet(theExcelworksheetwithitsnameonthelowerleftcornershownwhite)isfilledinB,andtheuserdefinedfunctionthatwehavewrittenbeforegivesthesumofCandD,thatis,…Ifyouwanttoperformtheaboveonaworksheetthatisnotcurrentlyactive,say,theactiveworksheetis“Sheet”butyouwanttoperformsomeactionon“Sheet”,thenyouhavetoaddSheets(“Sheet):Subtest()'entersavalueinB,thenlinkscellBtoBSheets("Sheet")Range("B")=Sheets("Sheet")Range("B")="=B"Sheets("Sheet")Cells(,)="ThisisCells(,)"EndSubSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgAfter“Playing”theabove,thebothBandBinSheetwouldshowObservethatCellDwouldshow“ThisisCells(,)”ThisisbecauseCells(,)isactuallyDTheRangefunctionisnotthatconvenientbecauseittakesincellsintheformlikeB,ABetcAnotherwayistouseCells(x,y),wherexisthehorizontalposition,andyistheverticalpositionEgCells(,)isDCells(x,y)isagoodwaytospecifycellsinloopingTherearemanyactionsthatyoucandoforcellsEgCells(,)="deltas"inputthestring“deltas”tocellACells(,)FontItalic=TruechangethefontofGtoitalicRange("A:B")SelectselectsthecellrangeA:BRange("A:B")CopycopiesthecellrangeA:BtotheclipboardRange("H:I")PasteSpecialcopiesthecontents(butnottheformula)oftheclipboardtoH:IRange("A:I")ClearContentscleareverythinginA:IApplicationScreenUpdating=FalsestopstheExcelscreenupdatingduringtherunningofsubroutine(importantforspeedingup)SinceSubdoesnottakeinputvariableslikefunctions,youneedtofetchinputdirectlyfromcellsForexample,tocomputetheproductoftwonumbersstoredintwocells(sayCellBandB)andoutputtheresultinanothercell(sayCellB):Subtest()Cells(,)=Cells(,)*Cells(,)EndSubAbetterwayistofetchinputsusingInputBoxfunctionandoutputresultsusingMsgBoxfunctionForInputBox,thesyntaxislikethis:theinput=InputBox(prompt,title,default,xpos,ypos)theinput:theinputvariable,canbestring,orvariant,oranotherdatatypeprompt:itisthemessagethatwouldbeshowninthewindowtitle:(optional)itisthemessagewordsshownontheframeofthemessageboxdefault:(optional)itisthedefaultmessageshownintheboxforinputxpos:(optional)thehorizontallevelwherethemessageboxwouldappearinthescreenypos:(optional)theverticallevelwherethemessageboxwouldappearinthescreenUsuallywewouldnotenterdefault,xposandyposForMsgBox,thesyntaxislikethis:result=MsgBox(prompt,buttons,title,xpos,ypos)prompt:astringornumber,itisthemessagethatwouldbeshowninthewindowSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgbuttons:(optional)itspecifiesthebuttonstobeshownonthemessageboxForourapplication,justsetittosothatwewouldgetonlyasinglebuttonthat“OK”title:(optional)itisthemessagewordsshownontheframeofthemessageboxresult:(optional)itisanumericalvaluereturnedbasedonwhatthebuttonisclickedWeshouldnotusetheresultbecausewearegoingtoignorethebuttonsIfbuttons,titlesareallomitted,thenwecanshortentheabovetoMsgBoxpromptSubtest()DimxAsDouble,yAsDouble,zAsDoublex=InputBox("Enterthestnumber","test","Don’tenterletters")y=InputBox("Enterthendnumber","test")z=x*yMsgBoxzEndSubWhentheaboveSubisexecuted,thefirstinputboxwouldpopup:SupposethatisenteredandOKispressedThenthesecondinputboxwouldpopup:SupposethatisenteredandOKispressedThenthemessageboxwouldpopup:IfwechangeMsgBoxztoSupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNga=MsgBox("Theproductis"z,,"Result")thenthefinalmessageboxwouldbeHerewehaveusedthestringoperatortojointhestring“Theproductis”andthevalueofzintoasinglepieceofstringastheoutputinthemessageboxFlowControlandBooleanDataTypeVeryfrequentlywewouldneedtomakedecisionsorbranchingTherearemanyformsoftheIfthenelsestructuresHerearethetwobasicforms:If(somecondition)ThenBlockofStatementsEndIfIf(somecondition)ThenBlockofStatementsElseBlockofStatementsEndIfForasimpleexamplewheresuchdecisionsareuseful,letusconsiderthefollowingfunction:Functiondivtwo(xAsDouble,yAsDouble)AsDoubledivtwo=xyEndFunctionconditionstatementsin“Then”truefalseconditionstatementsin“Then”truefalsestatementsin“Else”SupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgWhentheinputyisset,adivisionbyoccurs,andtheoutputofthefunctionis“#VALUE!”The“#VALUE!”suggeststhatinsomemathematicalexpressions,avariablethatshouldbeassignedanumericalvalue(inthiscasedivtwo)iswronglyassignedatextvalue(inthiscase,xybecomesa“divisionby”stringoftexts)WecanusetheIfstatementtoshowthecorrecterrormessageFunctiondivtwo(xAsDouble,yAsDouble)AsVariantIfy<>Thendivtwo=xyElsedivtwo=CVErr(xlErrDiv)EndIfEndFunctionIntheabove,thefunctionCVErrreturnsanerrormessagetodivtwoItcantakequitealotofinputs,thefollowingbeingsomemorecommonlyseenones:−xlErrDivfora#DIVerror−xlErrValuefora#VALUEerror−xlErrNumfora#NUMerrorItisgenerallyagoodpracticetovalidatetheinputargumentsandreturnanerrorwithCVErrorsomemessagesthataredefinedbytheprogrammerInthecodeabove,youseethecondition“y<>”If“y<>”holds,the“Then”partoftheprogramwouldbeexecutedandwedothedivisionIf“y<>”doesnothold,thenthe“Else”partoftheprogramwouldbeexecutedandtheerrormessageisshownFrequentlyusedconditionsforDoubleandIntegervariablesare(a=n),(a<>n),(a>=b),(a>b)ForStringvariablesthatwouldbe(stringvar=“c”)(Noticethatweneedtoinclude“”forstringvariables)ForBooleanvariablesthatwouldbe(booleanvar=True),andthe“=True”partcanevenbeomittedIfstatementscanbenestedEgFunctiondivtwo(xAsDouble,yAsDouble)AsVariantIfy=ThenIfx=Thendivtwo=""Elsedivtwo="Divisionby"EndIfElsedivtwo=xyEndIfEndFunctionWhenwehavenestedIfstatementsintheprogram,foreasyreadings,programstatementsareusuallytypedinanindentedmannerIntheabove,SupplementaryExcelVBAMaterialsVBAUserDefinedFunctionsandSubroutinesCYNgIfx=Thendivtwo=""Elsedivtwo="Divisionb

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/22

VBA

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利