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.

 

 

How can I add a new line IN A MESSAGE BOX?

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.

New line in a MsgBox

how can I get the sheet names of an Excel file?

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:

Get Sheetnames Name second sheet The third sheet name

How can I delay a macro?

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")

Delay a macro

How can I automatically run a macro?

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.

The Workbook_Open Event

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

how can I disable errors showing up in Excel vba?

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.

How can I select a range from the active cell to the last entry in a column or a row?

Example:

Select a range

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:

selected range

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

how can I manipulate text using Excel vba?

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

how can I halt an infinite loop?

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.

tags

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