When a UDF looks up data from an Access table, Excel can end up calling the function hundreds or even thousands of times during recalculation. Without a cache, each call hits the database (a.k.a. server trips), opening connections, running queries, and slowing things down unnecessarily. By loading the product table into a VBA Dictionary the first time the UDF runs, all subsequent lookups happen.
This approach takes full advantage of the huge amounts of RAM packed into modern PCs, letting Excel access data lightning-fast. Even with just 151 products, caching can slash recalculation time and make your workbook feel far more responsive.
Below I’ve taken the UDF GetMaterial I demonstrated in My blog post: When Excel is Overwhelmed: Access Excel Integration and I’ve modified it using a sub routine named: LoadProductCache to cache the data so there will be only one trip to the server (access db).
Warning, I should really say “Good News”, there really is very little code there — I’ve over annotated it so you can follow what’s going on. In the download below, I took the previously offered code and added caching to all the UDFs to demonstrate how easy caching is to implement and how much it will improve performance.
Private ProductCache As Object 'Dictionary
'==========================================================
' Developer: Ray Mills
' Contact Phone: 484 574 3190
' Contact Email raymills.developer@gmail.com
' UDF: GetMaterialNameCached
'==============================================================
' UDF: GetMaterialNameCached
' Purpose: Returns a product name from Access using a cache
'
' HOW THE CACHE WORKS:
' 1. First time the UDF runs, the cache is EMPTY.
' 2. We detect this and load ALL products into the Dictionary.
' 3. Subsequent calls DO NOT touch Access at all.
' 4. Lookups are now instant (memory dictionary lookup).
'==============================================================
Public Function GetMaterialNameCached(Material_ID As Long) As String
On Error GoTo ErrHandler
'----------------------------------------------------------
' Step 1: Make sure the cache exists
' If this is the very first call in the session, create it.
'----------------------------------------------------------
If ProductCache Is Nothing Then
Set ProductCache = CreateObject("Scripting.Dictionary")
LoadProductCache < ' <-- Load all products from Access
End If
'----------------------------------------------------------
' Step 2: Lookup directly from the Cache (dictionary)
' If the ProductID exists, return instantly.
'----------------------------------------------------------
If ProductCache.Exists(ProductID) Then
GetMaterialNameCached = ProductCache(ProductID)
Else
GetMaterialNameCached = "Not Found"
End If
Exit Function
ErrHandler:
GGetMaterialNameCached = "#ERROR"
End Function.
'==============================================================
' SUPPORT ROUTINE: LoadProductCache
' Loads ALL ProductID/ProductName pairs from Access ONCE
'==============================================================
Private Sub LoadProductCache()
Dim cn As Object
Dim rs As Object
Dim sql As
' SQL to get all products
sql = "SELECT Material_ID, Material_Name FROM Products"
' Create and open ADODB connection to Access
Set< cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyDB.accdb"
' Execute the query
Set rs = cn.Execute(sql)
'----------------------------------------------------------
' Loop through the recordset and add each Product id/ProductName
' pair to the Dictionary (cache)
'----------------------------------------------------------
Do While Not rs.EOF<
' Add Material_ID as key, Material_Name as value '
ProductCache(rs.Fields("Material_ID").Value) = rs.Fields("Material_Name").Value
rs.MoveNext
Loop
Cleanup:
' Clean up objects
On Error Resume Next
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
If Not cn Is Nothing Then cn.Close
Set cn = Nothing
Exit Sub
ErrHandler:
' If something goes wrong, clear the cache so UDF can fail gracefully
Set ProductCache = Nothing
Resume Cleanup
End Sub
The included Access database and Excel workbook show exactly how to:
- Replace volatile formulas and large lookup ranges with lean, cached functions
- Eliminate repeated database hits by leveraging in-memory result caching Maintain data integrity in Access while keeping Excel fast, light, and responsive
- Use Excel as a clean front-end without sacrificing performance
