spot_img

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 complete 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

For Beginners

Don’t know where to start? If you’re new to Excel VBA, start with these pages. Before you know it, you’re writing your own VBA programs.

1 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 Prote Macro: Just like you can password protect workbooks and worksheets, you can password protect a macro in Excel from being viewed (and executed).

spot_img
Previous articleNumbering in Excel
Next articleAnalysis ToolPak in Excel