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.