CBlog(handziuk)

Function MyNewFunction()
    a = 5 * 7
    MyNewFunction = a
End Function

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.

    Private Sub VeryifyBackendIsInPlace()
        Dim rst As DAO.Recordset
        Dim ITEM_NOT_FOUND_IN_COLLECTION_ERROR As Integer, FILE_NOT_FOUND_ERROR As Integer
        Dim INVALID_PATH_ERROR As Integer, NETWORK_CONNECTION_ERROR As Integer
       
        ITEM_NOT_FOUND_IN_COLLECTION_ERROR = 3265
        FILE_NOT_FOUND_ERROR = 3024
        INVALID_PATH_ERROR = 3044
        NETWORK_CONNECTION_ERROR = 3043
        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
        rst.Close
        On Error GoTo 0
        Exit Sub
    FindBackEndFile:
       
        AttachToAnotherDataFile
       
    End Sub

    Public Function AttachToAnotherDataFile() As Boolean
        On Error GoTo 0
        Dim ofd As FileDialog
        Dim result As VbMsgBoxResult
        Set ofd = FileDialog(msoFileDialogFilePicker)
        ofd.show
        If ofd.SelectedItems.Count = 1 Then
           
            result = RelinkLinedTablesToBackend(ofd.SelectedItems(1))
            If result = vbCancel Then
                AttachToAnotherDataFile = False
            End If
            AttachToAnotherDataFile = True
        Else
            AttachToAnotherDataFile = False
        End If
    End Function

    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
                    db.TableDefs(tdf.Name).RefreshLink
                    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
            Next
           
        Set tdf = Nothing
        Set db = Nothing
    End Function

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.

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.