VBA

 

VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs.

1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro.

2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.

5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation: In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.

10 Date and Time: Learn how to work with dates and times in Excel VBA.

11 Events: Events are actions performed by users which trigger Excel VBA to execute code.

12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.

14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

15 ActiveX Controls: Learn how to create ActiveX controls, such as command buttons, text boxes, list boxes etc.

16 Userform: This chapter teaches you how to create an Excel VBA Userform.

VBA +

 

Become a VBA pro! You can find related examples and features on the right side of each chapterat the bottom of each chapter. Below you can find a quick overview.

1 Create a Macro: Swap Values | Run Code from a Module | Macro Recorder | Use Relative References | FormulaR1C1 | Add a Macro to the Toolbar | Enable Macros | Protect Macro

2 MsgBox: MsgBox Function | InputBox Function

3 Workbook and Worksheet Object: Path and FullName | Close and Open | Loop through Books and Sheets | Sales Calculator | Files in a Directory | Import Sheets | Programming Charts

4 Range Object: CurrentRegion | Dynamic Range | Resize | Entire Rows and Columns | Offset | From Active Cell to Last Entry | Union and Intersect | Test a Selection | Possible Football Matches | Font | Background Colors | Areas Collection | Compare Ranges

5 Variables: Option Explicit | Variable Scope | Life of Variables | Type Mismatch

6 If Then Statement: Logical Operators | Select Case | Tax Rates | Mod Operator | Prime Number Checker | Find Second Highest Value | Sum by Color | Delete Blank Cells

7 Loop: Loop through Defined Range | Loop through Entire Column | Do Until Loop | Step Keyword | Create a Pattern | Sort Numbers | Randomly Sort Data | Remove Duplicates | Complex Calculations | Knapsack Problem

8 Macro Errors: Debugging | Error Handling | Err Object | Interrupt a Macro | Subscript Out of Range | Macro Comments

9 String Manipulation: Separate Strings | Reverse Strings | Convert to Proper Case | Instr | Count Words

10 Date and Time: Compare Dates and Times | DateDiff Function | Weekdays | Delay a Macro | Year Occurrences | Tasks on Schedule | Sort Birthdays

11 Events: BeforeDoubleClick Event | Highlight Active Cell | Create a Footer Before Printing | Bills and Coins | Rolling Average Table

12 Array: Dynamic Array | Array Function | Month Names | Size of an Array

13 Function and Sub: User Defined Function | Custom Average Function | Volatile Functions | ByRef and ByVal

14 Application Object: StatusBar | Read Data from Text File | Write Data to Text File | Vlookup

15 ActiveX Controls: Text Box | List Box | Combo Box | Check Box | Option Buttons | Spin Button | Loan Calculator

16 Userform: Userform and Ranges | Currency Converter | Progress Indicator | Multiple List Box Selections | Multicolumn Combo Box | Dependent Combo Boxes | Loop through Controls | Controls Collection | Userform with Multiple Pages | Interactive Userform

VBA +

 

Below you can find a description of the first 60 pages.

Swap Values: This example teaches you how to swap two values in Excel VBA. You will often need this structure in more complicated programs as we will see later.

2 Run Code from a Module: As a beginner to Excel VBA, you might find it difficult to decide where to put your VBA code. This example teaches you how to run code from a module.

3 Macro Recorder: The Macro Recorder, a very useful tool included in Excel VBA, records every task you perform with Excel. All you have to do is record a specific task once. Next, you can execute the task over and over with the click of a button.

4 Use Relative References: By default, Excel records macros in absolute mode. However, sometimes it is useful to record macros in relative mode.

5 FormulaR1C1: This example illustrates the difference between A1, R1C1 and R[1]C[1] style in Excel VBA.

6 Add a Macro to the Toolbar: If you use an Excel macro frequently, you can add it to the Quick Access Toolbar. This way you can quickly access your macro.

7 Enable Macros: Enable macros in Excel when the message bar appears. Change your macro security settings in the Trust Center. To create macros, turn on the Developer tab.

8 Protect Macro: Just like you can password protect workbooks and worksheets, you can password protect a macro in Excel from being viewed (and executed).

9 MsgBox Function: The MsgBox function in Excel VBA can return a result while a simple MsgBox cannot.

10 InputBox Function: You can use the InputBox function in Excel VBA to prompt the user to enter a value.

11 Path and FullName: The Path property in Excel VBA returns the complete, saved path to the workbook (Excel file). The FullName property in Excel VBA returns the complete, saved path, including the name of the workbook.

12 Close and Open: The Close and Open Method in Excel VBA can be used to close and open workbooks. Remember, the Workbooks collection contains all the Workbook objects that are currently open.

13 Loop through Books and Sheets: Use Excel VBA to loop through all open workbooks and worksheets and display all the names.

14 Sales Calculator: Let's create a VBA program that calculates the total sales of each employee over a period of three years.

15 Files in a Directory: Use Excel VBA to loop through all closed workbooks and worksheets in a directory and display all the names.

16 Import Sheets: In this example, we will create a VBA macro that imports sheets from other Excel files into one Excel file.

17 Programming Charts: Use Excel VBA to create two programs. One program loops through all charts on a sheet and changes each chart to a pie chart. The other program changes some properties of the first chart.

18 CurrentRegion: This example illustrates the CurrentRegion property in Excel VBA. The current region is a range bounded by any combination of blank rows and blank columns.

19 Dynamic Range: Learn how to create a program in Excel VBA that colors the maximum value of a dynamic range.

20 Resize: The Resize property in Excel VBA makes a range a specific number of rows and columns larger or smaller. The Resize property always takes the top left cell of a range as the starting point.

21 Entire Rows and Columns: This example teaches you how to select entire rows and columns in Excel VBA. Are you ready?

22 Offset: The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range.

23 From Active Cell to Last Entry: This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column.

24 Union and Intersect: The Union method in Excel VBA returns a Range object that represents the union of two or more ranges. The Intersect method in Excel VBA returns a Range object that represents the intersection of two or more ranges.

25 Test a Selection: This program in Excel VBA uses the Count property, IsNumeric function, IsEmpty function and Intersect method to test a selection.

26 Possible Football Matches: Learn how to create a program in Excel VBA that shows a print preview of all the possible football matches from a list of teams.

27 Font: The Font property of the Range object in Excel VBA gives access to a lot of other properties such as the Color property and the Bold property.

28 Background Colors: Changing background colors in Excel VBA is easy. Use the Interior property to return an Interior object. Then use the ColorIndex property of the Interior object to set the background color of a cell.

29 Areas Collection: This example illustrates the Areas collection in Excel VBA. Our range has two areas.

30 Compare Ranges: Learn how to create a program in Excel VBA that compares randomly selected ranges and highlights cells that are unique.

31 Option Explicit: We strongly recommend to use Option Explicit at the start of your Excel VBA code. Using Option Explicit forces you to declare all your variables.

32 Variable Scope: The scope of a variable in Excel VBA determines where that variable may be used. There are three scoping levels: procedure level, module level, and public module level.

33 Life of Variables: Sometimes you want to retain the value of a variable in Excel VBA when a procedure ends. You can achieve this by using the Static keyword.

34 Type Mismatch: The type mismatch error in Excel VBA occurs when you try to assign a value to a variable that isn't of the correct type.

35 Logical Operators: The three most used logical operators in Excel VBA are: And, Or and Not. As always, we will use easy examples to make things more clear.

36 Select Case: Instead of multiple If Then statements in Excel VBA, you can use the Select Case structure.

37 Tax Rates: Learn how to create a program in Excel VBA that calculates the tax on an income.

38 Mod Operator: The Mod operator in Excel VBA gives the remainder of a division.

39 Prime Number Checker: Let's create a VBA program to check whether a number is a prime number or not.

40 Find Second Highest Value: Use Excel VBA to find the second highest value in a randomly selected range (this range can be of any size).

41 Sum by Color: Learn how to create a program in Excel VBA that sums numbers by color.

42 Delete Blank Cells: In this example, we will create a VBA macro that deletes blank cells. First, we declare two variables of type Integer.

43 Loop through Defined Range: Use Excel VBA to loop through a defined range. For example, when we want to square the numbers in the range A1:A3.

44 Loop through Entire Column: Use Excel VBA to loop through the entire first column and color all values that are lower than a certain value.

45 Do Until Loop: VBA code placed between Do Until and Loop will be repeated until the part after Do Until is true.

46 Step Keyword: You can use the Step keyword in Excel VBA to specify a different increment for the counter variable of a loop.

47 Create a Pattern: Learn how to create a program in Excel VBA that creates a pattern. Place a command button on your worksheet and add the following code lines.

48 Sort Numbers: In this example, we will create a VBA macro that sorts numbers. First, we declare three variables of type Integer and one Range object.

49 Randomly Sort Data: Let's create a VBA program that randomly sorts data (in this example randomly sorts names). Are you ready?

50 Remove Duplicates: Use Excel VBA to remove duplicates. In column A we have 10 numbers. We want to remove the duplicates from these numbers and place the unique numbers in column B.

51 Complex Calculations: Learn how to create a program in Excel VBA that calculates any term Tk and summation of terms up to N.

52 Knapsack Problem: In this example, we will create a VBA macro that solves a small instance of a knapsack problem.

53 Debugging: This example teaches you how to debug code in Excel VBA. By pressing F8, you can single step through your code.

54 Error Handling: Use Excel VBA to create two programs. One program simply ignores errors. The other program continues execution at a specified line upon hitting an error.

55 Err Object: When an error in Excel VBA occurs, the properties of the Err object are filled with information.

56 Interrupt a Macro: You can interrupt a macro in Excel at any time by pressing Esc or Ctrl + Break.

57 Subscript Out of Range: The 'subscript out of range' error in Excel VBA occurs when you refer to a nonexistent collection member or a nonexistent array element.

58 Macro Comments: A macro comment is a piece of text in a macro which will not be executed by Excel VBA. It is only there to provide you information about the macro.

59 Separate Strings: Let's create a program in Excel VBA that separates strings. Place a command button on your worksheet and add the following code lines.

60 Reverse Strings: Learn how to create a program in Excel VBA that can reverse strings.