Module_M - E X TENDE D L E ARN IN G MOD U L E M PROGRAMMING IN EXCEL WITH VBA Student Learning Outcomes 1 Explain the value of using VBA with Excel 2

Module_M - E X TENDE D L E ARN IN G MOD U L E M PROGRAMMING...

This preview shows page 1 - 3 out of 28 pages.

EXTENDED LEARNING MODULE M PROGRAMMING IN EXCEL WITH VBA Student Learning Outcomes Explain the value of using VBA with Excel. Define a macro. Build a simple macro using a sub procedure and a function procedure. Describe an object. Explain the difference between a comment, a variable, and a constant. List the various Visual Basic application data types and operators. Describe and build a macro that uses the If-Then-Else, For-Next, Do-Until, Do-While,and Select Casestructures. 1.2.3.4.5.6. 7.
Image of page 1
M.2 Extended Learning Module M Introduction VBA, which stands for Visual Basic for Applications, is a programming language devel- oped by Microsoft. Excel, along with the other members of Microsoft Office, includes the VBA language. VBA extends and customizes Excel, allowing you to do things that Excel itself is not able to do, such as calculating values in cells based on user input values. Excel VBA is a programming application that allows you to use Visual Basic code to run the many features of Excel, thereby allowing you to customize your Excel applica- tions. Units of VBA code are often referred to as macros. Although this module covers more formal terminology, the term macro will be used as a general way to refer to any VBA code. It is not essential to make a further software purchase in order to learn the ele- ments of Visual Basic programming. Excel VBA comes free with Excel (97, 2000, 2002, 2003, etc.) and provides a great deal of the functionality (which happens to be the topic of this module). One of the great advantages of Excel VBA is the macro recorder. The macro recorder is a software tool that lets you record a sequence of commands in Excel and save them as a macro. This is invaluable if you are struggling with some programming syntax. Just get the recorder to do it for you and then view the code to see how it is done. Why VBA? Excel programming terminology can be a bit confusing. For example, VBA is a program- ming language, but it also serves as a macro language. A macro language is a program- ming language that includes built-in commands that mimic the functionality available from menus and dialog boxes within an application. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros. A macro is a set of actions recorded or writ- ten by a user. For instance, you can create a macro that always prints your name in bold on a spreadsheet. You can name it PrintName and then reuse the PrintName macro in any spreadsheet. This term means that a series of steps is completed automatically. For example, if you write a macro that adds a background color to some cells and then prints the worksheet, you have automated those two steps. People use Excel for thousands of different tasks, such as: Budgeting and forecasting Analyzing data Creating invoices and other forms Developing charts from data One thing virtually every user has in common is the need to automate some aspect of Excel. For example, you might create a VBA macro to format and print a month-end
Image of page 2
Image of page 3

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture