This lab exercise will help you to start you on the road to using VBScript as a scripting language and VBA as a scripting tool within the Microsoft Office set of applications …
VBA Experimentation
Learning to write a macro in Microsoft Excel using VBA and VBScript
OSF – PROG1596
Contents
Introduction ……………………………………………………………………………………………………………………………………………………………………………………………………….. 2 Importing / Loading RAW Data into Excel ………………………………………………………………………………………………………………………………………………………………. 3 Getting Ready to Write Your First Excel Macro ……………………………………………………………………………………………………………………………………………………….. 8 Writing the Macro ………………………………………………………………………………………………………………………………………………………………………………………………. 9
VBA/VBScript Resources to Look To …………………………………………………………………………………………………………………………………………………………………… 9 Some Common VBScript Functions and VBA Techniques ……………………………………………………………………………………………………………………………………… 9 Starting Your Macro……………………………………………………………………………………………………………………………………………………………………………………….. 11 Macro Requirements ……………………………………………………………………………………………………………………………………………………………………………………… 15
Introduction
As mentioned in this week’s module – there are many different scripting languages available to us in the Windows OS environment. One of the scripting languages is VBScript and one of the scripting environments and tools is VBA (Visual Basic for Applications). This week in lab, you will be exposed to both VBA and VBScript for Microsoft Excel and learn to create a basic macro (a fancy word for a script) which will be used to transform some unformatted / raw data into a more useable, different format.
Unlike other labs in OSF this semester – there is no lab report to be completed or created. What you need to do is follow the steps in this Excel/VBA walk-through and submit your macro-enabled Excel spreadsheet (after ZIP’ing it up) to the “OSF: L-10 – Advanced Forms of Scripting” drop-box by the due date.
.
Since this lab is not a typical one, you will have more time to investigate, program and complete it. The lab is due Thursday December 6, 2018 by 11:00pm in the
drop-box
OSF – Lab 10 Page 2 Module 11
Importing / Loading RAW Data into Excel
Unlike the name implies, CSV files do not have to have the various fields of data separated by a comma … there are many other characters that can be used to separate the fields. Another common character is the pipe character (“|”) – we’ve seen this character in OSF as an input/output redirection character.
In the sample raw data file that you need to import into Microsoft Excel – the various columns of data are separated by this character. We need to tell Excel to expect this as the separation character in a file with a .csv file extension [because it assumes a comma is used]. [
] To do this, follow this process:
• This will open up an Excel Workbook. Most likely your workbook will come with 3 existing sheets already present in the book as shown here:
• Alternatively, it may open a workbook with only 1 sheet in it like this:
• If you do only have 1 sheet in Excel when you open it, press the “+” sign to the right of the Sheet1 label to add another sheet
• Before we import the raw data, let’s get the first sheet in the workbook ready to accept it. Let’s rename it to read RAW_DATA. Do this by right-
clicking on the Sheet1 label and selecting the rename option. When you’re done this, your workbook should look like this:
2. Now we will import the pipe delimited CSV file. (that’s actually how you would refer to the rawData.csv file in industry)
• While you are still in the RAW_DATA sheet – you need to click on cell A1 within the sheet. This is important – because when we begin to import the
data, it will import it to whatever the currently active cell is within the active sheet in Excel. This is what you should be seeing within Excel:
OSF – Lab 10 Page 3 Module 11
outlined below does not work in Excel within Office365. This lab has not been tested in Office365’s version of Excel
1. Launch Excel 2016 (or earlier)
NOTE: the process for importing the raw data
•
Select the Data tab across the top of the Excel ribbon and click on the From Text button as shown here:
• A pop-up window will appear. Navigate within this window to locate the rawData.csv file that you have downloaded from this lab’s content, select the file and click the import button
At this point, the Text Import Wizard windows will appear and lead you through a series of 3 screens where you effectively tell Excel how the data is formatted in your CSV file and how to interpret and read it … follow the steps you need to do through the following series of screen captures:
Page 4
•
OSF – Lab 10 Module 11
Step 1
OSF – Lab 10 Module 11
Page 5
Step 2
•
Step 3 is a little more involved … you use the third screen of the import wizard to walk across each of the columns of data in your file to indicate what type of data / what values / how to interpret the data for Excel
• By default when this third screen initially shows – it will look like this:
• This screen currently has the first column of data in the input CSV file highlighted in black (nice highlight colour – eh?) and this screen is asking you through the radio-button selections at the top of the screen if this column should just be interpreted as general format data, text data, a date field or whether you want to skip (and not import this column). [NOTE: The general format allows Excel to look at the contents (value) of the column’s data and decide if it is numeric or text]
• So you need to click on the column in the bottom window of the wizard and select the appropriate radio button data choice in the top
• Walking across the data file, we want the following data interpretations: a. Customer Name – make it a TEXT field
b. Customer ID – make it a TEXT field
c. Shipping Address – make it a TEXT field
d. Email – make it a TEXT field (you may use the scroll bar at the bottom to see the other columns) e. Phone Number – make it a TEXT field
f. Date of Birth – make it a DATE field (format MDY)
g. Last Purchase Date – make it a DATE field (format MDY)
h. Last Purchase Amount – make it a GENERAL field
• Once you’ve selected all columns and changed their import interpretation / mapping (as in the step above) you can click the FINISH button
Page 6
OSF – Lab 10 Module 11
• One final pop-up window from Excel will be asking you where in the Excel workbook you want to import the data to. This allows you to import the data to the current sheet in the spreadsheet at any location … remember how we already selected cell A1? We could change this import location here. By default, the current existing worksheet will be selected and it will be prompting you asking if it should import to cell A1 as shown here:
— OR —
• You can simply click OK
3. At this point, you’ve successfully imported your rawData.csv file into the Excel workbook in front of you!! Good Job!! [It should look like the following]
OSF – Lab 10 Page 7 Module 11
Getting Ready to Write Your First Excel Macro
If you followed the steps above correctly – you will be sitting in front of:
• an Excel workbook containing 3 different sheets (named RAW_DATA, Sheet2 and Sheet3)
• the workbook’s name (appearing in the top title bar) will most likely be called something like “Book1”
We need to take this unnamed Excel workbook and save it – but make sure to save it in an Excel format which will support and allow us to create the macro that needs to be created. Follow these instructions in order to save the file as required:
• Click on the File menu and select Save As
• In the Save as type drop-down select the “Excel Macro-Enabled Workbook (*.xlsm)” option
• Edit the filename to be your name – for example, if your name is John Smith – then call the file johnSmith.xlsm as shown here
OSF – Lab 10 Module 11
Page 8
Writing the Macro
As mentioned, this is a self-directed exploration of VBScript and VBA within Excel. As such, it will require you to do some amount of investigation into which VBScript functions to call and use and some degree of reading online help …
VBA/VBScript Resources to Look To
As mentioned in Module-11, there are 3 very good VBA resources that you can look to for reliable help and good examples (besides the built-in help within Excel that is):
• SS64
• W3 Schools – VBScript Functions
• Tutorials Point
Some Common VBScript Functions and VBA Techniques
I’ve pulled together some information about common VBScript functions and what they do as well as common techniques for accomplishing tasks within Excel:
1. As shown in the VBScript and Excel examples of Module-11 … it is good practice to get in the habit of declaring and initializing the variables that you will be using in your macro. Carlo wasn’t wrong in your C/C++ course!!! Declaring and initializing variables is always a really good thing to do!!
o ButjustsoyouareawareofoneofthedifferencesbetweenVBScriptandCasfarasprogramminglanguagesgo…
C forces you to declare your variables before you can use them – VBScript doesn’t make you do this
Carlo recommends that you initialize your variables in C (really C doesn’t care if you do most of the time) – VBScript really doesn’t
care if you initialize your variables
2. Remember that you access elements within an Excel spreadsheet by referencing the sheet that the data is found in as well as the row and column
that the data can be found in. For example – if I wanted to set the value of cell D8 on Sheet2 to a value of “Hello” – I would write
Sheet2.Cells(8,”D”) = “Hello”
3. Within your macro, you generally know what datatype you want your variable(s) to be – and when your variable’s values are coming from cells in the spreadsheet there are some very commonly used conversion functions that you can call upon to ensure that the value ends up being your datatype and not the datatype that the value was when it was in its cells:
OSF – Lab 10 Module 11
Page 9
o CStr() – converts a value to a string
o CInt() – converts a value to be an integer
o CLng() – converts a value to be a long integer o CDbl() – converts a value to be a floating point
e.g. myStr = CStr(Sheet2.Cells(8,”D”)) e.g. myInt = CInt(Sheet2.Cells(4,”C”)) e.g. myLong = CLng(Sheet2.Cells(4,”C”)) e.g. myFloat = CDbl(Sheet2.Cells(2,”E”))
OSF – Lab 10 Module 11
Page 10
4. It would be obvious that if you have numeric (integer or floating point) variables that you want to add together you would simply use the + operator. In VBA, if you have 2 string type variables that you want to concatenate (combine) you simply + them as well. For example – if I had myFirstStr=”Sean” and mySecondStr=”Clarke”, I could make my name (and put it in cell in a sheet) with this line
Sheet2.Cells(1,”E”) = myFirstStr + “ “ + mySecondStr
5. So far, I’ve been using letters to represent the columns in my sheet/cell references – but you are also able to reference the columns as numbers where…A=1,B=2,etc. SotheexampleabovecouldbeSheet2.Cells(1,5)=myFirstStr+““+mySecondStraswell
6. A very important programming concept to remember about VBA is that arrays are not zero-indexed – this is not the case in C
7. I’ve said this in class many times – but a large portion of programming and solving problems can be done through string manipulation and as a
result, all programming languages have a number of common string manipulation functions:
o Instr() – given a starting point in the string to search you can search for a substring (or character) within the string. This function
returns the index of the substring (or character) if it is found in the string. For example, let’s say I have a variable myStr=”The Quick Brown” and I want to find where the letters “ck” are found – if I had the line Instr(1, myStr, “ck”) it would return the index 8 (remember arrays and strings are not zero-indexed)
o Trim() – use this function if you have a string variable and you want to clean up the whitespace characters that may be found at its start or end. A whitespace character is generally a space character – for example if I had the variable myStr = “ Hello There! “ (you can see 2 spaces at the start and one trailing space), then myStr = Trim(myStr) would make myStr=”Hello There!”
o Left() and Right() – are 2 string manipulation functions that can be called to return the leftmost “n” characters in a string or the rightmost “n” characters in a string. For example – if myStr = “Bibidee Bobadee Boo”, then Left(myStr,4) would return “Bibi” and Right(myStr,3) would return “Boo”
o Mid() – is a string manipulation function related to Left() and Right() – but it allows you to extract portions of a string variable starting at any location and for any length. For example – if myStr = “Bibidee Bobadee Boo”, then Mid(myStr, 5, 7) [start at character position 5 and extract 7 character] would return “dee Bob”
o Len() – is a function that returns the length of the string (i.e. the number of characters in the string). For example: Len(myStr) where myStr=”Sean Clarke” would return 11
8. Another common task that you will find yourself doing in data transformation tasks is the manipulation of values that represent dates. You may need to extract the year from a date, or the month or the day. You may also need to take a raw data value (representing a date) and reformat it to be read as words instead of numerics. VBScript offers many, many functions to manipulate dates – including some to find the difference between 2 dates (perhaps to find the number of days between 2 dates). Other functions allow you to add a number of days to a given date … the list is endless.
o Year() – given a value that represents a valid date, this function returns an INTEGER value which is the year component of the date
o Month() – given a value that represents a valid date, this function returns an INTEGER value which is the month component of the date
o Day() – given a value that represents a valid date, this function returns an INTEGER value which is the day component of the date
o Forexampleifthedateis12/31/2018(representingDecember31,2018)–thenYear()returns2018,Month()returns12andDay()
returns 31
Starting Your Macro
Okay – so back to that Excel spreadsheet with your recently imported raw data that is sitting in front of you. Because we saved the file as an .xlsm file (macro-enabled spreadsheet) – all we need to do is to create a macro in the spreadsheet. NOTE: When we start a macro – you will see another side of Excel that perhaps you have never seen before … it will take you into a programming side of Excel! To start your macro for this lab, follow these instructions:
1.
Select the VIEW tab across the top ribbon of Excel
2.
Now select the Macros button in this ribbon and choose the View Macros option
OSF – Lab 10 Module 11
Page 11
3. When you do this, you’ll be presented with a pop-up dialog that is asking you about running, editing, deleting or creating a macro. In this case, we want to create a new macro … it is counterintuitive, but before we get to press the Create button – we need to give our macro a name. So enter the name of your macro in the top line of the dialog … call your macro “L10_Format_Macro” and then press the create button
OSF – Lab 10 Module 11
Page 12
4. As mentioned above, this opens a whole new Excel window exposing the programming side to Excel – where we will be using VBA and VBScript to do some processing and reformatting of the RAW_DATA that we just imported. This is what you should be seeing right now …
OSF – Lab 10 Module 11
Page 13
5.
And so you begin to declare and initialize the variables that you need in your macro …
• •
But wait!!! How do you know that variables you need in your macro?? You need to read the requirements below first and then according to best practices from SEF (and Carlo in C/C++ class) – you need to pseudocode your solution first
In order to help you on your way of creating this macro … I found an old spreadsheet that a former student (John Smith) created for this assignment. He started writing this macro and has some of it completed and other parts … well – it has to be completed.
• The one nice thing about John’s spreadsheet is that he commented it quite well – he left notes to himself about things he needed to finish and possibly how to do it … John was a nice guy!!
• You can find John’s spreadsheet and use it as a guide and/or use his code in the johnSmith.xlsm file found in the JohnsSpreadheet.zip file in this lab area. In order to see John’s macro:
1. Unzip the file and open his spreadsheet – and if challenged to do so – make sure you “enable macro content”
OSF – Lab 10 Module 11
Page 14
2. Click on the VIEW tab of Excel and then go into the Macros button and click View Macro
3. Select the macro that John started and click on the EDIT button
Macro Requirements
What you have in front of you in your spreadsheet is a collection of raw data that we need to reformat and process through the creation of an Excel macro to perform some necessary transformations and steps. Below is a table that describes the raw input values as well as any processing / transformation that needs to be done on them
Data Element
Raw Data Source
Raw Data Format / Notes
Output Data Location
Transformation Notes
Customer Name
RAW_DATA – Column A
1. currently the fields are constructed to contain the person’s last name, a comma and then their first name
2. this field will always be present in the raw data
Sheet2 – Columns B and C
1. write some code to extract the last name and first name from the raw data and write them out separately
Customer ID
RAW_DATA – Column B
1. this field may contain only the numeric digits of the actual customer ID value (e.g. 2015090001) or it might contain the real, full customer ID value (e.g. CUST-2015090001)
2. this field will always be present in the raw data
Sheet2 – Column A
1. when processing the raw data value for this element – check if it starts with the “CUST-“ value and if it does, just write it out to the output location. If it doesn’t, then add the “CUST-“ to the start of the raw ID value
Shipping Address
RAW_DATA – Column C
1. this field contains the customer’s address and is made up of 3 sub-fields (the street address, the city and the province). These sub-field values are separated within the raw data by commas.
2. this field will always be present in the raw data
Sheet2 – Columns E, F and G
1. when processing the shipping address, extract the street address portion out and write it to column E. Extract the city portion and write it to column F and then extract the province and place it in column G
Email
RAW_DATA – Column D
1. this is simply the customer’s email address
2. this field may be empty in some customer’s data
Sheet2 – Column I
1. if the customer has an email address, then output it to the location
2. if no email was given – then output nobody@nowhere.com
Phone Number
RAW_DATA – Column E
1. this field will always be present in the raw data, but it may come in many different formats. It could be given in the format ###-###-#### (where “#” is a number) or the format ########## or ###-#### or #######
Sheet 2 – Column H
1. the output format for this field must always be ###-###-#### – so if there are dashes missing in the raw value, you’ll need to insert them
2. and if the area code is missing (the first 3 digits) – then insert “XXX-“ a the start of the properly formatted remaining 7 digits
Date of Birth
RAW_DATA – Column F
1. this field contains the customer’s birth date in the format M/D/Y
2. this field may be empty in some customer’s data
Sheet 2 – Column D
1. if the birth date is present, then reformat the date to be in the format YYYY-MM-DD where MM and DD are always 2 digits (e.g. 03=March) . Also note that dashes need to be inserted into the reformatted date 2. if the birth date is missing from the customer’s data – then leave this field blank in output
Last Purchase Date
RAW_DATA – Column G
1. this field contains the date of the customer’s last purchase and is given in the format M/D/Y
2. this field will always be present in the raw data
Sheet 2 – Column L
1. format this date the same way as you formatted the customer’s birth date
OSF – Lab 10 Page 15 Module 11
Data Element
Raw Data Source
Raw Data Format / Notes
Output Data Location
Transformation Notes
Last Purchase Amount
RAW_DATA – Column H
1. this field represents the last $$ amount that the customer spent. If the value is positive – it means that the customer purchased something and spent money. If the value is negative, it means that the customer received a credit (money back)
2. this field will always be present in the raw data – but it may be a value of 0.00
Sheet 2 – Column K
1. if the value in the raw data is positive – then output the string representation of the value
2. if the value in the raw data represents a credit, then output the word “CREDIT”
Sheet 2 – Row 1, Column B
1. as you are walking through the raw data and processing it – keep a running total of all positive purchase amounts and output the value into this cell after all raw data input rows have been processed
Sheet 2 – Row 1, Column D
1. as you are walking through the raw data and processing it – keep a running total of all credit amounts (a negative value in the raw data). You need to transform the negative value to a positive value and keep the running total so that after all raw data input rows have been processed, you can output the total credit value here
NOTE:
1. Since the processed / transformed output (found in Sheet 2 of the spreadsheet) has certain running totals being displayed in row 1 of the sheet, you will need to output your row-by-row
values for each customer’s raw data starting at row 4 of Sheet 2 (the output sheet)
2. As well, let’s add some column headings to the transformed / processed data found in Sheet 2 … the column headings will be found in row 3 of Sheet 2. Let’s title the columns as follows:
– COLUMN A : Cust ID
– COLUMN B : First Name
– COLUMN C : Last Name
– COLUMN D : BirthDate
– COLUMN E : Street Address – COLUMN F : City
– COLUMN G : Province
– COLUMN H : Contact
– COLUMN I : Email
– COLUMN K : Last Sale Value – COLUMN L : Last Sale Date
SPECIAL NOTE:
1. For the “Date of Birth” and “Last Purchase Date” reformatting of output into Sheet 2 – we want to tell Excel that the value we are printing should actually be interpreted as a TEXT value
and not allow Excel to look at the value and interpret and display it as it sees fit. You can do this in your reformatting of the dates by starting the reformatted output value with a single
quote mark (‘). So if your macro is trying to reformat and output the date 5/23/1980 – your generated output string would be ‘1980-05-23
2. For the “Last Purchase Amount” value that you output into Sheet2 – try to figure out a programmatic way that you can ensure there are always 2 decimal places being shown. That is, if the
dollar amount is “523.70”, Excel will tend to show “523.7” … if the dollar amount is “0.00” then Excel will show “0”. If you cannot figure out how to always show 2 decimal places, just make sure you actually show the dollar value in the output and let Excel show it as it wants to …
OSF – Lab 10 Page 16 Module 11