Visual Basic Code Examples In Excel

-->

Visual Basic Code Examples In Excel Free
Visual Basic Code Examples For Excel
Microsoft Excel Visual Basic Examples
Visual Basic Code Examples In Excel For Beginners

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The examples in this article use the Visual Basic methods listed in the following table.

The examples in this article use the properties in the following table.

How to Select a Cell on the Active Worksheet

To select cell D5 on the active worksheet, you can use either of the following examples:

In the code above, the cell A1 has its value changed to 'My Test.' After the text is changed in the A1 cell, a message box opens and displays the text 'Cell value change finished.' This is just a small example of how you can use the MsgBox function. After you finished changing your VBA code, click the play button again.
Visual Basic Sample Codes Ebook. Visual Basic Sample Codes E-Book is written by our tutor, Dr.Liew. It comprises 258 pages of captivating contents and 48 fascinating Sample Codes.Perfect source of reference for your VB projects. Check it out by clicking the book picture.

Due to a quirk I explain below, you also need to set up a reference to the Windows Script Host Object Model if you want to implement this VBA code example #3 and/or the following sample #4. In order to create these references, go to the Visual Basic Editor and follow these 4 easy steps: Step #1: Go to the Tools menu and select “References”.

How to Select a Cell on Another Worksheet in the Same Workbook

To select cell E6 on another worksheet in the same workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 1 above to select the cell:

How to Select a Cell on a Worksheet in a Different Workbook

To select cell F7 on a worksheet in a different workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 1 above to select the cell:

How to Select a Range of Cells on the Active Worksheet

To select the range C2:D10 on the active worksheet, you can use any of the following examples:

How to Select a Range of Cells on Another Worksheet in the Same Workbook

To select the range D3:E11 on another worksheet in the same workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 4 above to select the range:

How to Select a Range of Cells on a Worksheet in a Different Workbook

To select the range E4:F12 on a worksheet in a different workbook, you can use either of the following examples:

Or, you can activate the worksheet, and then use method 4 above to select the range:

How to Select a Named Range on the Active Worksheet

To select the named range 'Test' on the active worksheet, you can use either of the following examples:

How to Select a Named Range on Another Worksheet in the Same Workbook

To select the named range 'Test' on another worksheet in the same workbook, you can use the following example:

Or, you can activate the worksheet, and then use method 7 above to select the named range:

How to Select a Named Range on a Worksheet in a Different Workbook

To select the named range 'Test' on a worksheet in a different workbook, you can use the following example:

Or, you can activate the worksheet, and then use method 7 above to select the named range:

How to Select a Cell Relative to the Active Cell

To select a cell that is five rows below and four columns to the left of the active cell, you can use the following example:

To select a cell that is two rows above and three columns to the right of the active cell, you can use the following example:

Note

An error will occur if you try to select a cell that is 'off the worksheet.' The first example shown above will return an error if the active cell is in columns A through D, since moving four columns to the left would take the active cell to an invalid cell address.

How to Select a Cell Relative to Another (Not the Active) Cell

To select a cell that is five rows below and four columns to the right of cell C7, you can use either of the following examples:

How to Select a Range of Cells Offset from a Specified Range

To select a range of cells that is the same size as the named range 'Test' but that is shifted four rows down and three columns to the right, you can use the following example:

If the named range is on another (not the active) worksheet, activate that worksheet first, and then select the range using the following example:

How to Select a Specified Range and Resize the Selection

To select the named range 'Database' and then extend the selection by five rows, you can use the following example:

How to Select a Specified Range, Offset It, and Then Resize It

To select a range four rows below and three columns to the right of the named range 'Database' and include two rows and one column more than the named range, you can use the following example:

How to Select the Union of Two or More Specified Ranges

To select the union (that is, the combined area) of the two named ranges 'Test' and 'Sample,' you can use the following example:

Note

that both ranges must be on the same worksheet for this example to work. Note also that the Union method does not work across sheets. For example, this line works fine.

but this line

returns the error message:

Union method of application class failed

How to Select the Intersection of Two or More Specified Ranges

To select the intersection of the two named ranges 'Test' and 'Sample,' you can use the following example:

Note that both ranges must be on the same worksheet for this example to work.

Examples 17-21 in this article refer to the following sample set of data. Each example states the range of cells in the sample data that would be selected.

How to Select the Last Cell of a Column of Contiguous Data

To select the last cell in a contiguous column, use the following example:

When this code is used with the sample table, cell A4 will be selected.

How to Select the Blank Cell at Bottom of a Column of Contiguous Data

To select the cell below a range of contiguous cells, use the following example:

When this code is used with the sample table, cell A5 will be selected.

How to Select an Entire Range of Contiguous Cells in a Column

To select a range of contiguous cells in a column, use one of the following examples:

When this code is used with the sample table, cells A1 through A4 will be selected.

How to Select an Entire Range of Non-Contiguous Cells in a Column

To select a range of cells that are non-contiguous, use one of the following examples:

When this code is used with the sample table, it will select cells A1 through A6.

How to Select a Rectangular Range of Cells

In order to select a rectangular range of cells around a cell, use the CurrentRegion method. The range selected by the CurrentRegion method is an area bounded by any combination of blank rows and blank columns. The following is an example of how to use the CurrentRegion method:

This code will select cells A1 through C4. Other examples to select the same range of cells are listed below:

In some instances, you may want to select cells A1 through C6. In this example, the CurrentRegion method will not work because of the blank line on Row 5. The following examples will select all of the cells:

How to Select Multiple Non-Contiguous Columns of Varying Length

To select multiple non-contiguous columns of varying length, use the following sample table and macro example:

When this code is used with the sample table, cells A1:A3 and C1:C6 will be selected.

Notes on the examples

The ActiveSheet property can usually be omitted, because it is implied if a specific sheet is not named. For example, instead of

you can use:

The ActiveWorkbook property can also usually be omitted. Unless a specific workbook is named, the active workbook is implied.

When you use the Application.Goto method, if you want to use two Cells methods within the Range method when the specified range is on another (not the active) worksheet, you must include the Sheets object each time. For example:

For any item in quotation marks (for example, the named range 'Test'), you can also use a variable whose value is a text string. For example, instead of

you can use

where the value of myVar is 'Sheet1'.

What are Macros?

They are a series of commands used to automate a repeated task. This can be run whenever the task must be performed.

How to access Macros

Click on the ‘View’ tab, at the end you’ll find the function ‘Macros’ arranged in the Macros group. Click the arrow under ‘Macros’ where you can manage your macro performances easily.

To edit a macro, click on the ‘Edit’ button, this will take you to the ‘Visual Basic Editor’ where you can easily modify the macro to do what you want.

Visual Basic Editor view:

10 Useful Examples of Macros for Accounting:

1. Macro: Save All

Helpful for saving all open excel workbooks at once. It is advised to run this macro before running other macros or performing tasks that’s could potentially freeze Microsoft Excel.

Name this macro ‘SaveAll’

Example code:

2. Macro: Comments and Highlights

Helpful is you use a lot of comments and highlights while editing worksheets. Running this macro generates a new tab at the front of the worksheet with a listing of every cell with a comment or highlight in the workbook. Each cell reference is a hyperlink that leads directly to the cell with the comment/highlight. The summary tab lists the value within the cell and the text in the comment.

Additionally, there is an ‘Accept’ button that removes the highlight or deletes the comment from the chosen cell. Once all changes are accepted, the summary tab will be deleted.

NB: This macro only sets up to find certain highlights of yellow, but this can be modified.

3. Macro: Insert a Check Mark

Helpful for footing (Adding a column of numbers) trial balances, schedules and reconciliations. It is often common to put a check mark below the total to show the column total is accurate.

This macro will insert a red check mark in the active cell. Once this is added all you will be required to do is select the cell you want to check-mark in and run the macro. Name this macro ‘Checkmark’.

Example code:

4. Macro: Mail Workbook

Helpful to send a lot of excel files via email. Running this macro creates a new Microsoft Outlook email with the last saved version of the open workbook. The email, by default, will not be addressed by anyone, the subject will be the name of the workbook, and the body will be ‘See attached’. However, these settings can be customized.

Example code:

5. Macro: Copying the Sum of Selected Cells

Helpful in copying the sum of several cell in a spreadsheet. These cells can be scattered throughout the spreadsheet or all in the same row/column.

Once this macro is added you need to highlight the cells you want to sum, then run the macro, and then paste into the cell you want the sum in.

NB: This will just paste the value of the sum, not a sum() formula

Example code:

6. Macro: Open Calculator

Helpful to open a calculator.

Example code:

Sub OpenCalculator()
Application.ActivateMicrosoftApp Index:=0
End Sub

7. Macro: Refresh All Pivot Tables

Helpful to refresh all pivot tables in the whole workbook in a single shot.

Example code:

8. Macro: Multiply all the Values by a Number

Helpful if you have a list of numbers you want to have multiplied by a particular number. Select the range of cells you need and run the example code below. It will first ask you for the number with whom you want to multiply and then instantly multiply all the numbers in the range with it.