Vba part 1

EXCEL VBA PROGRAMMING PART 1 MORTEZA NOSHAD MSPRO.NOSHAD@GMAIL.COM 0912 - 84 98 775

WHAT CAN YOU DO WITH VBA? Inserting a bunch of text Automating a task you perform frequently Automating repetitive operations Creating a custom command Creating a custom button Developing new worksheet functions Creating custom add-ins for Excel Creating complete, macro-driven applications

ADVANTAGES AND DISADVANTAGES OF VBA Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.) Excel performs the task much faster than you can do it manually (unless, of course, you’re Clark Kent).you’re a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you or me)If you set things up properly, someone who doesn’t know anything about Excel can perform the task You can do things in Excel that are otherwise impossible — which can make you a very popular person around the officeFor long, time-consuming tasks, you don’t have to sit in front of your computer and get bored. Excel does the work, while you hang out at the water cooler VBA ADVANTAGES

ADVANTAGES AND DISADVANTAGES OF VBA You have to know how to write programs in VBA (but that’s why you bought this book, right?). Fortunately, it’s not as difficult as you might expectOther people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand- alone program, but that isn’t possible (and probably never will be) Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging and, if others are using your macros, technical support VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that the VBA code you’ve written doesn’t work properly with older versions or VBA DISADVANTAGES

VBA IN A NUTSHELL You perform actions in VBA by writing (or recording) code in a VBA module A VBA module consists of Sub procedures A VBA module can also have Function procedures VBA manipulates objects Objects are arranged in a hierarchy Objects of the same type form a collection You refer to an object by specifying its position in the object hierarchy, using a dot (a.k.a., a period) as a separator If you omit specific references, Excel uses the active objects Objects have properties

VBA IN A NUTSHELL You refer to a property of an object by combining the object name with the property name, separated by a dot You can assign values to variables Objects have methods You specify a method by combining the object with the method, separated by a dot VBA includes all the constructs of modern programming languages, including variables, arrays, and looping

MACRO Create your first macro Use relative references Macro shortcut key-Place macro Examining macro How Excel Executes Statements Saving workbooks that contain macros Understanding macro security

VISUAL BASIC EDITOR Working with the Project Explorer Working with a Code Window Getting VBA code into a module Enter the code directly. Use the Excel macro recorder to record your actions and convert them to VBA code Copy the code from one module and paste it into another. Customizing the VBA Environment

SUBS VERSUS FUNCTIONS A Sub procedure is a group of VBA statements that performs an action (or actions) with Excel. A Function procedure is a group of VBA statements that performs a calculation and returns a single value.

NAMING SUBS AND FUNCTIONS You can use letters, numbers, and some punctuation characters, but the first character must be a letter. You can’t use any spaces or periods in the name. VBA does not distinguish between uppercase and lowercase letters. You can’t embed any of the following characters in a name: #, $, %, &, @,^, *, or !. If you write a Function procedure for use in a formula, make sure the name does not look like a cell address (for example, AC12). Names can be no longer than 255 characters. (Of course, you would never make a procedure name this long.) Ideally, a procedure’s name should describe the routine’s purpose. A good practice is to create a name by combining a verb and a noun — for example, ProcessData, PrintReport, Sort_Array, or CheckFilename

EXCECUTING SUB PROCEDURES With the Run➪Run sub/UserForm command (in the VBE) & F5 key. From Excel’s Macro dialog box. You open this box by choosing Developer➪Code➪Macros). Or you can press the Alt+F8 shortcut key. require an argument. Using the Ctrl+key shortcut assigned to the Sub procedure Clicking a button or a shape on a worksheet From another Sub procedure that you write From a button on the Quick Access Toolbar From a custom item on the ribbon you develop Automatically, when you open or close a workbook When an event occurs From the Immediate window in the VBE

EXECUTING FUNCTION PROCEDURES By calling the function from another Sub procedure or Function procedure By using the function in a worksheet formula 

COMMENTS A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote. Use comments liberally and extensively to describe what the code does (which isn’t always obvious by reading the code itself). Often, code that makes perfect sense today mystifies you tomorrow

COMMENTS When testing a procedure, you may want to remove some statements temporarily. Rather than delete the statements, you can convert them to comments. Then when testing is completed, convert the comments back to statements. In the VBE, choose view➪Toolbars➪Edit to display the Edit toolbar. To convert a block of statements to comments, select the statements and click the Comment Block button. To remove the apostrophes, select the statements and click the

COMMENTS • The following tips can help you make effective use of comments: • Briefly describe the purpose of each Sub or Function procedure • you write. • Use comments to keep track of changes you make to a procedure. • Use a comment to indicate that you’re using a function or a construct • in an unusual or nonstandard manner. • Use comments to describe the variables you use, especially if you don’t use meaningful variable names. • Use a comment to describe any workarounds you develop to overcome bugs in Excel.

UNDERSTANDING VARIABLES A variable is simply a named storage location in your computer’s memory. You have lots of flexibility in naming your variables, so make the variable names as descriptive as possible. You assign a value to a variable by using the equal sign operator.You can use letters, numbers, and some punctuation characters, but the first character must be a letter. You cannot use any spaces or periods in a variable name. VBA does not distinguish between uppercase and lowercase letters. You cannot use the following characters in a variable name: #, $, %, &, or !. Variable names can be no longer than 255 characters.

UNDERSTANDING VARIABLES wear yourself out typing the entire name of a variable. Just type the first two or three characters and then hit Control+Space. The VBE will either complete the entry for you or — if the choice is ambiguous — show you a pick list to select from. In fact, this slick trick works with reserved words too. has many reserved words that you can’t use for variable names or procedure names. These include words such as Sub, Dim, With, End, and For. If you attempt to use one of these words as a variable, you may get a compile error (your code won’t run). So, if an assignment statement produces an error message, double-check and make sure that the variable name

DECLARING AND SCOPING VARIABLES

INTRODUCING THE EXCEL OBJECT MODEL

INTRODUCING THE EXCEL OBJECT MODEL 1 2 3

REFERRING TO OBJECTS In this case, the number is not in quotation marks. Bottom line? If you refer to an object by using its name, use quotation marks. If you refer to an object by using its index number, use a plain number without quotation marks.

NAVIGATING THROUGH THE HIERARCHY

USING VBA AND WORKSHEET FUNCTIONS

VBA FUNCTIONS THAT DO MORE THAN RETURN A VALUE

Vba part 1

More Related Content

What's hot

Viewers also liked

Similar to Vba part 1

Recently uploaded

Vba part 1