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.
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)
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:
Dim c As Range
For Each c In WorksheetFunction.Index(Range("NameList"), 0, 2)
c.Offset(0, 1) = "'" & Names(c.Value).RefersTo
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.
If IsNull(Me.Photo) Or Len(Me.Photo) = 0 Then
'no image; load an image before showing
Me.Parent!imgDisplay.Picture = Me.Photo
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.
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)
.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)
'copy to the destination directory
FileCopy vrtSelectedItem, SAVE_PATH & strFname
‘optional: delete the original, keeping only the copy.
‘write the full path to the Photo field
Me.Photo = SAVE_PATH & strFname
'The user pressed Cancel.
'Set the object variable to Nothing.
Set fd = Nothing
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.