I have an Excel function that populates a dictionary with information from a SQL pull. To help visualize the answer set, I had it currently dumping into a new workbook - and while I don't need to visualize it anymore, I still find it helpful to populate.
The answer set doesn't change unless I myself have done something in the database populating it, so I don't need the function to perform the query every time. Therefore, once the dictionary is populated, I am bypassing the query unless I force it to initialize the dictionary with a refresh parameter.
The module is structured as follows:
Option Explicit
Option Compare Text
Private dProducts As Scripting.Dictionary
------
Function ProdLookup(sValue As Variant, sReturn As Variant, sLookupType As
Variant, _Optional iVendor As Integer, Optional bRefresh As Boolean) As
Variant
If sValue = "" Then
ProdLookup = ""
Exit Function
End If
If sLookupType = "SKU" Then
If (dProducts Is Nothing) Or (bRefresh = True) Then
Call Create_dProdsBySKU
End If
ProdLookup = dProducts(CStr(sValue.Value))(CStr(sReturn.Value))
Exit Function
End If
End Function
------
Sub Create_dProdsBySKU()
Dim newBook As Workbook
Set newBook = Workbooks.Add
'Rest of code to create query, run it, retrieve results, dump onto
'newBook, and populate into dProducts
newBook.Close SaveChanges:=False
End Sub
If I simply run Create_dProdsBySKU from within the Editor, the dictionary populates onto a new workbook, and closes. If I use the ProdLookup function within Excel, however, it never creates a new workbook - and if I put a watch on newBook, it shows it's got a value of ThisWorkbook.
Attempting to see the properties of newBook in the Watch window hangs Excel and I need to End Task from the Task Manager.
What am I missing?
If I use the ProdLookup function within Excel
If you are using the function as a UDF, it will not be permitted to create a new workbook. UDFs are limited to only returning a value to the cell containing the function call.