Bradley Handziuk's blog

Always use Option Explicit

When writing any VBA for Excel, Access or Word or whatever you automate you have the option to use the default settings in the VBE editor. Well one of the default that Microsoft provides is to not enforce variable declaration. This code can run just fine
Function MyNewFunction()
    a = 5 * 7
    MyNewFunction = a
End Function
While this is not a very useful function you'll notice that a was used without every being declared. This often leads to a lot of problems for people. the usual things are
  1. They don't know when their variable went out of scope
  2. They don't know the data type of their variable (and consequently) there is no intellisense support for variables used like this
  3. They use it in appropriately like trying to add a string to a date

the trick is to always have option explicit declared at the top of all your modules. This means that if you do not declare a variable your code will not compile.

You should have the VBE automatically put option explicit at the top of any new modules/class you make by going to  Tools>Options>Editor> Require Variable Declaration = true

Now you can only write the much less sloppy
Function MyNewFunction()
    Dim a as Integer
    a = 5 * 7
    MyNewFunction = a
End Function
Now start hitting Compile often! These two together will save you a lot of headaches.

Finding a Access backend data file

Because you should be splitting your Access database before you give it to a customer to use you can oftern run into the problem of your app not finding that backend file. This is what I do to always ensure my front end points to the right data
I generally use an AutoExec macro to apply some basic settings when the app starts up (hiding the navigation pane, disabling full menus maybe, minimizing the ribbon maybe). At the end of all this I check to make sure the backend data re in place. The general process is to try to open a record set from a table you know exists. If that succeeds then super! you're conencted to your backend already. If that fails then you are probably not connected and you should take some action.
    Private Sub VeryifyBackendIsInPlace()
        Dim rst As DAO.Recordset
        FILE_NOT_FOUND_ERROR = 3024
        INVALID_PATH_ERROR = 3044
        On Error Resume Next
        Set rst = CurrentDb.OpenRecordset("Select top 1 * from Table_I_Know_Exists")
        If Err.Number = FILE_NOT_FOUND_ERROR Or Err.Number = INVALID_PATH_ERROR Then
            MsgBox Err.Description & vbNewLine & "You will be prompted next to locate the data file. Without this file the database cannot open." _
                , vbCritical + vbOKOnly, "Backend Data File Not Found"
            GoTo FindBackEndFile
        ElseIf Err.Number = NETWORK_CONNECTION_ERROR Then
            If MsgBox(Err.Description & vbNewLine & "Would you like to connect to another data source", vbYesNo, "Network error") = vbYes Then
                GoTo FindBackEndFile
            End If
        ElseIf Err.Number <> 0 Then
            MsgBox Err.Description, , "Could load the data"
        End If
        On Error GoTo 0
        Exit Sub
    End Sub
You see if we get an assortment of network/file connection issues I prompt the user to go look for the backend data file.
    Public Function AttachToAnotherDataFile() As Boolean
        On Error GoTo 0
        Dim ofd As FileDialog
        Dim result As VbMsgBoxResult
        Set ofd = FileDialog(msoFileDialogFilePicker)
        If ofd.SelectedItems.Count = 1 Then
            result = RelinkLinedTablesToBackend(ofd.SelectedItems(1))
            If result = vbCancel Then
                AttachToAnotherDataFile = False
            End If
            AttachToAnotherDataFile = True
            AttachToAnotherDataFile = False
        End If
    End Function
Then I relink all the linked tables to it.
    Function RelinkLinedTablesToBackend(backendPath As String) As VbMsgBoxResult
        Dim tdf As TableDef
        Dim db As Database
        Dim tdfRefresh As TableDef
        Set db = CurrentDb
            For Each tdf In CurrentDb.TableDefs
                If tdf.Connect <> vbNullString Then
                    On Error Resume Next
                    db.TableDefs(tdf.Name).Connect = ";DATABASE=" & backendPath
                    If Err.Number <> 0 Then
                        RelinkLinedTablesToBackend = MsgBox(Err.Description, vbCritical + vbRetryCancel, "Error #:" & Err.Number)
                    End If
                    '' RelinkLinedTablesToBackend = VbMsgBoxResult.vbOK
                    On Error GoTo 0
                End If
        Set tdf = Nothing
        Set db = Nothing
    End Function
This strategy only works if all the linked tables are in the same file.

I could update it so that it only updates those linked tables which it could not conenct to.

How we do computer backups

Computer backups

I thought I should write a little about how our data are backed up because I always forget. 

My desktop is backed up internally. There are 4 drives.

A 128 GB SSD which is where Windows lives (C: drive) is backed up to another internal mechanical 500 GB drive (A: drive)

A 1 TB internal data drive (J: drive) where all our "stuff" (not programs) is stored. This is backed up to another internal 1 TB drive (B: drive).

Drives A: and B: are hidden from being seen in Windows Explorer by setting the NoDrives key in the registry (HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer) to 3. (A = 1) + (B = 2) = 3.

My drive setup in Disk Management

1 - The OS Backup

The OS is backed up to the A: drive through the built in Windows back up utility (File History)

Control Panel\System and Security\File History

2 - The Data Backup

Data are backed up using the Microsoft tool SyncToy. You point it to a folder (Left Folder, J:) and tell it where to sync (Right Folder, B:). It's set up to Echo which means it will

New and updated files are copied left to right. Renames and deletes on the left are repeated on the Right.

This is set up to run as a scheduled task. I named it "SyncToy Data Drives" and set it to run every Monday at 3:18 AM.  It runs the following command

"C:\Program Files\SyncToy 2.1\SyncToyCmd.exe" -R "Back up new TB to old TB"

I allow it to  "Run whether user is logged on or not"

This all works pretty well. Except sometimes 1. hits a glitch not recognizing the drive.

I'm not sure what the heck is going on.

Emma's laptop is also set up to back up to the 500 GB drive. There is a virtual drive created in a folder there which is mounted on her computer as a 'local drive'. This was the only way I could get Windows 7 Home (the version she has) to do network backups; trick it. I can write about that more later. It is very finicky and doesn't always reconnect.

The only off site backups we have going on are a lot of pictures backed up to Google+ and out YNAB accounts are synced to Dropbox. We should be better about that but we really don't have all that much going on with out PCs at home.

How to reuse VBA function in all your Excel files

Excel allows you to create .xlsm files (macro enabled spreadsheets) but these are a bit of a hassle because whenever someone wants to open one they need to click "Enable Content". The other big issue is that any code you write is not reusable in other files. Creating an Excel Add-in means you can save commonly used functions in a central repository. They don't move with the file but they are always there for you on your computer. So, depending on your needs this might be better than an xlsm.
Note: there is another option of saving your reusable code in your Personal.xlsb file.

To make an Excel Add-in
  1. Open a new Excel file and Save As...
  2. Make the file type Excel 97-2003 Add-In (*.xla). This will change the save path to something like C:\Users\%UserName%\AppData\Roaming\Microsoft\AddIns. If you are using a new version of Excel you can also use the .xlam Excel Add-In file type)
  3. Make the File name something like "MyUDFs"
  4. Save. Close your new xla (or .xlam)
  5. Reopen your just made file (the VBA IDE can be finicky about what shows up and this is a good step just to ensure everything is fresh in the Project Explorer window)
  6. Open the VBA IDE (Alt+F11) and add a new Module
    1. With "VBAProject (MyUDFs.xla)" selected, Insert > Module 
  7. Let's create an example function
    Function IsFormula(rng As Range) As Boolean
        IsFormula = rng.HasFormula
    End Function
    Whatever code you write in here will be available in your worksheets. Only functions (not subs) will show up as available worksheet functions. You can save subs in here if you want, too; they will be usable but not from a worksheet.
  8. Save. Close. 
  9. Open a fresh, blank Excel workbook. On the ribbon select Developer > Add-Ins
    1. If the developer tab is not showing for you you can make it show up by right clicking on the ribbon > Customize the Ribbon...
  10. When you have the Add-Ins dialog open you can select your newly minted add-in from the list.
  11. Now test it out!

I'll include the current contents of my MyUDFs file here for my own use and yours. I'll try to keep it updated.

I moved my Excel UDFs to GitHub so I can keep them up to date better.

Trying a new blog

I thought I should put something for real up on this handziuk.com domain so here it is! I am making my own blog! well...sort of. I am trying out this blog software called BlogEngine.net. Pretty cool so far!