# Easy excel macros

This is easyexcelmacros.com. This site provides you with free tips and hints which will make developing your own macros in Excel VBA easy.

This is easyexcelmacros.com. This site provides you with free tips and hints which will make developing your own macros in Excel VBA easy.

You can use vbCrLf. vbCrLf stands for VB Carriage Return Line Feed.

MsgBox "Line 1" & vbCrLf & "Line 2"

It puts a return and a new line as shown in the message box below.

In order to display all the sheet names of an excel file we need to know how many sheets the excel file contains. In Excel VBA we can use the for each..next loop for this.

Dim exampleSheet As Worksheet

For Each exampleSheet In Worksheets

MsgBox exampleSheet.Name

Next exampleSheet

result:

To wait a certain amount of time before executing the macro use this simple code.

Application.Wait Now + TimeValue("00:00:5")

MsgBox ("5 sec has elasped")

If you want a macro to run automatically when opening your workbook you can use the Workbook_Open event. In your visual basic editor, click on ThisWorkBook. From the left dropdown list choose workbook and from the right drop down list choose open.

Save your macro and open the workbook and a message box saying Good Morning should appear.

The following code line will allow Excel VBA to continue the next code line upon hitting an error.

On Error Resume Next

example:

On Error Resume Next

Range("").Value = 3

Range("A1").Value = 2

As a result the incorrect code line will be ignored and only a value of 2 will be placed into Cell A1.

Example:

To select a range from the active cell tot the last entry in a column or a row use the following VBA code.

Range(ActiveCell, ActiveCell.End(xlToRight).Select

Result:

You can use xlToLeft, xlUp and xlDown for the other directions.

To extracts the leftmost characters from a string use left.

left("example text", 4) will give exam

To extracts the rightmost characters from a string use right.

Right("example text", 2) will give xt

To get the length of a string, use len

Len("example text") will give 12 (including space!)

To find the position of substring in a string use Instr

Instr("example text", "am") will give 3 (string "am" found at position 3)

To extract a substring starting in the middle of a string use Mid.

Mid("example text", 9, 4) will give text

When we execute the following code in Excel VBA it will never stop.

x = 5

Do While x > 2

x = x+1

loop

It never stops because the part after Do While never becomes false. x will always stay higher than 2. To halt this infinite loop in Excel VBA simply press Ctrl + Break.

excel vba variable | excel vba integer variable | excel vba string variable | excel vba double variable | excel vba boolean variable | excel vba date variable | excel vba single loop | excel vba loop | excel vba double loop | excel vba triple loop | excel vba do while loop | excel vba for each next loop | excel vba range | excel vba range examples | declare excel vba range | select excel vba range | excel vba range columns | excel vba range rows | excel vba range offset | excel vba range copy paste | clear excel vba range | count excel vba range | excel vba range currentregion | resize excel vba range | excel vba range font | excel vba array | excel vba array one-dimensional | excel vba array two-dimensional | excel vba array redim | excel vba array size | excel vba userform | excel vba userform example | excel vba userform controls | excel vba userform show | excel vba userform initialize | excel vba userform macros | test excel vba userform