1

I am trying to open a workbook, do some stuff, and close the workbook when called from a cell - i.e. using a Public Function.

When I run the code in a sub routine, it works fine. But for some reason when I run it from a cell, passing all parameters exactly the same, it fails to open the workbook.

Is this something that is known to be the case?

Code for sub (works):

Sub testing()
    
    Dim sName As String
    Dim tName As String
    
    sName = [Config.Folder.Core.GS].Value & "\" & [Config.FileName21].Value
    
    Dim wb As Workbook
    Debug.Print sName
    Set wb = Workbooks.Open(sName)
    
    ' opens workbook fine!!!

End Sub

Code for public function (does not work):

Public Function NewCsvWithoutRowsFromXLS(sSourceName As String, sTargetName As String, rowExclusionsList As String, Optional iRowsToDeleteFromTop As Integer, Optional iCharsFromLeft As Long) As String
    
    Application.Volatile True
    
    On Error GoTo EH
    
    Dim wb As Workbook
    
    ' open wb
    Set wb = Workbooks.Open(sSourceName)

    ' fails to set wb object!!!

End Function

Fyi, file names are identical when opening - have double, and triple checked!

Dean
  • 2,326
  • 3
  • 13
  • 32
  • You can't open a workbook with a UDF. Well, you can, with some workarounds, but I'd generally advise against it. – BigBen Oct 07 '21 at 13:13
  • 1
    You cannot change the state of Excel or the Excel model from inside of a function. You can change VBA's state though. – RBarryYoung Oct 07 '21 at 13:14
  • 1
    @BigBen Right, technically, the restriction isn't on what type of VBA code is running (e.g., "functions can't change excel, but subroutines can"), but rather *how VBA* was called from Excel*. If VBA was invoked through a Macro or an Event, then it can change Excel. But if it was invoked through a Formula Function call or through Conditional Formatting , then you are in a restricted execution mode that will not allow the invoking Excel or the Excel Object Model to be changed. – RBarryYoung Oct 07 '21 at 13:25
  • @BigBen The linked article in compliant with my statement above, but is also incomplete. There are many other ways around it if you base it on "type of VBA code". If you base it on *how* the code is invoked, then the rule is pretty consistent. – RBarryYoung Oct 07 '21 at 13:31
  • @BigBen how would I call a sub? I'm not able to as this is triggered from the cell..? – Dean Oct 07 '21 at 13:39
  • Are you trying to get a value from a closed workbook? What is the end goal? – BigBen Oct 07 '21 at 13:40
  • @BigBen we have a custom add in that reads csv files. Before this can be done, I need to clean the csv file and then read it - but this is done as part of the `Application.Calculate` method. The more I think about this, the more I think I will need some other way to invoke this. Logic for the post was to see if this was something that was a known issue, which you and RBarryYoung have answered. – Dean Oct 07 '21 at 13:42

0 Answers0