My work is not mainly about Excel but people often come to me for solution to their everyday computing problems. Eventually after several months of repeating the same thing to many people, I decided to sum up all the questions and make a training session covering simple but many people just don’t know they existed in Excel.
I am not intending to teach people how to use Excel but how to use it efficiently. So my method is mostly visual-based: I use a lot of images to illustrate concepts so they can recall what I’ve said later easily. I intended to make some practice exercise to reinforce their knowledge too but my folks were strongly oppose against homework so that idea was abandoned :p
The first session have 4 attendants, which I considered a successful experiment. It lasted for around 45 minutes and covered the following topics:
Counting with multiple conditions
Visual Basic for Application basics
I intended to record the session for later broadcast but forgot it on that day. So if you are interested, here are the slides at screen resolution (72 dpi i.e. it look pretty crooked) together with notes in PDF format. Powerpoint file is available upon request if you want to conduct similar sessions. I love to spread knowledge 🙂
* For the real action, jump straight to the “with VBA” section
It’s there a decade ago, it started off the wrong foot, generated some (bad) publicity and people started to wonder why it’s there in the first place. It’s quite surprising how people are ignoring it by now. It’s just sitting there in the corner of the file system, doing various sort of slavery work for people. Visual Basic for applications (VBA), imagine how we missed ya!
When I was a kid, I once thought that by choosing the path of a developer, I would sooner or later encounter it and do various sort of wondrous stuff with it, like some kind of smart worksheet that won’t let you type character data into numeric cells.
Sadly, that never happened. There are other ways to do that without VBA, or even without Excel since now I know, there’s Open Office and all. VBA’s role is even somewhat superseded by Visual Studio tools for Office/Applications.
Despite all that, VBA is still there, in the latest (official) version of Office for you… There’s no reason for it to wait any longer, right? This is intended to be a short (and simple) tutorial covering a fraction of what VBA can do for an average user, so you don’t have to be a total nerd to follow this.
Excel comes with a huge load of functions (hundreds or so) serving various purposes, but sometimes even that is not enough! Let’s say you want a function that returns the last working day before a certain date. Doing this week Excel’s function could be tedious because you need to handle three separate cases which will require two IFs:
– If the given date is Sunday, the last working date is last Friday (Two days backwards).
– If the given date is Monday, the last working date is also the last Friday (Three days backwards).
– For every other day of the week, it’s just the previous day
And also, WEEKDAY() returns a number from 1 to 7 so you have to remember which number corresponds to which day to make the IFs. The finished function should look like this:
Give that function to someone who haven’t read the two paragraphs above, it will take them at least 5 minutes to figure out what are you trying to do in that cell, and then you’ll waste yet another comment to explain that to them!
You can program a new function to do the above.
1. From Excel press Alt+F11 or open the following menu/ribbon:
2. Select new module from the toolbar
3. Type in the following code in the new window
Public Function BusinessDayPrior(dt As Date) As Date
Select Case Weekday(dt, vbMonday)
BusinessDayPrior = dt - 3 'Monday becomes Friday
BusinessDayPrior = dt - 2 'Sunday becomes Friday
BusinessDayPrior = dt - 1 'All other days become previous day
It will look like this
4. Click save, return to the main excel window, type a date you want and in C14 and =BusinessDayPrior(C14) (replace C14 with any cell of your choice), the result will look like this
Much clearer, and for an exercise, try to handle the case to suit your own specific need: tweak that function so it will handle holidays too:
– What if the given date is right after a one-day long holiday?
– What if the given date is right after a several days long holiday?
– What if you have compensated holidays (e.g. if the holiday is on weekends, you get another day off)
Imagine this scenario: You are browsing through a list of stock quotes and you want to take note of profitable stocks on another sheet. If you are doing this manually you’ll have to copy the stock name, switch to the other sheet and paste it. With VBA you can have a button on the sheet that when you click, will do all three steps for you.
1. Open the visual basic editor with Alt + F11 and create a new module (see above on how to do it)
2. Add the following code to the window
' Macro1 Macro
' Macro recorded 14/10/2009 by SilentWind
Dim Temp As Double
Temp = ActiveCell.Value 'Get selected cell value
For i = 1 To 65535 'Scan the destination sheet…
Range("A" + Trim(Str(i))).Select
If (ActiveCell.Value = "") Then '…for the first empty cell
ActiveCell.FormulaR1C1 = Temp 'Copy it to the destination sheet
3. Draw a button on the sheet:
– In Excel 2007, go here
– In Excel 2003, right-click the toolbar, select customize, and drag the button onto the sheet
– In place of the button, you can even you an auto shape! Contrary to popular belief, it does not always require a button to do some useful task!
4. Right click the newly added button/shape and select Assign Macro…
5. Select Macro1 (the one you created at step 2)
Now, every time you select a stock name and click on the button/shape, it gets automatically added to the last empty cell in column A on sheet3.
In Excel 2003, you can drag the button onto the toolbar instead; the steps are still the same. In Excel 2007, you can only add that button to the Quick Access toolbar (well, unless you know how to use VSTO…):
– Right click the Quick Access toolbar and select Customize Quick Access Toolbar…
– Add your macro by selecting “Macros” from “Choose commands from”, select your macro and click add.
I think the two examples above are enough for casual user to understand and adapt for their daily use. Application is endless! Should there be any other questions, feel free to ask; I will add them to future posts if they are interesting enough.