Category: Office

* 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.

Custom functions

Preface

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:

=IF(WEEKDAY(C14)=2,C14-3,IF(WEEKDAY(C14)=1,C14-2,C14-1))

(C14 is the cell containing the original date)

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!

With VBA

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)
        Case 1
            BusinessDayPrior = dt - 3 'Monday becomes Friday
        Case 7
            BusinessDayPrior = dt - 2 'Sunday becomes Friday
        Case Else
            BusinessDayPrior = dt - 1 'All other days become previous day
    End Select
End Function

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)

Automating tasks

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

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 14/10/2009 by SilentWind
'

    Dim Temp As Double
    Temp = ActiveCell.Value               'Get selected cell value
    Sheets("Sheet3").Select 
    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
            Exit For
        End If
    Next i
End Sub

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.

Conclusion

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.

You can download the demo workbook here.

Reference

Wikipedia’s article on this