Renaming ranges in VBA

Recently a question came up on an Excel forum. The request was to rename hundreds of named ranges in VBA. The simplest option is to create an array from a 2-column table and loop through the array, making changes to each name in the first column. The table is called NameList. Populate and name the table (old names in the first column), paste this code into a new module, and run it.

Note: If you haven’t created a named range before,  follow the steps below.

1. Select the data (not the headings), highlighted here in blue


2. Click the Name Box (just to the left of the formula bar), type the name (no gaps) and press Enter.

Sub NameChanger()
    Dim arNames()
    Dim nm As Name
    Dim i As Integer

    arNames = Range("NameList").Value
    For i = LBound(arNames) To UBound(arNames)
        For Each nm In ActiveWorkbook.Names
            If nm.Name = arNames(i, 1) Then
                nm.Name = arNames(i, 2)
            End If
        Next nm
    Next i
End Sub

You can confirm the names changes by pressing F3 and then Alt+L (on a blank sheet).
Optional extra: If you want to check the range references (and list them next to the NameList table) paste this to your module and run it:

Sub NameRefs()
    Dim c As Range

    For Each c In WorksheetFunction.Index(Range("NameList"), 0, 2)
        c.Offset(0, 1) = "'" & Names(c.Value).RefersTo
    Next c
End Sub

A simple method for linking and viewing images in Access

Access has traditionally not been a good environment for storing media, images and files due to database bloating. Because of that many people prefer to store images in the file system and link to those files from Access. The approach shown below is just one of many methods, and has these features:

  • Files are stored in a predefined directory
  • The images are located / stored using a button on a subform. They are displayed in an unbound image frame on the main form.
  • The same button is used to browse for and display a new image, and to display an existing image.
  • Images are located on disk using the FileDialog object. Once selected you can open, save, rename or otherwise modify the file.

You will need to set up the following to use this code:

  • A command button on a subform, with the code shown in Listing 1.
  • A text field in the subform, named Photo
  • An unbound image frame in the main form, called imgDisplay. When you set up this control you will be prompted to browse for an image. Whatever you select will become the default image.
  • You will also need to save Listing 2 in the code module of the subform.

Listing 1

    If IsNull(Me.Photo) Or Len(Me.Photo) = 0 Then
        'no image; load an image before showing
        GoTo TryAgain
        Me.Parent!imgDisplay.Picture = Me.Photo
    End If

The If statement checks first to see whether the Photo field is empty. If it is the CopyImage function launches, prompting the user to locate an image for the record. Once the image is copied and the link written, the code returns to the top of the routine and loads the link into the image container.

Listing 2

Private Function CopyImage()
    ''You will need a reference to the Microsoft Office x.0 Object Library
    '' where x = 10,11,12 or 13

    Dim fd As Office.FileDialog
    Const SAVE_PATH = "H:\Documents\Images\" 'default folder for saving the images
    Dim strFname As String
    Dim i As Integer
    Dim vrtSelectedItem As Variant
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Add "All files", "*.*"
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1
        .AllowMultiSelect = False
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                strFname = ""
                For i = Len(vrtSelectedItem) To 1 Step -1
                    If Mid(vrtSelectedItem, i, 1) = "\" Then
                        strFname = Mid(vrtSelectedItem, i + 1)
                        Exit For
                    End If
                Next i
                'copy to the destination directory
                FileCopy vrtSelectedItem, SAVE_PATH & strFname
                ‘optional: delete the original, keeping only the copy.
                Kill vrtselecteditem
                ‘write the full path to the Photo field
                Me.Photo = SAVE_PATH & strFname
            Next vrtSelectedItem
            'The user pressed Cancel.
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
End Function

Regional settings cause report to fail in Access

Here’s a weird problem that I encountered recently. In a database that I had developed for a client, one of the reports refused to run when they switched their regional settings to another language — a problem, since some of the users were German and hence would be unable to use the report without changing back to English settings first.

The problem came down to the headers that I had used in the crosstab query that fed the report. It had text strings like “01_Mon”, “02_Tue”, etc for a total of 10 headers. And of course, these became fields for the report.

What I hadn’t realised what that Access interpreted these as dates, so when the regional settings changed, they became “01_Mo”, 02_Di”, etc … and the report broke. The fix was simple: use generic “Day_01”, “Day_02”, etc as the day headers, and update the report controls to read from these generic fields.

I guess it’s another case of crosstab queries and reports having unintended consequences. They are powerful, but they can bite. Here’s hoping they don’t bite you the same way.