Excel VBA By Index (1)
Table of Contents | Next
I have compile a list of Excel VBA code for beginner in an indexing format. More code will be added from time to time.
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Add comments to a Cell (back to top)
In order to add comment to a cell, you can use the AddComment method with the Range object. The macro below will show you how.
Sub AddCommentDemo ( )
Range("A1").AddComment "Prevent Global Warming"
Here the word "Prevent Global Warming" is added as a comment for cell A1. To display this comment all the time insert the following code..
Range("A1").Comment.Visible = True
Add Method (back to top)
Add a workbook and input a value in Range A1 of workbook created.
Sub AddWorkbook ()
sht1.Cells(1, 1) = "Welcome"
Adding Items to a ComboBox and ListBox
To specify a range reference in a style we are familiar with, like A1 or E5. The Excel VBA procedure below will find a cell that has formula content
and will display the address of this cell in a message box.
Sub AddressDemo ()
Set MyRange = Range("A1:Z1000")
For Each cell in MyRange
If cell.HasFormula = True Then
And Operator (back to top)
Perform a logical conjunction on two expression. In this case, two expression has to be satisfied in order for the macro to continuerunning. The macro below show how:
Sub AndDemo ()
x = 1
If x = 1 and y = 2 then
z = x * y
Areas Collection (back to top)
The Areas collection contains a few block of cells within a specific selection i.e. multiple ranges to create one multi-range. Let's look at an example...
The above macro use the Union function to combine 3 non-contiguous ranges. AreasInMyRange will return 3 as we use the Areas.Count method to calculate the 3 areas in myRange.
This method enable user to autofill a series of data on the specified range of cells. Look at the example below.
Sub AutofillDemo ( )
The above will autofills cells C1 through J1 using the source data in cells A1 and B1. If A1 contains 1 and
B1 contains 2, then this code will fill the destination cells with consecutive integers starting at 3 (in cell C1), 4 (in cell D1) and so on
Built -in Functions (back to top)
This code will use the Excel built-in function, Average, Max, Min and Standard Deviation.
Option Base 1
Sub BuiltInFunctionDemo ()
Dim MyArray(100) As Integer
For x = 1 to 100
MyArray(x) = Rnd
average = Application.Average(MyArray)
max = Application.Max(MyArray)
min = Application.Min(MyArray)
std = Application.Stdev(MyArray)
Calling a Worksheet Function from Visual Basic (back to top)
In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following procedure uses the Max worksheet function to determine the largest value in a range of cells¡
The range A1:B10 on Sheet1 is Set as myRange. Another variable,
Cells Method (back to top)
To enter the the value 100 into Range("B2"), the code below will do the trick...
ActiveSheet.Cells(2,2).Value = 100
We can also reference a specific cell in a Range object by using the code below...Here the value 100 is enter into Range("C4")
Set MyRange = Range("C3:C10")
MyRange.Cells(2).Value = 100
Change text to proper case
Change the name of a Worksheet
You can change the name of a worksheet by using the Name property of the Worksheet object. The macro below will show you how.
Sub ChangeNameDemo ( )
Dim wsName As String
wsName = InputBox("Enter a new worksheet name")
ActiveSheet.Name = wsName
This method enables you to clear the entire values, formulas and formatting from a range of cells. The procedure below use different Clear method..
Sub ClearDemo ( )
Range("A101:E200").ClearContents 'will clear the cell values and formulas from a range of cells except the formatting
Range("A201:E300").ClearFormats 'to clear the formatting
Range("A1:E100").Clear 'will clear the entire values, formulas and formatting from a range of cells
Copying a range (back to top)
This is how you write a simple copy and paste operation...
Sub CopyDemo ()
ActiveSheet.Range("A1:A3"). Copy (ActiveSheet.Range("B1:B3"))
Comparing two strings (back to top)
The Excel built-in function StrComp is use to compare whether two strings are alike. Let me show you how with the macro below.
Sub CompareString ( )
aStr = ActiveSheet.Range("A1").Value
bStr = ActiveSheet.Range("A2").Value
If StrComp(aStr,bStr) = 0 Then
MsgBox "They match"
MsgBox "They are not the same"
Create Chart Sheet
To create a chart sheet, we can use the Add method of the Charts collection to create and add a new chart sheet to the workbook. The
macro below will do the trick.
Sub CreateChartDemo ( )
Dim ch As Chart
Set ch = ThisWorkbook.charts.Add()
ch.Name = "Account"
Current Cell Content (back to top)
Disable the Ctrl + Break and Esc key (back to top)
In order for you to prevent user to stop a macro before it finish running by pressing the Ctrl + Break and Esc key, just insert the code below at
the top of your procedure...
Application.EnableCancelKey = False
The macro below show you how to display fullscreen using Excel VBA
Application.DisplayFullScreen = True
To exit full screen using VBA then
End Method (back to top)
You can use the End of the Range object to select a particular. See this example...
Sub EndDemo ()
Range(ActiveCell, ActiveCell.End(xlDown)).Select 'select downward the activecell to last non-empty cell
Range(ActiveCell, ActiveCell.End(xlUp)).Select 'select upward the activecell to last non-empty cell
Range(ActiveCell, ActiveCell.End(xlToLeft)).Select 'select to the left of the activecell to last non-empty cell
Range(ActiveCell, ActiveCell.End(xlToRight)).Select 'select to the right of the activecell to last non-empty cell
The default syntax of Excel VBA. This example demonstrates error handling by jumping to a label..
Sub ErrDemo ()
Dim x As Integer
On Error GoTo ErrorHandler
x = "abc"
e = Err.Number & " ...cannot assign integer value to x"
Exit Sub (back to top)
The Exit Sub statement is use as a point of exiting a subroutine with running the rest of any other statement in a procedure. The example below will display a message box to prompt the user whether to continue or not. If user choose yes, the subroutine will call the macro MyProcdure and will exit the current subroutine.
Sub ExitSubDemo ( )
msg = "Do you want to continue?"
answer = MsgBox(msg,vbYesNo)
If answer = vbYes Then
MsgBox "Program will end now."
You can use the Fill method to fills a range of cells. Let's see how this is implemented below.
Sub FillDemo ( )
Range("A2:A10").FillUp 'this will fill up the value contain in cell A10 to all cell above until A2
Range("A2:A10").FillDown 'this will fill down the value contain in cell A2 to all cell below until A10
For Next Loop (back to top)
Use this syntax when you want to execute for a determine number of time.
Sub ForNextDemo1 ()
For x = 1 to 10
y = x + 1
Here you'll get y = 11
Look at another example...The code below will for cell value that are positive to bold.
Sub ForNextDemo2 ()
Set MyRange = Range("A1:A100")
y = MyRange.Rows.Count ' y = 100
For x = 1 to y
If MyRange.Cells(x).Value > 0 Then
MyRange.Cells(x).Font.Bold = True
Or you can use the For Each - Next syntax to do the same thing.
Sub ForNextDemo3 ()
Set MyRange = Range("A1:A100")
For Each cell in MyRange
If cell.Value > 0 Then
cell.Font.Bold = True
GoTo (back to top)
When the GoTo syntax is use, you can make Excel VBA to jump to a label and execute the line of code under the label like the On Error GoTo example I've demonstrate above. Let's look at another example...
Sub GotoDemo ()
x = Int(Rnd() * (1 - 10) + 10) 'a random number between 1 and 10
If x < 5 Then
MsgBox x & " is less than 5"
MsgBox x & " is more than 5"
You can also specify a Excel macro to go to a specific range by using the GoTo method. For example, in a worksheet you have name a Range ("Credit Card"). To select this Range use the following statement...
Sub GoToDemo2 ()
Height (back to top)
To change the height userform, you can use the Height property. Look at the example below...
Private Sub UserForm_Initialize()
When UserForm1 initialize, it height will be set to 100 points.
To hide a range of column the macro below will do the trick.
Sub HideColDemo ( )
StartCol = 3
EndCol = 6
For n = StartCol to EndCol
Columns(n).Hidden = True
With the above procedure column C to F will be hidden
To hide a range of rows the macro below will do the trick.
Sub HideRowDemo ( )
StartRow = 3
EndRow = 6
For n = StartRow to EndRow
Rows(n).Hidden = True
With the above procedure Row 3 to 6 will be hidden
If you hide your sheets this way, users will not be able to unhide them using the Excel menus. Only using VB codes will be able to display the sheets again.
However, if you want to hide a userform different statement is required. The macro below will do the trick. If you have rename a userform to Account then the code is
Sub HideUserformDemo ()
Range(A1).Value = Account.TextBox1.Text
You still can access the value store in the userform programmatically. For example, you still can retrieve value on TextBox1 on the form.
Hyperlinks In Excel (back to top)
You may notice that Excel automatically convert URL that you enter into hyperlink. If you don't like this then you can delete the hyperlink
by entering the code below.
Here all the hyperlinks in sheet 2 will be deleted
If Then statement
This statement checks to see if a specific condition is true, and if true then it will execute all the code between the Then keyword and the End If statement. Look at the example below...
Sub IfThenDemo ( )
Dim x As Integer, MySum As Integer
For x = 1 to 100
If IsNumeric(Range("A" & x)) = True Then
MySum = MySum + Range("A" & x).Value
The above procedure will check whether the value in column A is numerical. If it is numerical, then it will add the cell value into MySum.
Input Box (back to top)
Input Box is used to prompt user for input. The example below will ask the use to enter the year he is born and then a message box will display his age.
MyInput = InputBox("Which year are you born?")
Age = 2007 - MyInput
Let's look at another example...
You can use the Input Box function to prompt specific user input during the execution of a subroutine. Look at the sample code below.
Sub InputBoxDemo2 ( )
UserInput = InputBox("Type something", ,"Enter")
A input box will be displayed asking the user to type something. The title of this input box is "Enter"
Inserting value into an existing cell that contain value
Here you can use the Insert method. For example, cell A1 contain the word "Credit". You can insert another word "Card" by using the
Sub InsertWordDemo ( )
You insert the word "Card" into cell A1 starting at character 8 with 4 character i.e "Card". As a result, cell A1 now contain the word "Credit Card".
Intersect (back to top)
You can specify the location where two ranges intersect by leaving a space between the two range definitions. For example, Range(A2:F5 D2:G6) specifies a range where the range of of cells A2 to F5 intersect with the range of cells D2 to G6.
Sub IntersectDemo ( )
Selection.Name = "Medicine"
Range("D2:F5") is the intersection, will be selected and is name "Medicine".
Invoke another macro on a change event
Automatically invoke a macro after entering data into a particular column. The example below will call the macro myProcedure when there is changes in Column 3. The code is insert into the Worksheet_Change () module.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 3 Then myProcedure
Inserting a Worksheet Function into a Cell (back to top)
To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. The following example insert the AVERAGE worksheet function into the property of range A1 on Sheet1 in the active workbook.
If / Then / Else
You use the If-Then structure when you want to execute one or more statements conditionally. The optional Else clause, enable you to execute one or more statements if the condition you¡¯re testing is false. See the example below...
Sub IfThenElseDemo ()
If Time < 0.5 Then
MsgBox "Good Morning"
MsgBox "Good Afternoon"
When Time is less than 0.5, it mean that it is before 12 pm.
IsNumeric (back to top)
This syntax enable to check a specified varaibles whether it is a numerical data. The subroutine below will check each cell in range A1:A100 whether it is a numerical value. If yes then it will be added to the total sum.
Sub IsNumericDemo ( )
Total = 0
For Each cell in Range("A1:A100")
If IsNumeric(cell.Value) Then
Total = Total + cell.Value