CODEWITHSUNDEEP
vbaexcelexcel-2013
mikstravaganza
mikstravaganza

Reputation: 52

Workbooks.Add not adding a new workbook

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?

Upvotes: 1

Views: 819

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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.

Upvotes: 3

Related Questions