Using Visual Basic for Applications (VBA) isn’t really hard. Instead, I’d call it complex. This means that in order to do it proficiently you have a lot of small things to learn. And most of all, you have to stick to the task and don’t give up. Find a reasonably easy task and just work with it until it’s done.

I teach a beginning class in Visual Basic, three hours on a Saturday, spread over six weeks. That’s only 18 hours of instruction, and, for the most part, it’s been pretty successful. But only after I’ve learned a few very important things. I tell my students that if they attend every week and work hard in class … they’ll not learn anything, actually fail…And they’re just wasting our time.

Programming has to be practiced. It’s a continual voyage of discovery and accomplishment.

Now to the subject of user-written functions. Understanding functions is a very fundamental part of programming. Surprisingly though, you can program in Access (or Excel or … ) for years and never write a function. If that’s you, you’re missing something essential. Functions give you reusable code. You write functions to be general purpose so that on your next program, and the next, you just merge this function into your module and it’s ready to go without modification. ("Without modification" is a goal, not a rule.)

You have to think of a function as an encapsulation of a single idea. To illustrate this, let’s look at built-in functions. A built-in function – called intrinsic functions - are the building blocks of all programs. Let’s take Int() for instance. Here’s an example of how you use Int(). Dollars = Int( Cost ). This takes a variable called Cost, cuts off the fractional part, and stores it in Dollars as a whole number. So if Cost was 3.65, then Dollars will be 3. Int() is just one of dozens of intrinsic functions like Format, Ucase, Mid etc …

If you were going to write your own Int function, here’s what it will look like:

Public Function Int( ByVal InputVariable As Double ) As Double
‘Takes the Input Variable, and magically
‘cuts off the fractional part if any, and
‘returns it by assigning it to Int.
Int = … do something clever with math …
End Function

The first line is the function declaration. It simply defines the rules whereby it runs. The name is Int. It takes as a parameter one variable containing any number – InputVariable – and returns a number. Both the input and output are of type Double. Int is dimensioned as Double – the output and InputVariable is of type Double.

The lines beginning with an apostrophe are comments. Comments are ignored. The next line is incomplete. Frankly I can’t think of any way to accomplish this with math, but the objective is to take the value stored in Input Variable and cut off any fractional part. Since I can’t think of a way to do this, I substituted "psuedo code". The result is that this function won’t work, making it a bad example, but I hope you get the idea.

Here’s a better example that actually works.

Public Function Round2( ByVal ToBeRounded as Double ) as Double
‘Take the Input variable ToBeRounded, and
‘magically round it to two places.
Round2 = Int( ( ToBeRounded * 100 ) + 0.5 ) / 100
End Function

To use this function, in your code anywhere in your application, use it like this: Dollars = Round2( Cost ). So if Cost was say $105 times 1.0675 tax and you wanted it in dollars and cents, you’d need to round off to the penny. Useful ? Yes, absolutely. If you were to add a column of numbers that had them all rounded off, the result would be very different than if you didn’t.

I’d really not program Round this way. I’d add another argument that specifies the places that it would round to. So I’d make the header: Public Round( ByVal ToBeRounded As Double, Places As Integer ) As Double. This way it’s even more useful because I can round to zero places, or ten places.

OK, now you have the idea, I hope. That is to encapsulate an idea into a single, reusable function. Both examples stand alone with no outside support. They are completely portable. These kinds of functions can be thought of as "filters". There are a couple of entire computer languages that operate on the principle of taking data and "filtering" it until you get it where you want it.

I’ll give a much more complex example. I was asked to help with a problem from another employee in my company. She wanted to calculate 20 working days from now. Whoops. This is more complex.

Simulate it on paper first. Write the date. 7/1/1999 is a good one, and the date plus one, and the date plus 2 in a column on the paper. Write down say 35 days.

Look up the second date on a calendar. If it’s not a weekend or holiday, mark a 1 beside it, otherwise skip to the next date. If that one is not a weekend or holiday, mark it one plus the last one. The answer is the one that has 20 written beside it.

That’s not too bad. And here’s the next step, reproducing the ideas in a kind of a fake (pseudo-code) programming language. I made up my own pseudo-code so that you can easily read this example. It does exactly the same thing as the example above except that it delineates the repetitive part in a computer loop.

Set a counter to 0
Get a StartDate
Top of the Loop
If Counter = 20, then stop looping and go below the bottom of the loop
Add 1 more to Startdate and save it in StartDate
If StartDate is not a holiday AND is not a weekend day Add one to Counter
Else don’t
Go back up to the Top of the Loop
Here is where we go when the loop stops.
Since the counter is now 20, the result is the new StartDate

Now this code needs to be translated into the real thing. The header – the function definition – would be AddWorkDays( StartDate, NumberOfWorkDays) as Date. So this function returns a date, and that date is NumberOfWorkDays more than StartDate.

There are a couple of key things to work out. First, how do we know whether a date is a weekend day? And second, how do we find out if a date is a holiday? As it turns out, the first part is easy. There’s a function DatePart that does this. DatePart needs a date and two parameters, one that specifies that DatePart returns the day of the week. And the other parameter specifies that Sunday is the first day of the week. You use it like this: Result = DatePart("w", Date, vbSunday). It returns a 1 for Sunday, and a 7 for Saturday.

A holiday is much harder. The problem is that workday holidays don’t occur on the same days. Most holidays are predictable, but those aren’t necessarily the days we don’t work. So the only solution is to make a table. The table must be maintained manually. So I made a simple table in Access called tblHolidays. It has one column called Holiday, defined as Date/Time, and the Primary Key is placed on that column. And then I typed the workday holidays in the column.

Now we need to get dates from the table. Actually all we need to do is see if a date we have is also in the table.

We do this with some DAO programming. DAO uses the Access Jet engine to manipulate data tables. Here’s the sequence needed. Refer to the example below as we do this.

Now these concepts need to be integrated into the program. And it turns out pretty much as below. The structure of the program is the same as the pseudo-code above. But the DAO programming and the DatePart function is worked into it.

Public Function AddWorkDays(ByVal StartDate As Date, ByVal NDays As Integer) As Date
' ***************************************************************************
' Add NDays of workdays to StartDate.
' Use: NewDay = WorkDay( "7/1/1999",20) Adds 20 Workdays days to 7/1/1999
' Work days is defined as weekdays that do not appear in a table tblHolidays
' The Table tblHolidays has one field called Holiday, defined as Date/Time,
' required, no duplicates, and being the primary key
' ***************************************************************************

Dim db As Database
Dim rs As Recordset
Dim D As Integer
Dim DayIncrement As Date
' **************************
D = 0
Set db = CurrentDb ‘ Use the current Access database
Set rs = db.OpenRecordset "tblHolidays", dbOpenTable) ‘ Use the holiday table
rs.Index = "PrimaryKey" ‘ Assign the Primary key as the search column
DayIncrement = int(StartDate) ‘ Make sure there’s no time part on the startdate
Do Until D = Ndays ‘Top of the loop

Loop
rs.Close
db.Close
AddWorkDays = DayIncrement
End Function

It may take a little time to see how this works. I suggest highlighting some of the keywords like OpenRecordSet and press the Help button and learn as much about it as you can. Have Fun! u