One of the things which strikes many an intern when he steps into investment banking is the extent to which the industry relies on the tool called Excel. As a co-intern pointed out, many investment banking internships are indeed “Excel”lent experiences.
I have had my time with VB coding and personally find it quite an enjoyable thing to do. Also, the ability to code up excel macros is a very useful skill to have in the real world .
So I thought I would put a rough and dirty guide to Excel coding. The aim is really to get the beginner started and running. For many of the advanced applications, there are quite a few resources on the Web.
Before that, dumb as the following may sound, let me clarify a definition first. The term ‘Workbook’ (which we will never almost use) refers to the .xls file. The term ‘Worksheet’ (which we will use a lot) refers to the individual sheets in the workbook. This is very basic and if there is a confusion here, there could be problems later on, so I have just added a picture. Excel Demo.xls is the workbook and ‘Industry’, ‘DataDumpA’ and ‘DataDumpB’ are the Worksheets.
Let us take a simple problem. Say you have got data from data sources on two companies A and B which you have either copied on to the sheets or queried from some data source. Say you have copied quarterly sales data of company A and B into sheets ‘DataDumpA’ and ‘DataDumpB’.
For arguments’ sake, say A and B are the only players in the industry and you want to find the total sales of the industry. The easiest way is of course to use the formula in cell B4 as:
DataDumpA!B4+DataDumpB!B4
Sheetname! is the way to refer to worksheets in an excel sheet. You could use this when you have to manually input sheetnames at some places.
Let us do this in VB coding just to get an idea of how things work. The best way for a total novice to learn VB coding is by using the ‘Record Macro’ button. By recording the concerned macro and then seeing the code, the user gets an excellent idea of how vb coding works. I have Office 2007 and the screenshots are from that. I will also give the relevant instructions for Office 2003.
Getting Started:
Irrespective of the version, Alt+F8 should throw up a box like this.
Input a name for the macro and press the “Create” Tab.
This should get you to the Coding interface. (Phew... all these pictures just to get to the most basic step! The basics always take time I suppose...)
The “Sub” indicates that this is a macro. The way to access the macro is to either put in a command button that calls (The syntax is: Call MacroName) this macro or we can call it as part of another macro. We will revisit this.
The only other thing I have used is a Function. The code for that would start with:
Public Function FunctionName(inputs)
Okay, now that we have got the coding window, let’s type in the code.
(Disclaimer: I am no expert in coding practices. Some of the things I do may not be recommended coding practices. Again, this is only a rough guide to get you started on the job.)
Sub Add()
'Macro to obtain aggregate industry numbers
'Created on 14th June 2008
colcount = 3
For i = 1 To 4
Worksheets("Industry").Cells(colcount + i, 1).Value = Worksheets("DataDumpA").Cells(colcount + i, 1).Value
Worksheets("Industry").Cells(colcount + i, 2).Value = Worksheets("DataDumpA").Cells(colcount + i, 2).Value + Worksheets("DataDumpB").Cells(colcount + i, 2).Value
Next i
End Sub
Let us go through the code line by line
For i = 1 To 4
Next i
In VB Excel coding, the syntax for For is as above.
Worksheets(“Industry”).Cells(colcount+i,1).Value
This syntax is perhaps the most important syntax as it shows how to refer to cells in the Excel sheet through the VB code.
Worksheets(“SheetName”) refers to the specific sheet that has the data you are looking at.
Cells(row, column) is explanatory
.Value takes whatever is there in the cell as per the format in the cell. So if there is a date in the cell, it will take the date as it is in the cell format.
Say that the source data gives the data in MM/DD/YY format, but you want it as DD/MM/YY format, what you need to do is to change the format of the cell. So, right-click on the cell -> Format Cells... -> set the required format in that. Then VB would transfer the value as it is.
Some practical tips:
Most beginners would get the following error message:
Subscript out of range
This usually indicates that the Worksheet name has been input wrongly. So for example, instead of “DataDumpA”, if I had put “DataDump1” then this error would arise. This message would also come if an array assignment exceeds the declared boundaries of the array.
Say, an array of dimension 10 had been declared but if the ArrayName(12) is used, then the same error would arise.
As mentioned earlier, hardcoding the colcount is not a good thing. Perhaps we could have a flag. The string “Date” would be the flag and we should iterate until we find it.
The code for that would look as follows:
colcount=1
While Worksheets("Industry").Cells(colcount, 1).Value <> "Date"
colcount = colcount + 1
Wend
While
Wend
Is the syntax for the While loop
If Loop
There is the If loop and the If-Elseif loop.
Say in our sheet, we had to compare dates and if the quarterly dates did not match, then an error message box had to be thrown up.
The code would look as follows:
If Worksheets(“DataDumpA”).Cells(colcount,1).Value = Worksheets(“DataDumpB”).Cells(colcount,2).Value Then
Worksheets(“Industry”).Cells(colcount,1).Value = Worksheets(“DataDumpA”).Cells(colcount,1).Value
Elseif Worksheets(“DataDumpA”).Cells(colcount,1).Value <> Worksheets(“DataDumpB”).Cells(colcount,2).Value Then
MsgBox “Dates for the two companies do not match”
End If
Things neither here nor there
One of the biggest “stumbling blocks” in VB coding for excel is the “Not Equal to” Operator.
<> is used to indicate inequality
Declarations
The command Dim is used to declare data type.
If you notice we never used Dim in our code. That is because the operations are very simple. However if you had strings or arrays you would need to declare them. The syntax is as follows:
‘Declare a String
Dim Name as String
‘Declare an array of integers
Dim ArrayName(ArraySize)
‘Declare an array of strings
Dim ArrayName(ArraySize) As String
‘Declaring dynamic arrays
Say we need to have an array of a certain size x. However, x is the product of certain operations. Then we need to initially declare the array and then re-dimension it to the appropriate size x.
The syntax would be as:
Dim ArrayName()
ReDim ArrayName(x)
Wrap Up:
I think you are pretty much rough and ready to go. Hope this was useful. Again I stress that this is for those who need to get a quick move on things. There are always smarter ways to do stuff once you get the basics figured out.
Do tell me if this was helpful. I can put up more stuff on:
· Using the debugger
· Handling Strings
· Adding a command button
· Using Names in Excel for dynamic data management
