EXCEL VBA PROGRAMMING

PART 1MORTEZA NOSHADMSPRO.NOSHAD@GMAIL.COM0912 - 84 98 775

WHAT CAN YOU DO WITH VBA?

Inserting a bunch of textAutomating a task you perform frequentlyAutomating repetitive operationsCreating a custom commandCreating a custom buttonDeveloping new worksheet functionsCreating custom add-ins for ExcelCreating 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 taskYou 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 supportVBA 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 with a future version of Excel

VBA DISADVANTAGES

VBA IN A NUTSHELLYou perform actions in VBA by writing (or recording) code in a VBA moduleA VBA module consists of Sub proceduresA VBA module can also have Function proceduresVBA manipulates objectsObjects are arranged in a hierarchyObjects of the same type form a collectionYou refer to an object by specifying its position in the object hierarchy, using a dot (a.k.a., a period) as a separatorIf you omit specific references, Excel uses the active objectsObjects have properties

VBA IN A NUTSHELLYou 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

MACROCreate your first macro

Use relative references

Macro shortcut key-Place macroExamining macro

How Excel Executes Statements

Saving workbooks that contain macros

Understanding macro security

VISUAL BASIC EDITORWorking 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 codeCopy 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 FUNCTIONSYou 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 PROCEDURESWith 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 procedureClicking a button or a shape on a worksheetFrom another Sub procedure that you writeFrom a button on the Quick Access ToolbarFrom a custom item on the ribbon you developAutomatically, when you open or close a workbookWhen an event occursFrom 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

Private and Public Procedures

COMMENTSA 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

COMMENTSWhen 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 Uncomment Block button.

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.

• Write comments as you develop code, instead of saving the task for a final step.

UNDERSTANDING VARIABLESA 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*Don’t 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.*VBA 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 isn’t a reserved word.

WHAT ARE VBA’S DATA TYPES?When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings.

DECLARING AND SCOPING VARIABLES*Variant Data Type*Option Explicit (Keep in mind thatthe Option Explicit statement applies only to the module in which it resides)*To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You find it in the Editor tab of the Options dialog box (in the VBE, choose Tools➪Options).

WORKING WITH CONSTANTSUsing constants in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it’s better to declare the value as a constant and refer to its name rather than the value. This makes your code more readable and easier to change; should the need for changes arise, you have to change only one statement rather than several. Excel and VBA contain many predefined constants, which you can use without the need to declare them yourself

WORKING WITH OPERATORS

Don’t be shy about using parentheses even if they aren’t required especially if doing so makes your code easier to understand. VBA doesn’t care if you use extra parentheses

WORKING WITH ARRAYSAn array is a group of variables that have a common name; you refer to a specific variable in the array by using the array name and an index number. For example, you may define an array of 12 string variables to hold the names of the months of the year. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(1), the second element as MonthNames(2), and so on.Declaring arrays:the following statements both declare the same 101-element array:

Multidimensional arrays

Dynamic arrays:

Arrays you create in VBA can have as many as 60 dimensions

Dynamic array doesn’t have a preset number of elements. Declare a dynamic array with a blank set of parentheses:

Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running.

INTRODUCING THE EXCEL OBJECT MODEL

Excel is an object and it contains other objects. Those objects, in turn, contain still more objects. In other words, VBA programming involves working with an object hierarchy.

INTRODUCING THE EXCEL OBJECT MODELApplication Object:• Addin• CommandBar• Window• Workbook• WorksheetFunc

tionWorkbook Object:• Chart• Name• VBProject• Window• Worksheet

Worksheet Object:CommentHyperlinkNameOutlinePageSetupPivotTableRange

1

2

3

COLLECTIONS OF OBJECTSWith a collection it is also very easy to find out how many objects there are of that kind. Worksheets.Count gives you the number of worksheets in the active workbook. You can refer to a member of a collection in two ways:

Sub ChangeTabColor()Dim Sht As

WorksheetFor Each Sht In

Worksheets

Sht.Tab.ColorIndex = 3Next

End Sub• By the index number: Sheet(1). For sheets, this number reflects the relative position starting from the far left.

• By using the member’s name: Sheets (“Sheet1”).

REFERRING TO OBJECTSWorksheets(“Sheet1”)Worksheets(1)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.

Another collection, called Sheets, contains all the sheets (worksheets and Chart sheets) in a workbook. If Sheet1 is the first sheet in the workbook, you can reference it asSheets

(1)

NAVIGATING THROUGH THE HIERARCHYApplication.Workbooks(“Book1.xlsx”)Application.Workbooks(“Book1.xlsx”).Worksheets(1)Application.Workbooks(“Book1.xlsx”).Worksheets(1).Range(“A1”).Value

Simplifying object references

Object Properties Object Methods

Object Events Using the Object Browser

WORKING WITH RANGE OBJECTSRange(“A1:C5”) - Range(“PriceList”) – Worksheets(“Sheet1”).Range(“A1:C5”) – Workbooks(“Budget.xlsx”).Worksheets(“Sheet1”).Range(“A1:C5”)Range(“3:3”) - Range(“D:D”) - Range(“A1:B8,D9:G16”)Worksheets(“Sheet2”).Cells(2, 3) - Range(Cells(1, 1), Cells(10, 10))

Range(“A1:J10”).Value = 99Range(Cells(1, 1), Cells(10, 10)).Value = 99

Cell Property:

Offset Property:Range(“A1”).Offset(1, 2) - Range(“C2”).Offset(-1, -2)Referring to entire columns and rows:Columns(“A:C”) - Rows(“1:5”)

WORKING WITH RANGE OBJECTS

MsgBox Worksheets(“Sheet1”).Range(“A1”).ValueValue Property:Text Property:MsgBox Worksheets(“Sheet1”).Range(“A1”).TextCount Property:

MsgBox Range(“A1:C3”).CountThe Column and Row properties:MsgBox Sheets(“Sheet1”).Range(“F3”).Column

WORKING WITH RANGE OBJECTS

Range(“A1”).Font.Bold = TrueFont Property:Interior Property:Range(“A1”).Interior.Color = 8421504Range(“A1”).Interior.Color = RGB(0, 0, 0) ‘blackRange(“A1”).Interior.Color = RGB(255, 0, 0) ‘ pure redRange(“A1”).Interior.Color = RGB(0, 0, 255) ‘ pure blueRange(“A1”).Interior.Color = RGB(128, 128, 128) ‘ middle gray

WORKING WITH RANGE OBJECTS

Range(“A1:C12”).SelectThe Select method: The Copy and Paste methods:Sub CopyRange()

Range(“A1:A12”).SelectSelection.CopyRange(“C1”).SelectActiveSheet.Paste

End Sub**************************Sub CopyRange2()

Range(“A1:A12”).Copy Range(“C1”)End Sub

Clear & Delete Methods

USING VBA AND WORKSHEET FUNCTIONSUsing Built-in VBA FunctionsDate & Len

Sub GetFileSize()Dim TheFile As StringTheFile = “C:\MSOFFICE\EXCEL\EXCEL.EXE”MsgBox FileLen(TheFile)

End Sub

FileLen:

VBA FUNCTIONS THAT DO MORE THAN RETURN A VALUE

Using Worksheet Functions in VBATotal =

Application.WorksheetFunction.Sum(Range(“A1:A12”))Using Custom Functions