Getting help on programming Access with VBA

Giving many advantages for rapid development of application (prototypes) Access in conjunction with VBA and SQL is a mighty tool. Where else do you have an application environment with integrated data storage options and the possibility to share your program in one file, without having the need to have any installation process on the target machine if already Microsoft Office with Access or at least the Access runtime are installed. Nonetheless, VBA (Visual Basic for Applications) is different from the original programming language Visual Basic and at times can be challenging regarding details and realizing one particular feature, that is not documented well and hard to figure out.

As I often spend time researching VBA particularities on the web today I want to share the best ways to find help and find it faster. This will boost your productivity in programming with Access and VBA and help you overcome sticking points, that would otherwise enormously longer your development times.

Finding Microsoft Access’ in program help and Downloading additional help files

The Help Dialog in Microsoft Access 2013 opens using the Help shortcut F1 (see Figure 1). Contrary to Microsoft Access 2010 it has less functionality and a real offline help is missing.

Figure 1: Help dialog in Access 2013

By pressing the small Arrow next to the dialog title you can open the context menu for choosing between online help and offline help (see Figure 2). Do not expect anything though when you are working on the plane or without WiFi. The offline help in Access 2013 will only give you information about the Ribbon buttons.

Figure 2: Microsoft Access Help Dialog

For having an offline reference of all available VBA functions and objects you will have to manually download the Developer Documentation (in CHM-Format, Compiled HTML Help). Browse to the Microsoft homepage: https://www.microsoft.com/en-us/download/details.aspx?id=40326. The necessary help files consist of the Shared Developer Documentation for all Office Applications and the Access specific Developer Documentation (see Figure 3).

Figure 3: Choosing the Developer Documentation files to download

After downloading you have to unblock the files before being able to open them in the Windows help viewer. Open the context menu of the files in windows explorer by doing a right click and select Properties. In the Properties dialog, click on the Button Unblock (see Figure 4) and close the Properties dialog afterwards.

Figure 4: Unblocking the downloaded Developer documentation

With the Access documentation opened, you can browse the contents by topic or directly search for a documentation of Access specific objects and functions (see Figure 5). On the other hand, you can find help on VBA functions, that are shared through all Office Applications, in the Office Shared Developer documentation (see Figure 6).

Figure 5: Access specific objects in the Access Developer Documentation

Figure 6: General VBA Functions and Objects in the Office Shared Developer Documentation

Find resources from Microsoft (MSDN)

Microsoft itself created many resources to aid your developer needs in Access. For all newcomers you can find an introduction to programming in Microsoft Access on support.office.com showing you how to use macros in Microsoft Access. Honestly, I never used Macros here and I rather get annoyed of the built-in functionalities, as I prefer to write my own VBA code. Still, for beginners in Access AND programming, this might be an easy way to near yourself towards programming with Access and VBA. Furthermore, the linked support page shows you how to use the in-program help of Microsoft Access.

MSDN, the Microsoft Developer Network, has detailed documentation for all existing VBA functions. While having information on many functions, the presented resources do not always answer all your questions. Nonetheless, here you can find links to the documentation for Access 2013:

Access 2013 documentation on MSDN

To search the whole Developer network for help on a certain keyword, click on the search symbol in the upper right corner of the page and type in what you are looking for (see Figure 7).

Figure 7: Finding help on a keyword in the Microsoft Developer Network

Other resources and third-party help sites

Support sites & forums

References and blogs

Tutorials and online courses

Read More

How to handle dates with VBA

This post is about doing all kind of operations on Dates, like adding intervals, substracting dates or finding the Xth working day in the future. Every now and then, I need to use one of these functions to do a specific tasks only to notice I had forgotten what’s the function name or how to use its parameters. So here it is – the missing manual on Date functions!

Declare and intialise Date functions

You should always declare your variables to prevent Type errors and automatic type conversions. Here is how:

Code Snippet 1:

Dim dat_today As Date

dat_today = Date()

 

If today was the 1st of January 2015, dat_today would now hold this date. You have to consider, that date variables in VBA, can hold date, time or even date and time!

Code Snippet 2:

Dim dat_today As Date

dat_today = Date()

Debug.Print dat_today

dat_today = Time()

Debug.Print dat_today

dat_today = Now()

Debug.Print dat_today

 

In Code Snippet 2 we change the value of dat_today three times, printing its content to the immediate window shortly afterwards. A sample output, you might receive in the immediate window, is shown in Listing 1:

Adding a time span to your date

After initializing your date with Date(), Time() or Now(), an algorithm or from a database table, you might want to add a specific time span to this date. VBA offers you this functionality with the function DateAdd(Interval as String, Number as Long, StartDate as Date) As Date, which requires the arguments Number (the quantity of Interval, e.g. days or years to add to your StartDate) and returns the resulting date. While Number must be a Long-Value, Interval is a string code for the intended time unit (e.g. “d” for days, “yyyy” for years or “n” for minutes; see the MSDN link below for a complete reference).

Code Snippet 3 shows an addition of months and a substraction of seconds to the Date/Time value of the Now function:

Code Snippet 3:

Dim dat_now As Date

dat_now = Now()

Debug.Print dat_now

Debug.Print DateAdd(“m”,5,dat_now)

Dim dat_10secondsEarlier As Date

dat_10secondsEarlier = DateAdd(“s”,-10,dat_now)

Debug.Print dat_10secondsEarlier

 

Which might give the output seen in Output Listing 2, depending on your current time:

In the next section I will show you how to retrieve the time span between two dates in your unit of choice.

Retrieving the time span between two dates

To find out the number of days you have to wait until your birthday or Christmas or until the release date of your favorite new product, you use DateDiff. DateDiff(Interval as String, StartDate as Date, EndDate as Date) as Long, similar to DateAdd, requires three arguments: The interval as String (e.g. “d” for day, “m” for month, same as for the DateAdd function). StartDate is the Date earlier than EndDate, in which case you will get a positive Long-Value as Return Value. If you set the later date first, the time span will have a negative sign.

Code Snippet 4 shows example usage of the the DateDiff function:

Code Snippet 4:

Dim dat_today As Date

Dim dat_tomorrow As Date

dat_today = Date()

dat_tomorrow = DateAdd(“d”,1,dat_today)

Debug.Print DateDiff(“h”,dat_today,dat_tomorrow)

Debug.Print DateDiff(“n”,dat_today,dat_tomorrow)

Debug.Print DateDiff(“s”,dat_tomorrow,dat_today)

 

Code Snippet 4 will give you the output shown in Output Listing 3:

Format dates or part of them into strings

Often you may come to the point where you want to format your calculated or modified date back into a string to print it to the user or write it into a table in your database. Here the function Format comes in handy, that helps you to force your date/time information in a string exactly in the form you want it to be. The Format function may also be used to format numbers, by the way. For now, only two of optionally four arguments of the function Format(expression as Variant, format as String) as String are relevant for us. To provide a date as expression, we can choose to deliver a string variable or a date function. In case of a string VBA will automatically recognize the date character, if your format implies the expression to be a date and the format of the string is any kind of date format.

Now our format string: similar to the interval variable for DateDiff and DateAdd functions you may use “d” or “dd” for the day of the month (later will use leading zeros), “mm” or “m” will respectively return the month with/without leading zeros. Three mmms in a row (“mmm”) will return the full month name’s abbreviation in three letters and four mmmms the full month’s name, depending on your local language (e.g. January for English system language). Furthermore, “yy” and “yyyy” will return the last or all four digits of the date’s year. Equal to “d” and “dd”, “h” and “hh”, “n” and “nn”, “s” and “ss” will return hours, minutes and seconds without/with leading zeros respectively.

Now the clue is, that you can combine all of the symbols named in the above paragraph, in a custom string with all additional letters and symbols that you want. E.g. to format your date dat_yourdate in U.S. date format, you would use: Format(dat_yourdate,”mm/dd/yyyy”). Here / corresponds to your local date separator. If that one should not be the symbol “/”, you must escape the slash again to get it in the string: Format(dat_yourdate,”mm\/dd\/yyyy”). To return any functional letter sequence itself inside your string (e.g. “dd”) you need to escape it, too: Format(“12:45″,”h\h”) will return the string “12h”.

Let us get to the examples. Code snippet 5 shows combined usage of DateAdd, DateDiff and Format functions:

Code Snippet 5:

Dim dat_today As Date

Dim dat_tomorrow As Date

 

Dim dat_today As Date

Dim dat_tomorrow As Date

 

dat_today = Now()

dat_tomorrow = DateAdd(“d”, 1, dat_today)

 

Debug.Print “Our meeting will begin at ” & Format(dat_today, “hh\h”) & ” on the ” & Format(dat_today, “mm/dd/yyyy”) & ” and will end on the” & Format(dat_tomorrow, ” mm/dd/yyyy “) & “(” & DateDiff(“d”, dat_today, dat_tomorrow) & ” day(s) later).”

 

Find your dates nicely formatted in Output Listing 4:

Find more info on MSDN

This is all you need to know about entering, processing and formatting dates so far. If you have any more questions, let me know in the comments below. You can also find more information in Microsoft’s online documentation:

MSDN, the Microsoft Developer Network, has detailed documentation for all the VBA functions that exist. While having information on many functions, the presented resources do not always answer all your questions. Nonetheless, here you can find links to the documentation of all the elements and functions we used in this post:

Read More