How to speed up VBA custom function?

[ChuxiLiu]

Hello there,

I am struggling with a custom function I wrote in the VBA. The problem is that this function create new Excel.Application instance inside it that will open excel workbook from another directory (to extract some values). Although it works fine, it takes quite some seconds to extract these values. I am wondering, is there a method that can drastically speed up this function? The excel has many formula that calls this function, and it takes roughly 1 hr to save the workbook (due to formula auto-calculate and value updating). I am attaching this function below for reference. Thank you very much!

Public Function Ext(Ref As String) As String

    Dim excelApp As New Excel.Application
    excelApp.Visible = False
    Dim book As Workbook
    Dim xSht As Worksheet
    Dim Val As String
    
    openPos = InStr(Ref, "'")
    closePos = InStr(Ref, "]")
    bookName = Mid(Ref, openPos + 1, closePos - openPos - 1)
    bookName = Replace(bookName, "'", "")
    bookName = Replace(bookName, "[", "")
    bookName = Replace(bookName, "]", "")
    openPos = InStr(Ref, "]")
    closePos = InStr(Ref, "!")
    sheetName = Mid(Ref, openPos + 1, closePos - openPos - 1)
    sheetName = Replace(sheetName, "'", "")
    charCt = Len(Ref)
    cellName = Right(Ref, charCt - closePos)
    Set book = excelApp.Workbooks.Open(bookName)
    Set xSht = book.Worksheets(sheetName)
    Val = xSht.Range(cellName).Value
    book.Close savechanges:=False
    excelApp.Quit
    Ext = Val

End Function

[6StringJazzer]

This will run once for every formula it is used in, so that is going to take a lot of time.

First, it is not necessary to create a new instance of Excel to do this. That adds a lot of overhead.

Second, it is probably not necessary to actually open the Excel file. You can build a formula that references the cell in the closed workbook, instead of opening the file to retrieve the value.

Please give examples of the formulas where you use this function in your worksheet and I can be more specific about a solution.

[ChuxiLiu]

Thank you 6StringJazzer for the help! Below I have attached an example where I used this function (some sensitive information are blurred out, they are workbook/sheet name in the formula). In addition, note that all highlighted cyan cells also use this Ext function. How can I implement the 2 methods you mentioned through this example? I really appreciate your assistance!

Attachment 757518

Attachment 757519

[MrShorty]

Your attachments seem to have failed to attach correctly. Be sure to follow the instructions in the yellow banner at the top of the page when attaching files (including pictures).

I'm going to agree with 6StringJazzer -- creating Excel instances and opening and closing the files are major "slow" steps. I expect that a big part of speeding this up will be figuring out ways to accomplish the same thing without opening and closing files. In addition to getting your uploads to work correctly, I think it might be important to understand the overall goal of your spreadsheet(s), how you intend to interact with them, and what limitations and restrictions (and any other programming requirements) for the project. Things that come to my mind include -- is it necessary for the files to be closed? Is it necessary to keep the different files separate, or could the be compiled into a single, good database? Are you required to use VBA, or would you entertain a Power Query type of solution (or some other solution that involves completely revamping and restructuring your data storage and data querying operations)?

[ChuxiLiu]

Hi MrShorty,

Due to this project management, I am afraid I can only use the VBA option. The other options you mentioned are not available.
I am also thinking if there is a workaround without opening the workbook, but I searched this before and couldn't find any useful information.

BTW, I attached the screenshot pictures in the previous reply, see if you can view them now.

Deeply appreciate the help!

[6StringJazzer]

There is not enough information to provide a completed solution. In particular I need to know how you determined that in the formula you showed, the file name was located in cell A2. You did not show column A in your picture. If you have a formula like this in every cyan cell you need to explain how the applicable file name is determined for each cell.

Similarly you have B6 and a sheet name hardcoded into the string. Is this different for different cells? If so, what determines them?

Here is the strategy but I can't give a complete solution without more information. This uses the same file name, sheet name, and cell range for every formula, just as an example:

Public Sub PopulateFormulas()

    Dim BookName As String
    Dim CellName As String
    Dim Cell As Range
    
    BookName = [A2]
    SheetName = "Blurred Out Sheet" ' as shown in example
    CellName = "B6" ' as shown in example
    
    For Each Cell In Range("B6,B8:M9,B10:M11,B12,D13:E13,G13:H13,J13:K13,M13,B14,E14,H14,K14")
       Cell.Formula = "=" & "'C:\Users\cxliu\Desktop\HARRY\[" & BookName & ".xlsx]" & SheetName & "'!" & CellName
    Next Cell
    
End Sub

This creates formulas that refer directly to the external file, which are effective even if the file is closed. This will be 100 times faster than what you are doing now.

[ChuxiLiu]

Thank you 6StringJazzer!

I think your solution is a perfect workaround and it inspired me to modify my code! I will try it.