This is part two of a new series I’m writing, teaching you how to get more functionality out of your Excel. If you are not familiar with Macros I suggest you read the first part, before beginning on this one.
In Part Two…
In this part we are starting off using some of the commands you should know, and then create a Macro that decide filename, which folder to use, before exporting the document to PDF-format. I am also going to show you how to create a button to trigger the macro.
As in the first part, I am using Office 2010. Even though the code and programming are the same in older versions of Excel, the images and buttons may differ from what you see on your screen.
Enough Chit Chat, let’s get this party started…
Quick Introduction to VBA and Excel
As mentioned earlier (in the first Part) VBA is Visual Basic Light. The programming language is object-oriented, meaning we create code that relates to objects like Cells, Sheets and Workbooks. Each Object has many functions (or actions) and each function has settings we can use; like Object Name, Value, Id etc. Don’t worry about remembering them all, VBA has a built-in tool that show you a list of available settings for each function.
In addition to the objects – we are going to use what we call Variables. Variables temporarily store data we are going to use later (think of it as small post-it notes). To keep track of our variables we declare them (give them names). This is usually done in the Macro Header. We will get back to this later.
One last thing: In VBA we don’t use the name Macro. Instead we call it SUBS. The Sub has a begin-code and an end-code, which declare the Macro Name and End:
… Content Here …
Some of the functions we are going to use
Every function we use will trigger an event inside the Excel workbooks. When using Excel you maneuver within Cells and Sheets. Each time you Select a Cell it becomes Active. Instead of Clicking on a cell, we can tell the macro to do it for us. First, we tell the macro which Cell to activate, using the function RANGE (Cell reference enclosed in parentheses and quotes). Then we tell it what we want to do; “click it”. The code looks like this:
Range(“B2”).Select (or Range(“B2”).Activate)
To WRITE something in that Cell we have to use a different function: ActiveCell.
ActiveCell.Value =”Write this into the Cell”
To READ the contents of the Cell we need to use a variable. As mentioned earlier the variable has to be declared (given a name). Then we can write something in it. The code would look like this:
CellContent = ActiveCell.Value
Then we can use that information later on – even if the content of that cell should change.
To Move between workbooks or Sheets we use the functions called: Sheets or Workbook, like this:
Creating a Macro
Now we should be ready to create our PDF Macro. For this lesson I have created a document containing a simplified order form. You can download it at the end of this article. The workbook contain the macros used in these articles. The first macro has been created using the RECORD MACRO function.
To make the macro as versatile as possible we are going to create a new worksheet: “Settings”. This Sheet is going to store information like default filename, default save-to folder(s), and order handler. Later we can add functionality like Open After Save, Overwrite existing … etc.
This will allow other users to change settings without having to edit the macro.
- Rename your main sheet to “Form” (Right click the sheet tab, choose Rename).
- Create a new worksheet within your document, rename it: “Settings”.
- In the workbook add some Labels to describe what information to store, and fill in the default values. (See example).
- Return to the Form Sheet (The main sheet)
- To create a new Macro, Click on the Macro Icon. In the Text box, type in the name for the Macro: “CreatePDF” and Click the Create-Button.
In the VBA-Editor you will now see an empty Macro, looking like this:
All our code will go in-between these two lines. First we declare our post-it notes (variables):
Dim Name As String
Dim Stamp As String
Dim WhereTo As String
Dim sFileName As String
Now we need to retrieve data from the Settings Sheet, add the following lines:
WhereTo = ActiveCell.Value
Stamp = ActiveCell.Value
If Stamp = "" Then Stamp = Date
The Cell references B5 and B7 may differ from your settings sheet. Use the apropriate cell references.
Then we will retrieve the customer name from the form:
Name = ActiveCell.Value
Next, we put the data to good use, by creating the filename:
sFileName = WhereTo & Name & "_" & Stamp & ".pdf"
“sFileName”, “WhereTo”, “Name” and “Stamp” are the variables we use. The “=” sign means that the following information is to be stored. We link the information from each variable using the “&”-sign. Text is added between the quotes. The finished variable could look like this:
sFileName = "C:\Sales\Thomas_09092010.pdf".
Last Thing we do; add the Save To PDF Command:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sFileName, Quality _
:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
Okay, before we test that everything works, let’s make a button to trigger the macro. In order to do this (in Office 2010) we need to activate a hidden Tab on the ribbon (older Office, look for “Form Controls”).
- Click on the Green File Tab
- Choose Options
- In the Options Dialogue, click “Customize Ribbon”
- In the List Window to the right, there is a list of all the available Ribbons.
Locate the one Called: “Developer” and Check the box next to it
- Click OK to Save
In the pull down menu, choose the first element that looks like a little grey button. With the crosshatch pointer, draw a button somewhere in the Sheet. This will trigger the Form Button Event Menu.
Choose the Macro you would like to run, and Click OK.
Now that the button has been created, we need to edit the Button label. With the button still activated, click the button text. This will allow you to type directly on the button. Write something clever on it, then press Enter.
Click somewhere on the sheet, to disable the button edit function, and to activate the button.
Now, all that is left, is to save your document. Make sure you save it as a Macro Activated Excel Document (xlsm).
This File: Documents contains both an 2010-Version (xlsm) and the XP-Version (xls).
Part 3, will take this macro functionality longer. We will add Error handling, and make sure we don’t overwrite existing files. We will add more settings to the “Settings” sheet. Then we will add another Macro just for fun.