These training courses will let you discover the capabilities of Excel Macros applied to everyday repetitive tasks so you can improve your productivity.
to declare a variable, you have to do it like this:
dim Variable_Name as Type_of_Variable
"Dim" followed by a name for the variable, then "as" followed by the variable type. you can declare as many variables as you want. Instead of declaring each variable on its own line, you can declare more than one variable on the same line. To do this, use one Dim keyword and separate the names of variables with commas. Some of the variable types are:
Integer Will hold a number that ranges from -32768 to 32767 Instead of using As Integer, you can use the % type character.
Dim Tracks%
Double If you want to use a decimal number that requires a good deal of precision, declare a variable using the Double data type
Boolean
Will hold a True or False value
String A string is a character or a combination of characters that constitute text of any kind and almost any length. To declare a string variable, use the String data type. There are many other variable types that we can go though in next posts.
In this tutorial we will learn how to create processes with
VBA (Visual BASIC for Applications).
The objective is to create a button that displays a message
for the user.
To create our program go to the Developer tab and click the
Visual Basic button
You will probably see an empty gray window like this one:
In case you don’t have the Project Explorer or properties
window open, go to menu bar and click “View”.
Then activate both windows:
Go to the Project Explorer and click on the “+” next to your
file to expand. This will display all your sheets modules and one for the
Workbook.
Now go to the second icon from left to right of the top left
corner (the one with the rulers) and insert a new module:
A new white page will pop out.
Just for your reference, there are two ways to define
routines in visual BASIC. The first one is with “Function” and the other one is
with “Sub”. They work almost the same; the only difference is that the function
is used to return a value to another function or process, while Subs can be
used to update a cell or perform a calculation, but the result can’t be
returned to another sub or function.
In this tutorial we will use a Sub routine.
Type down the following:
sub Test1
And click enter.
You will see that excel automatically adds “()” and closes the
sub routine with an “End Sub”.
Between these statements we have to write all the code for
our process.
The instruction to display a message box is “MsgBox”
followed by a space and the message we want to display between double quotes. For
example:
MsgBox “Hello World!”
In the end the code will look like this:
Save the file.
** Note : save the
file as a macro-Enabled workbook (*.xlsm)
Now we need to insert a Form Control Button from the
Developer tab as shown below:
Click and drag anywhere on the sheet to place the button and
a new window will appear
Just select the macro we just created and click the OK
button.
Now click the button and you’ll see the message box that
pops out with “Hello World!”
When we perform repetitive tasks in a workbook, the best
thing we can do is to record a macro so we can do our task much faster. In this
tutorial, we will record a macro that applies conditional formatting to a table of student grades.
Imagine we have multiple sheets with student grades, and we
want to mark in red all grades below 8.
We can go page by page formatting all cells, or we can do it
just once and record a macro to do it faster.
1
The first step is to select the grades range:
2
Now on the developer tab we can find the option “Record
Macro” on the upper left corner.
Click the button and a new window will pop out asking you to
input the following information:
Please note that you can create a shortcut to activate the
macro (in this case I’ll use Ctrl+d). Now click OK and you will see that the “Record
Macro” icon changes to a blue square that says “Stop Recording”.
3
Now let’s go to our Home tab, and click Conditional
Formatting->Highlight Cells Rules->Less Than
4
Select to format cells that are less than 8, with a light
red fill and dark red text. Click the OK button.
5
Now go to the developer tab and click on the “Stop Recording”
button.
6
Your macro will be saved automatically. Now go to another
sheet, select the grades and press Ctrl+d .
It will set the same conditional formatting.
7
To view the code of your recorded macro, just go to the developer
tab, and click the Macros icon at the top left corner. A window with all your
saved macros will appear.
Select the macro we just created and click the Edit button.
The VBA code will appear on a new window, where you can
modify any command directly from there.
In this free course, you will learn the basics of recording
and programming excel macros, how to use buttons user forms, and other tools
that can help us achieve repetitive tasks in a very optimized and user friendly
way.
Note that I’m going to use Excel 2013 for these tutorials.
The first thing we have to do, is to setup excel so we can
visualize the developer tab. This is achieved by clicking on the top left
corner “File”
Then we’ll have to click on “Options”. A new window will pop out.
Finally we have to click (1)“customize Ribbon” and check the
(2)“Developer” tab.
When we click the OK button, we should now be able to see
the Developer tab in our excel file:
Now we are ready to start developing Excel Macros.
