Excel VBA (Visual Basic for Applications) is the name of the
programming language of Excel.
1 Create a
Macro: With Excel VBA you can automate tasks in Excel by writing so
called macros. In this chapter, learn how to create a simple macro.
To
turn on the Developter tab, execute the following steps.
1.
Right click anywhere on the ribbon, and then click Customize the Ribbon.
2.
Under Customize the Ribbon, on the right side of the dialog box, select Main
tabs (if necessary).
3.
Check the Developer check box.
4.
Click OK.
5.
You can find the Developer tab next to the View tab.
To
place a command button on your worksheet, execute the following steps.
1.
On the Developer tab, click Insert.
2.
In the ActiveX Controls group, click Command Button.
3.
Drag a command button on your worksheet.
To
assign a macro (one or more code lines) to the command button, execute the
following steps.
1.
Right click CommandButton1 (make sure Design Mode is selected).
2.
Click View Code.
The
Visual Basic Editor appears.
3.
Place your cursor between Private Sub CommandButton1_Click() and End Sub.
4.
Add the code line shown below.
Note:
the window on the left with the names Sheet1, Sheet2 and Sheet3 is called the
Project Explorer. If the Project Explorer is not visible, click View, Project
Explorer. To add the Code window for the first sheet, click Sheet1 (Sheet1).
5.
Close the Visual Basic Editor.
6.
Click the command button on the sheet (make sure Design Mode is deselected).
Result:
Congratulations.
You've just created a macro in Excel!
To
open the Visual Basic Editor, on the Developer tab,
click Visual Basic.
The
Visual Basic Editor appears.
2 MsgBox: The
MsgBox is a dialog box in Excel VBA you can use to inform the users of your
program.
1.
A simple message.
MsgBox "This is fun"
Result
when you click the command button on the sheet:
2.
A little more advanced message. First, enter a number into cell A1.
MsgBox "Entered value is " &
Range("A1").Value
Result
when you click the command button on the sheet:
Note:
we used the & operator to concatenate (join) two strings. Although Range("A1").value
is not a string, it works here.
3.
To start a new line in a message, use vbNewLine.
MsgBox "Line 1" & vbNewLine & "Line
2"
Result
when you click the command button on the sheet:
3 Workbook and
Worksheet Object: Learn more about the Workbook and Worksheet object in Excel
VBA.
Object Hierarchy
In
Excel VBA, an object can contain another object, and that object can contain
another object, etc. In other words, Excel VBA programming involves working
with an object hierarchy. This probably sounds quite confusing, but we will
make it clear.
The
mother of all objects is Excel itself. We call it the Application object. The
application object contains other objects. For example, the Workbook object
(Excel file). This can be any workbook you have created. The Workbook object
contains other objects, such as the Worksheet object. The Worksheet object
contains other objects, such as the Range object.
The Create a Macro chapter illustrates how to run code by clicking on a
command button. We used the following code line:
Range("A1").Value = "Hello"
but
what we really meant was:
Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value
= "Hello"
Note:
the objects are connected with a dot. Fortunately, we do not have to add a code
line this way. That is because we placed our command button in
create-a-macro.xls, on the first worksheet. Be aware that if you want to change
different things on different worksheets to include the Worksheet object. Read
on.
You
may have noticed that Workbooks and Worksheets are both plural. That is because
they are collections. The Workbooks collection contains all the Workbook
objects that are currently open. The Worksheets collection contains all the
Worksheet objects in a workbook.
You
can refer to a member of the collection, for example, a single Worksheet
object, in three ways.
1.
Using the worksheet name.
Worksheets("Sales").Range("A1").Value =
"Hello"
2.
Using the index number (1 is the first worksheet starting from the left).
Worksheets(1).Range("A1").Value = "Hello"
3.
Using the CodeName.
Sheet1.Range("A1").Value = "Hello"
To
see the CodeName of a worksheet, open the Visual Basic Editor. In the Project Explorer, the first name is the CodeName. The
second name is the worksheet name (Sales).
Note:
the CodeName remains the same if you change the worksheet name or the order of
your worksheets so this is the safest way to reference a worksheet. Click View,
Properties Window to change the CodeName of a worksheet. There is one
disadvantage, you cannot use the CodeName if you reference a worksheet in a
different workbook.
Now
let's take a look at some properties and methods of the Workbooks and
Worksheets collection. Properties are something which an collection has
(they describe the collection), while methods do something (they
perform an action with an collection).
Place
a command button on your worksheet and add the code
lines:
1.
The Add method of the Workbooks collection creates a new workbook.
Workbooks.Add
Note:
the Add method of the Worksheets collection creates a new worksheet.
2.
The Count property of the Worksheets collection counts the number of worksheets
in a workbook.
MsgBox Worksheets.Count
Result
when you click the command button on the sheet:
Note:
the Count property of the Workbooks collection counts the number of active
workbooks.
4 Range Object:
The Range object, which is the representation of a cell (or cells) on your
worksheet, is the most important object of Excel VBA.
Range Examples
Place
a command button on your worksheet and add the following
code line:
Range("B3").Value = 2
Result
when you click the command button on the sheet:
Code:
Range("A1:A4").Value = 5
Result:
Code:
Range("A1:A2,B3:C4").Value = 10
Result:
Instead
of Range, you can also use Cells. Using Cells is particularly useful when
you want to loop through ranges.
Code:
Cells(3, 2).Value = 2
Result:
Explanation:
Excel VBA enters the value 2 into the cell at the intersection of row 3 and
column 2.
Code:
Range(Cells(1, 1), Cells(4, 1)) = 5
Result:
You
can declare a Range object by using the keywords Dim and Set.
Code:
Dim example As Range
Set example = Range("A1:C4")
example.Value = 8
Set example = Range("A1:C4")
example.Value = 8
Result:
An
important method of the Range object is the Select method. The Select method
simply selects a range.
Code:
Dim example As Range
Set example = Range("A1:C4")
example.Select
Set example = Range("A1:C4")
example.Select
Result:
The
Rows property gives access to a specific row of a range.
Code:
Dim example As Range
Set example = Range("A1:C4")
example.Rows(3).Select
Set example = Range("A1:C4")
example.Rows(3).Select
Result:
Note:
border for illustration only.
The
Columns property gives access to a specific column of a range.
Code:
Dim example As Range
Set example = Range("A1:C4")
example.Columns(2).Select
Set example = Range("A1:C4")
example.Columns(2).Select
Result:
Note:
border for illustration only.
The
Copy and Paste method are used to copy a range and to paste it somewhere else
on the worksheet.
Code:
Range("A1:A2").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Result:
Although
this is allowed in Excel VBA, it is much better to use the code line below
which does exactly the same.
Range("C3:C4").Value = Range("A1:A2").Value
To
clear the content of an Excel range, you can use the ClearContents method.
Range("A1").ClearContents
or
simply use:
Range("A1").Value = ""
Note:
use the Clear method to clear the content and format of a range. Use the
ClearFormats method to clear the format only.
With
the Count property, you can count the number of cells, rows and columns of a
range.
Note:
border for illustration only.
Code:
Dim example As Range
Set example = Range("A1:C4")
MsgBox example.Count
Set example = Range("A1:C4")
MsgBox example.Count
Result:
Code:
Dim example As Range
Set example = Range("A1:C4")
MsgBox example.Rows.Count
Set example = Range("A1:C4")
MsgBox example.Rows.Count
Result:
Note:
in a similar way, you can count the number of columns of a range.
5 Variables: This chapter
teaches you how to declare, initialize and display a variable in
Excel VBA.
Integer
Integer
variables are used to store whole numbers.
Dim x As Integer
x = 6
Range("A1").Value = x
x = 6
Range("A1").Value = x
Result:
Explanation:
the first code line declares a variable with name x of type Integer. Next, we
initialize x with value 6. Finally, we write the value of x to cell A1.
String
variables are used to store text.
Code:
Dim book As String
book = "bible"
Range("A1").Value = book
book = "bible"
Range("A1").Value = book
Result:
Explanation:
the first code line declares a variable with name book of type String. Next, we
initialize book with the text bible. Always use apostrophes to initialize
String variables. Finally, we write the text of the variable book to cell A1.
A
variable of type Double is more accurate than a variable of type Integer and
can also store numbers after the comma.
Code:
Dim x As Integer
x = 5.5
MsgBox "value is " & x
x = 5.5
MsgBox "value is " & x
Result:
But
that is not the right value! We initialized the variable with value 5.5 and we
get the value 6. What we need is a variable of type Double.
Code:
Dim x As Double
x = 5.5
MsgBox "value is " & x
x = 5.5
MsgBox "value is " & x
Result:
Note:
Long variables have even larger capacity. Always use variables of the right
type. As a result, errors are easier to find and your code will run faster.
Use
a Boolean variable to hold the value True or False.
Code:
Dim continue As Boolean
continue = True
If continue = True Then MsgBox "Boolean variables are cool"
continue = True
If continue = True Then MsgBox "Boolean variables are cool"
Result:
Explanation:
the first code line declares a variable with name continue of type Boolean.
Next, we initialize continue with the value True. Finally, we use the Boolean
variable to only display a MsgBox if the variable holds the value True.
6 If Then
Statement: Use the If Then statement in Excel VBA to execute code lines
if a specific condition is met.
If Then Statement
Place
a command button on your worksheet and add the following
code lines:
Dim score As Integer, result As String
score = Range("A1").Value
If score >= 60 Then result = "pass"
Range("B1").Value = result
score = Range("A1").Value
If score >= 60 Then result = "pass"
Range("B1").Value = result
Explanation:
if score is greater than or equal to 60, Excel VBA returns pass.
Result
when you click the command button on the sheet:
Note:
if score is less than 60, Excel VBA places the value of the empty variable
result into cell B1.
Place
a command button on your worksheet and add the following
code lines:
Dim score As Integer, result As String
score = Range("A1").Value
If score >= 60 Then
result = "pass"
Else
result = "fail"
End If
Range("B1").Value = result
score = Range("A1").Value
If score >= 60 Then
result = "pass"
Else
result = "fail"
End If
Range("B1").Value = result
Explanation:
if score is greater than or equal to 60, Excel VBA returns pass, else Excel VBA
returns fail.
Result
when you click the command button on the sheet:
Note:
only if you have one code line after Then and no Else statement, it is allowed
to place a code line directly after Then and to omit (leave out) End If (first
example). Otherwise start a new line after the words Then and Else and end with
End If (second example).
7 Loop:
Looping is one of the most powerful programming techniques. A loop in Excel VBA
enables you to loop through a range of cells with just a few codes lines.
Single Loop
You
can use a single loop to loop through a one-dimensional range of cells.
Place
a command button on your worksheet and add the following
code lines:
Dim i As Integer
For i = 1 To 6
Cells(i, 1).Value = 100
Next i
For i = 1 To 6
Cells(i, 1).Value = 100
Next i
Result
when you click the command button on the sheet:
Explanation:
The code lines between For and Next will be executed six times. For i = 1,
Excel VBA enters the value 100 into the cell at the intersection of row 1 and
column 1. When Excel VBA reaches Next i, it increases i with 1 and jumps back
to the For statement. For i = 2, Excel VBA enters the value 100 into the cell at
the intersection of row 2 and column 1, etc.
Note:
it is good practice to always indent (tab) the code between the words For and
Next. This makes your code easier to read.
You
can use a double loop to loop through a two-dimensional range of cells.
Place
a command button on your worksheet and add the following
code lines:
Dim i As Integer, j As Integer
For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100
Next j
Next i
For i = 1 To 6
For j = 1 To 2
Cells(i, j).Value = 100
Next j
Next i
Result
when you click the command button on the sheet:
Explanation:
For i = 1 and j = 1, Excel VBA enters the value 100 into the cell at the
intersection of row 1 and column 1. When Excel VBA reaches Next j, it increases
j with 1 and jumps back to the For j statement. For i = 1 and j = 2, Excel VBA
enters the value 100 into the cell at the intersection of row 1 and column 2.
Next, Excel VBA ignores Next j because j only runs from 1 to 2. When Excel VBA
reaches Next i, it increases i with 1 and jumps back to the For i statement.
For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the
intersection of row 2 and column 1, etc.
You
can use a triple loop to loop through two-dimensional ranges on multiple Excel
worksheets.
Place
a command button on your worksheet and add the following
code lines:
Dim c As Integer, i As Integer, j As Integer
For c = 1 To 3
For i = 1 To 6
For j = 1 To 2
Worksheets(c).Cells(i, j).Value = 100
Next j
Next i
Next c
For c = 1 To 3
For i = 1 To 6
For j = 1 To 2
Worksheets(c).Cells(i, j).Value = 100
Next j
Next i
Next c
Explanation:
The only change made compared to the code for the double loop is that we
have added one more loop and added Worksheets(c). in front of Cells to get the
two-dimensional range on the first sheet for c = 1, the second sheet for c = 2
and the third sheet for c = 3. Download the Excel file to see this result.
Besides
the For Next loop, there are other loops in Excel VBA. For example, the Do
While Loop. Code placed between Do While and Loop will be repeated as long as
the part after Do While is true.
1.
Place a command button on your worksheet and add the following
code lines:
Dim i As Integer
i = 1
Do While i < 6
Cells(i, 1).Value = 20
i = i + 1
Loop
i = 1
Do While i < 6
Cells(i, 1).Value = 20
i = i + 1
Loop
Result
when you click the command button on the sheet:
Explanation:
as long as i is lower than 6, Excel VBA enters the value 20 into the cell at
the intersection of row i and column 1 and increments i by 1. In Excel VBA (and
in other programming languages), the symbol '=' means becomes. It does not mean
equal. So i = i + 1 means i becomes i + 1. In other words: take the present
value of i and add 1 to it. For example, if i = 1, i becomes 1 + 1 = 2. As
a result, the value 20 will be placed into column A five times (not six because
Excel VBA stops when i equals 6).
2.
Enter some numbers in column A.
3.
Place a command button on your worksheet and add the following
code lines:
Dim i As Integer
i = 1
Do While Cells(i, 1).Value <> ""
Cells(i, 2).Value = Cells(i, 1).Value + 10
i = i + 1
Loop
i = 1
Do While Cells(i, 1).Value <> ""
Cells(i, 2).Value = Cells(i, 1).Value + 10
i = i + 1
Loop
Result
when you click the command button on the sheet:
Explanation:
as long as Cells(i, 1).Value is not empty (<> means not equal to), Excel
VBA enters the value into the cell at the intersection of row i and column 2,
that is 10 higher than the value in the cell at the intersection of row i and
column 1. Excel VBA stops when i equals 7 because Cells(7, 1).Value is empty.
This is a great way to loop through any number of rows on a worksheet.
8 Macro
Errors: This chapter teaches you how to deal with macro errors in
Excel.
Place
a command button on your worksheet and add the following
code lines:
x = 2
Range("A1").Valu = x
Range("A1").Valu = x
1.
Click the command button on the sheet.
Result:
2.
Click OK.
The
variable x is not defined. Because we are using the Option Explicit statement at the start of our code, we have to declare all
our variables. Excel VBA has colored the x blue to indicate the error.
3.
In the Visual Basic Editor, click Reset to stop the debugger.
4.
Correct the error by adding the following code line at the start of the code.
Dim x As Integer
You
may have heard of the technique called debugging before. With this
technique you can step through your code.
5.
In the Visual Basic Editor, place your cursor before Private and press F8.
The
first line turns yellow.
6.
Press F8 three more times.
The
following error appears.
The
Range object has a property called Value. Value isn't spelled correctly
here. Debugging is a great way to not only find errors, but also understand
code better. Our Debugging example program shows you how to single
step through your code and see the effect of each code line on your worksheet.
9 String
Manipulation: In this chapter you find the most important functions to manipulate
strings in Excel VBA.
Place
a command button on your worksheet and add the code lines
below. To execute the code lines, click the command button on the sheet.
We
use the & operator to concatenate (join) strings.
Code:
Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"
MsgBox text1 & " " & text2
text1 = "Hi"
text2 = "Tim"
MsgBox text1 & " " & text2
Result:
Note:
to insert a space, use " "
To
extract the leftmost characters from a string, use Left.
Code:
Dim text As String
text = "example text"
MsgBox Left(text, 4)
text = "example text"
MsgBox Left(text, 4)
Result:
To
extract the rightmost characters from a string, use Right. We can also directly
insert text in a function.
Code:
MsgBox Right("example text", 2)
Result:
To
extract a substring, starting in the middle of a string, use Mid.
Code:
MsgBox Mid("example text", 9, 2)
Result:
Note:
started at position 9 (t) with length 2. You can omit the third argument if you
want to extract a substring starting in the middle of a string, until the end
of the string.
To
get the length of a string, use Len.
Code:
MsgBox Len("example text")
Result:
Note:
space (position 8) included!
To
find the position of a substring in a string, use Instr.
Code:
MsgBox Instr("example text", "am")
Result:
Note:
string "am" found at position 3.
10 Date and
Time: Learn how to work with dates and times in Excel VBA.
Place
a command button on your worksheet and add the code lines
below. To execute the code lines, click the command button on the sheet.
The
following macro gets the year of a date. To declare a date, use the Dim
statement. To initialize a date, use the DateValue function.
Code:
Dim exampleDate As Date
exampleDate = DateValue("Jun 19, 2010")
MsgBox Year(exampleDate)
exampleDate = DateValue("Jun 19, 2010")
MsgBox Year(exampleDate)
Result:
Note:
Use Month and Day to get the month and day of a date.
To
add a number of days to a date, use the DateAdd function. The DateAdd function
has three arguments. Fill in "d" for the first argument to add days.
Fill in 3 for the second argument to add 3 days. The third argument represents
the date to which the number of days will be added.
Code:
Dim firstDate As Date, secondDate As Date
firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)
MsgBox secondDate
firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)
MsgBox secondDate
Result:
Note:
Change "d" to "m" to add a number of months to a date.
Place your cursor on DateAdd in the Visual Basic Editor and click F1 for help on
the other interval specifiers. Dates are in US Format. Months first, Days
second. This type of format depends on your windows regional settings.
To
get the current date and time, use the Now function.
Code:
MsgBox Now
Result:
The
get the hour of a time, use the Hour function.
Code:
MsgBox Hour(Now)
Result:
Note:
Use Minute and Second to get the minute and second of a time.
The
TimeValue function converts a string to a time serial number. The time's serial
number is a number between 0 and 1. For example, noon (halfway through the day)
is represented as 0.5.
Code:
MsgBox TimeValue("9:20:01 am")
Result:
Now,
to clearly see that Excel handles times internally as numbers between 0 and 1,
add the following code lines:
Dim y As Double
y = TimeValue("09:20:01")
MsgBox y
y = TimeValue("09:20:01")
MsgBox y
Result:
11 Events: Events
are actions performed by users which trigger Excel VBA to execute code.
Workbook Open Event
Code
added to the Workbook Open Event will be executed by Excel VBA when you open
the workbook.
1.
Open the Visual Basic Editor.
2.
Double click on This Workbook in the Project Explorer.
3.
Choose Workbook from the left drop-down list. Choose Open from the right
drop-down list.
4.
Add the following code line to the Workbook Open Event:
MsgBox "Good Morning"
5.
Save, close and reopen the Excel file.
Result:
Code
added to the Worksheet Change Event will be executed by Excel VBA when you
change a cell on a worksheet.
1.
Open the Visual Basic Editor.
2.
Double click on a sheet (for example Sheet1) in the Project Explorer.
3.
Choose Worksheet from the left drop-down list. Choose Change from the right
drop-down list.
Add
the following code lines to the Worksheet Change Event:
4.
The Worksheet Change Event listens to all changes on Sheet1. We only want Excel
VBA to do something if something changes in cell B2. To achieve this, add the
following code lines:
If Target.Address =
"$B$2" Then
End If
End If
5.
We only want Excel VBA to show a MsgBox if the user enters a value greater than
80. To achieve this, add the following code line between If and End If.
If Target.Value
> 80 Then MsgBox "Goal Completed"
6.
On Sheet1, enter a number greater than 80 into cell B2.
Result:
12 Array: An
array is a group of variables. In Excel VBA, you can refer to a specific
variable (element) of an array by using the array name and the index
number.
One-dimensional Array
To
create a one-dimensional array, execute the following steps.
Place
a command button on your worksheet and add the following
code lines:
Dim Films(1 To 5) As String
Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"
MsgBox Films(4)
Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"
MsgBox Films(4)
Result
when you click the command button on the sheet:
Explanation:
the first code line declares a String array with name Films. The array consists
of five elements. Next, we initialize each element of the array. Finally, we
display the fourth element using a MsgBox.
To
create a two-dimensional array, execute the following steps. This time we are
going to read the names from the sheet.
Place
a command button on your worksheet and add the following
code lines:
Dim Films(1 To 5, 1 To 2) As String
Dim i As Integer, j As Integer
For i = 1 To 5
For j = 1 To 2
Films(i, j) = Cells(i, j).Value
Next j
Next i
MsgBox Films(4, 2)
Dim i As Integer, j As Integer
For i = 1 To 5
For j = 1 To 2
Films(i, j) = Cells(i, j).Value
Next j
Next i
MsgBox Films(4, 2)
Result
when you click the command button on the sheet:
Explanation:
the first code line declares a String array with name Films. The array has two
dimensions. It consists of 5 rows and 2 columns. Tip: rows go first, then
columns. The other two variables of type Integer are used for the Double Loop to initialize each element of the array. Finally, we
display the element at the intersection of row 4 and column 2.
13 Function
and Sub: In Excel VBA, a function can return a value while a sub
cannot.
Function
If
you want Excel VBA to perform a task that returns a result, you can use a
function. Place a function into a module (In the Visual Basic Editor, click
Insert, Module). For example, the function with name Area.
Function Area(x As Double, y As Double) As Double
Area = x * y
End Function
Area = x * y
End Function
Explanation:
This function has two arguments (of type Double) and a return type (the part
after As also of type Double). You can use the name of the function (Area) in
your code to indicate which result you want to return (here x * y).
You
can now refer to this function (in other words call the function) from
somewhere else in your code by simply using the name of the function and giving
a value for each argument.
Place
a command button on your worksheet and add the following
code lines:
Dim z As Double
z = Area(3, 5) + 2
MsgBox z
z = Area(3, 5) + 2
MsgBox z
Explanation:
The function returns a value so you have to 'catch' this value in your code. You
can use another variable (z) for this. Next, you can add another value to this
variable (if you want). Finally, display the value using a MsgBox.
Result
when you click the command button on the sheet:
If
you want Excel VBA to perform some actions, you can use a sub. Place a sub into
a module (In the Visual Basic Editor, click Insert, Module). For example, the
sub with name Area.
Sub Area(x As Double, y As Double)
MsgBox x * y
End Sub
MsgBox x * y
End Sub
Explanation:
This sub has two arguments (of type Double). It does not have a return type!
You can refer to this sub (call the sub) from somewhere else in your code by
simply using the name of the sub and giving a value for each argument.
Place
a command button on your worksheet and add the following
code line:
Area 3, 5
Result
when you click the command button on the sheet:
Can
you see the difference between the function and the sub? The function returned
the value 15. We added the value 2 to this result and displayed the final
result. When we called the sub we had no more control over the result (15)
because a sub cannot return a value!
14 Application
Object: The mother of all objects is Excel itself. We call it the
Application object. The application object gives access to a lot of Excel
related options.
WorksheetFunction
You
can use the WorksheetFunction property in Excel VBA to access Excel
functions.
1.
For example, place a command button on your worksheet and add the following
code line:
Range("A3").Value =
Application.WorksheetFunction.Average(Range("A1:A2"))
When
you click the command button on the worksheet, Excel VBA calculates the average
of the values in cell A1 and cell A2 and places the result into cell A3.
Note:
instead of Application.WorksheetFunction.Average, simply use
WorksheetFunction.Average. If you look at the formula bar, you can see that the
formula itself is not inserted into cell A3. To insert the formula itself into
cell A3, use the following code line:
Range("A3").Value = "=AVERAGE(A1:A2)"
Sometimes
you may find it useful to disable screen updating (to avoid flickering) while
executing code. As a result, your code will run faster.
1.
For example, place a command button on your worksheet and add the following
code lines:
Dim i As Integer
For i = 1 To 10000
Range("A1").Value = i
Next i
For i = 1 To 10000
Range("A1").Value = i
Next i
When
you click the command button on the worksheet, Excel VBA displays each value a
tiny fraction of a second and this can take some time.
2.
To speed up the process, update the code as follows.
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 10000
Range("A1").Value = i
Next i
Application.ScreenUpdating = True
Application.ScreenUpdating = False
For i = 1 To 10000
Range("A1").Value = i
Next i
Application.ScreenUpdating = True
As
a result, your code will run much faster and you will only see the end result
(10000).
You
can instruct Excel VBA not to display alerts while executing code.
1.
For example, place a command button on your worksheet and add the following
code line:
ActiveWorkbook.Close
When
you click the command button on the worksheet, Excel VBA closes your Excel file
and asks you to save the changes you made.
2.
To instruct Excel VBA not to display this alert while executing code, update
the code as follows.
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
ActiveWorkbook.Close
Application.DisplayAlerts = True
As
a result, Excel VBA closes your Excel file, without asking you to save the
changes you made. Any changes are lost.
By
default, calculation is set to automatic. As a result, Excel recalculates
the workbook automatically each time a value affecting a formula changes. If
your workbook contains many complex formulas, you can speed up your macro by
setting calculation to manual.
1.
For example, place a command button on your worksheet and add the following
code line:
Application.Calculation = xlCalculationManual
When
you click the command button on the worksheet, Excel VBA sets calculation to
manual.
2.
You can verify this by clicking on File, Options, Formulas.
3.
Now when you change the value of cell A1, the value of cell B1 is not
recalculated.
You
can manually recalculate the workbook by pressing F9.
4.
In most situations, you will set calculation to automatic again at the end of
your code. Simply add the following code line to achieve this.
Application.Calculation = xlCalculationAutomatic
15 ActiveX
Controls: Learn how to create ActiveX controls such as command buttons,
text boxes, list boxes etc.
1. On the Developer
tab, click Insert.
2. For example, in the ActiveX Controls group, click Command
Button to insert a command button control.
3. Drag a command button on your worksheet.
4. Right click the command button (make sure Design Mode is
selected).
5. Click View Code.
Note: you can change the caption and name of a control by right
clicking on the control (make sure Design Mode is selected) and then clicking
on Properties. Change the caption of the command button to 'Apply Blue Text
Color'. For now, we will leave CommandButton1 as the name of the command
button.
The Visual Basic Editor appears.
6. Add the code line shown below between Private Sub
CommandButton1_Click() and End Sub.
7. Select the range B2:B4 and click the command button (make
sure Design Mode is deselected).
Result:
16 Userform: This chapter
teaches you how to create an Excel VBA Userform.
This
chapter teaches you how to create an Excel VBA Userform. The Userform we
are going to create looks as follows:
To
add the controls to the Userform, execute the following steps.
1.
Open the Visual Basic Editor. If the Project Explorer is not visible,
click View, Project Explorer.
2.
Click Insert, Userform. If the Toolbox does not appear automatically, click
View, Toolbox. Your screen should be set up as below.
3.
Add the controls listed in the table below. Once this has been completed, the
result should be consistent with the picture of the Userform shown earlier. For
example, create a text box control by clicking on TextBox from the Toolbox.
Next, you can drag a text box on the Userform. When you arrive at the Car
frame, remember to draw this frame first before you place the two option
buttons in it.
4.
Change the names and captions of the controls according to the table below.
Names are used in the Excel VBA code. Captions are those that appear on your
screen. It is good practice to change the names of controls. This will make
your code easier to read. To change the names and captions of the controls,
click View, Properties Window and click on each control.
Control
|
Name
|
Caption
|
Userform
|
DinnerPlannerUserForm
|
Dinner Planner
|
Text Box
|
NameTextBox
|
|
Text Box
|
PhoneTextBox
|
|
List Box
|
CityListBox
|
|
Combo Box
|
DinnerComboBox
|
|
Check Box
|
DateCheckBox1
|
June 13th
|
Check Box
|
DateCheckBox2
|
June 20th
|
Check Box
|
DateCheckBox3
|
June 27th
|
Frame
|
CarFrame
|
Car
|
Option Button
|
CarOptionButton1
|
Yes
|
Option Button
|
CarOptionButton2
|
No
|
Text Box
|
MoneyTextBox
|
|
Spin Button
|
MoneySpinButton
|
|
Command Button
|
OKButton
|
OK
|
Command Button
|
ClearButton
|
Clear
|
Command Button
|
CancelButton
|
Cancel
|
7 Labels
|
No need to change
|
Name:, Phone
Number:, etc.
|
Note:
a combo box is a drop-down list from where a user can select an item or fill in
his/her own choice. Only one of the option buttons can be selected.
To
show the Userform, place a command
button on your
worksheet and add the following code line:
Private Sub CommandButton1_Click()
DinnerPlannerUserForm.Show
End Sub
DinnerPlannerUserForm.Show
End Sub
We
are now going to create the Sub UserForm_Initialize. When you use the Show
method for the Userform, this sub will automatically be executed.
1.
Open the Visual Basic Editor.
2.
In the Project Explorer, right click on DinnerPlannerUserForm and then click
View Code.
3.
Choose Userform from the left drop-down list. Choose Initialize from the right
drop-down list.
4.
Add the following code lines:
Private Sub UserForm_Initialize()
'Empty NameTextBox
NameTextBox.Value = ""
'Empty PhoneTextBox
PhoneTextBox.Value = ""
'Empty CityListBox
CityListBox.Clear
'Fill CityListBox
With CityListBox
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With
'Empty DinnerComboBox
DinnerComboBox.Clear
'Fill DinnerComboBox
With DinnerComboBox
.AddItem "Italian"
.AddItem "Chinese"
.AddItem "Frites and Meat"
End With
'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False
'Set no car as default
CarOptionButton2.Value = True
'Empty MoneyTextBox
MoneyTextBox.Value = ""
'Set Focus on NameTextBox
NameTextBox.SetFocus
End Sub
'Empty NameTextBox
NameTextBox.Value = ""
'Empty PhoneTextBox
PhoneTextBox.Value = ""
'Empty CityListBox
CityListBox.Clear
'Fill CityListBox
With CityListBox
.AddItem "San Francisco"
.AddItem "Oakland"
.AddItem "Richmond"
End With
'Empty DinnerComboBox
DinnerComboBox.Clear
'Fill DinnerComboBox
With DinnerComboBox
.AddItem "Italian"
.AddItem "Chinese"
.AddItem "Frites and Meat"
End With
'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False
'Set no car as default
CarOptionButton2.Value = True
'Empty MoneyTextBox
MoneyTextBox.Value = ""
'Set Focus on NameTextBox
NameTextBox.SetFocus
End Sub
Explanation:
text boxes are emptied, list boxes and combo boxes are filled, check boxes are
unchecked, etc.
We
have now created the first part of the Userform. Although it looks neat already,
nothing will happen yet when we click the command buttons on the Userform.
1.
Open the Visual Basic Editor.
2.
In the Project Explorer, double click on DinnerPlannerUserForm.
3.
Double click on the Money spin button.
4.
Add the following code line:
Private Sub MoneySpinButton_Change()
MoneyTextBox.Text = MoneySpinButton.Value
End Sub
MoneyTextBox.Text = MoneySpinButton.Value
End Sub
Explanation:
this code lines updates the text box when you use the spin button.
5.
Double click on the OK button.
6.
Add the following code lines:
Private Sub OKButton_Click()
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value
If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption
If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption
If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption
If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
Cells(emptyRow, 7).Value = MoneyTextBox.Value
End Sub
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value
If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption
If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption
If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption
If CarOptionButton1.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
Cells(emptyRow, 7).Value = MoneyTextBox.Value
End Sub
Explanation:
first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow
is the first empty row and increases every time a record is added. Finally, we
transfer the information from the Userform to the specific columns of emptyRow.
7.
Double click on the Clear button.
8.
Add the following code line:
Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
Call UserForm_Initialize
End Sub
Explanation:
this code line calls the Sub UserForm_Initialize when you click on
the Clear button.
9.
Double click on the Cancel Button.
10.
Add the following code line:
Private Sub CancelButton_Click()
Unload Me
End Sub
Unload Me
End Sub
Explanation:
this code line closes the Userform when you click on the Cancel button.
Exit
the Visual Basic Editor, enter the labels shown below into row 1 and test the
Userform.
Result:
Anda baru saja membaca artikel yang berkategori Excel
dengan judul Excel VBA Programing. Jika kamu suka, jangan lupa like dan bagikan keteman-temanmu ya... By : o s m
Ditulis oleh:
Alfonsius Karno - Thursday, April 24, 2014
