1 / 27

Mastering VBA and Macro Creation in Excel with Object-Oriented Programming

Unlock the power of Visual Basic for Applications (VBA) in Excel by learning object-oriented programming (OOP) principles. This guide covers how to create effective macros, utilizing objects such as workbooks, worksheets, and ranges. Discover the benefits of using classes and methods to streamline your coding process. Learn to identify and declare variables, create subroutines, and write conditional statements to handle data more efficiently. Whether you're a beginner or looking to refine your skills, this resource will enhance your Excel automation capabilities.

thalia
Télécharger la présentation

Mastering VBA and Macro Creation in Excel with Object-Oriented Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA and Macro creation (using Excel) DSC340 Mike Pangburn

  2. Agendafor Today Object-OrientedProgramming CreatingMacroswithVBA

  3. OBJECTORIENTED PROGRAMMING: VBA

  4. What is O-O programming? • Aprogrammingstyle thatuses“objects” to comprise programs. • Objects: • In a pure O-O language, every thing in the program is an object. • An object is adatastructureconsistingofattributes(datafields)andmethods(actions). • Theattributes oftheobject hold its current information • Themethods of the object determine what the object can do

  5. Different objects types: classes • Each programming environment comes with many different kinds of predefined objects. • An object type is called a class. A class is thus some type of object. • All objects within a class hold the same kind of information (identical attributes) and can perform the same actions (identical methods). • A class is the “cookie cutter”/template you use to create new object instances. Hollywood analogy: in BattlestarGalactica there are 12 “classes” of human robots. One of these 12 model types was the Sharon model.

  6. Creating and using objects • You 1st need a class class Sharon Attributes: height, 3Dbodyshape, faceimage, currentlocation, currentlove, pastexperienceslog Methods: imitateHuman, fallInLove(person), doSecretMission(password) • You then “declare” a new object and can subsequently assign its attribute value and invoke its methods. • Example: declaring a new Sharon object, assigning a value, and calling a method Sharon sharonNo55 sharonNo55.currentlocation = “KA.493.X7.1034” sharonNo55.fallInLove(Helo)

  7. ObjectOrientedProgrammingFundamentals Appropriateobjectclasses(theirattributesandmethods)heavilydepend ontheproblemyou areworkingon. Example: Asystemforabank: ◦Objects:Customers,Accounts,etc. ◦Attributes:FirstName,LastName,SSN,Address,etc. ◦Methods:Withdraw,Openanaccount,Deposit,Cashcheck,etc.

  8. VBA:VisualBasicforApplications VBAisaspecializedversionofMicrosoft’swell-knownVisualBasic language. VBAisanObject-OrientedlanguagewithinMicrosoftOfficesuite: ◦Excel,Word,Access,andPowerPoint. ◦WewillfocusonExcel. ◦ShortcuttoaccesstheVBAinterfaceinExcel:Alt+F11. Exceltreatseverythinginaspreadsheetasobjets.So,VBAletsusplay withalltheseobjects. ◦Objectthatweareinterestedin:Workbooks,worksheets,cells,rows,ranges. ◦Thereareover200different class (types of objects) inExcel.

  9. CodingwithVBA • TherearetwoapproachestowriteacodeinVBA: • StandardCoding • MacroRecording • TheMacroapproach“records”aseriesofmouse-clicksandkeyboard • strokes. • Advantage:CorrespondingVBAcodeiscreatedautomaticallyandrecordedforyou. • Disadvantage:It’slimited. • Totacklethislimitation,weuseamixedapproach: • First,recordasimplemacro. • Then,tweakittoachievemorecomplextasks.

  10. ProblemDefinition Wehavedataspanning3columnsand13rows(C6:F18). ◦Datashouldbestoredinthegivenrangeinordertouseitforotherapplication. ◦Unfortunately,therearesomeerrorsinthedataentryprocess,sosomeoftherowsareshifted totherightbyonecolumn.

  11. ProblemDefinition Wehavedataspanning3columnsand13rows(C6:F18). ◦Datashouldbestoredinthegivenrangeinordertouseitforotherapplication. ◦Unfortunately,therearesomeerrorsinthedataentryprocess,sosomeoftherowsareshifted totherightbyonecolumn. Ourjobistocorrectallthesemistakes: ◦First,recordasimplemacrothatcorrectaspecificrow(sayRow8). ◦Then,tweakittomakecorrectioninanygivenrow. ◦Finally,weletthecodecheckanymistakeandcorrectit. Whiledoingthis,welearnhowto ◦ ◦ ◦ ◦ Declareandnamevariables. CreateSubroutinesandFunctions(We’lllearnwhattheyareshortly). Useconditionalstatements. Createrepetitions.

  12. OurFirstMacro • Download and open VBA_Example_class.xlsm • In Excel 2010, we should save the file as a Macro Enabled Workbook (i.e., in the .xlsm format).

  13. OurFirstMacro Find the Macros option under the Excel 2010 View tab to record a VBA macro. Let’s call our Macro “Shifter.” We want our macro to do the following: ◦ ◦ ◦ ◦ Selectthe range D8:F8. Cuttheselectedcells. SelectcellC8. Pastethecutcells. To see the VBA code generated by your actions, go into VBA (Alt+F11) and then near the upper left of the window expand the Modules folder and double-click on Module1.

  14. Subroutines NoticethatthecodestartswithSubcommandandendswithEndSubcommand. SubreferstotheSubroutines. ◦Subroutine:Aportionofcodewithinalargerprogramthatperformsaspecifictaskandis relativelyindependentoftheremainingcode. ◦Wecanuseasubroutineinadifferentsubroutineby“calling”it. ◦e.g.,CallShifter().

  15. ExecutionofSubroutines Eachlineofasubroutineisexecutedsequentially. Let’sseehowourmacroisexecuted. • (The first threegreenlinesfollowing each ‘ are simply comments/documentation.) • TherealfirstlineisRange("D8:F8").Select,anditselectsthecellswewanttoselect. • Thesecondline,Selection.Cut,cutstheselectedcells. • Thethirdline,Range("C8").Select,selectsthecell C8. • Finally,thelastline,ActiveSheet.Paste,pastesthe cut valuestothe selectedcelloftheworksheet.

  16. SubroutineswithInputArguments ThelimitationofShifteristhatitonlycorrectsRow8. Wecansolvethisbycreatingasubroutinewhich will take a row number as aninput parameter. SubShiftOneColumn(RowNumAsInteger) CODEBLOCK EndSub ◦RowNumistheinputvariable.Wenameit. ◦AsIntegerpartdeclaresthedatatypeofourinputvariable. ◦CommonDataTypes: DataType Boolean Integer Double String Description HoldseitherTRUEorFALSEvalue. Integersbetween-32000and32000 Doubleprecisionnumbers,canholdfractionalnumbers(The rangeofdoublenumberisverylarge) Asetofcharacters(likesentences)

  17. ShiftingAnyGivenColumn SubShiftOneColumn(RowNumAsInteger) Range("D"&RowNum&":F"&RowNum).Select Selection.Cut Range("C"&RowNum).Select ActiveSheet.Paste EndSub HowdowetellVBAtoshifttherowaccordingtoourinput,RowNum? ◦Currently,weselectcellsD8,E8,F8bywriting"D8:F8". ◦We will construct that D#:F# syntax, for our row # (i.e., RowNum) Range("D"&RowNum&":F"&RowNum).Select ◦In Excel and VBA the & operator simply combines (“concatenates”) text together

  18. ShiftingRepeatedly We now haveasubroutinethatcancorrectanygivenrow. Wewanttoapplythissubroutinetoanyrowsbetween6and18. We use a loop (e.g., a FOR-NEXTLoop) forthistask: For-Next Loop Syntax ForvarName=start_valToend_valStepstep_size CODEBLOCK NextvarName ◦TheabovecodeassignsthevalueofvariablevarNametostart_val. ◦Then,executesthecodeinsidetheloop. ◦Afterthat,increasesthevalueofvariablevarNamebystep_size ◦And,runsthecodeagain. ◦RepeatsthatuntilthevalueofvariablevarNamereachesend_val.

  19. ShiftingRepeatedly SubShifterLoop ForRowNum=6To18Step1 CallShiftOneColumn(RowNum) NextRowNum EndSub Let’screateourFOR-NEXTLoop. ◦ThevariablewhosevaluewechangeisRowNum. ◦TheStartvalueis6.TheEndvalueis18. ◦And,theStepSizeis1. Ineachstep,wecallthesubroutineShiftOneColumn. Notice that we have started to use the value called RowNum that we introduced….

  20. VariableDeclaration ThesubroutineShiftOneColumnrequiresanintegerinput. We decided to introduce something called RowNumwhich stores a row number. ◦We want the data type for RowNumto be Integer. The VBA interpreter will attempt to guess (scary!) the data type if you forget to declare your variables. Toavoidthatproblem,weneedtodefinethevariablesweuseproperly: Variable Declaration in VBA DimvarNameAsdataType Somerulesaboutvariabledeclaration: ◦Variablesnameshavetostartwithaletter,andhavenospaces. ◦VBAisnotcasesensitive(RowNumandrownumarethesame). ◦Namesthataremorethanonewordareusuallywrittenwiththefirstletterofwords capitalized; it’s notrequiredbutit’s fairly standard practice).

  21. ShiftingRepeatedly So,weaddalinebeforetheloopwherewedeclareourvariableRowNum. SubShifterLoop DimRowNumAsInteger ForRowNum=6To18Step1 CallShiftOneColumn(RowNum) NextRowNum EndSub

  22. CheckingIfFirstColumnisEmpty Weneedtocheckwhetherthefirstcolumnofarowisemptyornot. VBA’sconditionalstatementIF-THEN-ELSEallowsustoachievethis goal. IF-THEN-ELSESyntax Iftest_condThen ... CODEBLOCKexecutediftest_condholds ... Else ... CODEBLOCKexecutediftest_condfails ... EndIf

  23. AFunctiontoChecktheFirstColumn Wecould writeourconditionalstatementtocheckthefirstcolumn withinthesubroutine, but wewilldefineaseparate“Function”for thecheckingtask. Functionsareverysimilartothesubroutines.Then,whyafunction? ◦Unlikeasubroutine,butFunctionsreturnavalue. ◦Usefulforreportingtheresultofacalculationorotherresults. ◦Ourfunctionwillreturn… 1ifthefirstcolumnisempty 0otherwise.

  24. MoreonFunctionsvs.Subroutines Functionsarenotalwaystherightchoice: ◦Afunctioncannotdirectlywritedatabacktothespreadsheet. ◦Wecanwritedatatothespreadsheetviasubroutines.Easiestway: How to write data to cells Cells(row_num,col_num).Value=x ◦e.g.,Cells(10,3).Value="DucksRock"writesDucksRocktothecell C10. (Tryit!) ◦ In this example, our function doesn’t need to changethevalueofcell, but our function does return a value.

  25. AFunctiontoChecktheFirstColumn Let’sgobacktoourtask:Creatingafunctiontocheckthefirstcolumn. WenameourfunctionasCheckColOne. Whatisourinputargument?RowNumber. Function to check the first column FunctionCheckColOne(RowNumasInteger) IfCells(RowNum,3).Value=""Then CheckColOne=1 Else CheckColOne=0 EndIf EndFunction Howdowecheckifthefirstcolumnisempty? ◦ ◦ ◦ ◦ WecanuseCells(row_num,col_num).Value.Then,checkwhetheritisempty. Weknowtherownumber:It’sourinputargument,RowNum. Thecolumnnumberis3sincethefirstcolumnofdataisinColumnC. So,ourtestconditionis if Cells(RowNum,3).Value="".

  26. UsingtheCheckFunctionintheMainSubroutine SubShifterLoop() DimRowNumAsInteger ForRowNum=6To18Step1 IfCheckColOne(RowNum)Then CallShiftOneColumn(RowNum) EndIf NextRowNum EndSub

  27. Practice: Extending our program • How would we extend our program if we wanted to have the program highlight each moved row with a yellow background? • Remember the approach most business people using VBA take: • Perform “Macro Recording” to start • Use/modify the resulting code

More Related