Excel VBA Load worksheet in function

Question

I am trying to write a function to find the first row of an outer sheet. I can do this with sub, but it does not work as a function. It can be done.

I am currently using

Function GetLine(fileName As String) As Boolean GetLine = 0 Dim loadBook As Workbook If loadBook = Application.Workbooks.Open(fileName) Then GetLine = True Else GetLine = False End If end function 

I get a #value return. I think this is due to an error loading the book.

I ask for advice and thank you.

Answer 1

That's right, the performance of user-defined functions has certain limitations. There are several tricks in UDF to do what you want, rather than disrupt the order.

1. Get one more instance of Excel.Application through late binding , open the workbook with it, do all the necessary calculations, referring to the instance. It is imperative to refer to the instantiated instance, so some nested With ... End With statements or optional e syntax. BC .Cells() , .Sheets() may seem unusual. There is an UDF example of how to get the first row in a worksheet from a closed file:

 Function GetFirstRowLbind(FileName, SheetName) ' UDF function that calculates value, works with certain limitations On Error Resume Next With CreateObject("Excel.Application") ' late binding .Workbooks.Open (FileName) GetFirstRowLbind = .Sheets(SheetName).UsedRange.Row .Quit End With End Function 

OERN used only to skip errors, such as a missing file and others, so that the .Quit statement .Quit executed to prevent memory leak, otherwise running excel processes will remain in memory after each recalc and UDF call.
<br> 2. Introduce some UDFs, expanding by planning to another procedure the actions that should be performed after the completion of the UDF , with execution based on the recalc event of the sheet. This method is more complicated and difficult to debug, but more flexible, and it makes it possible to do much more "inside" UDF, for example, change neighboring cells or even any available data throughout the application. Schedule example:

Put the code in one of the VBAProject modules:

 Public Tasks, Permit, Transfer Function GetFirstRowSched(FileName, SheetName) ' UDF function, schedules filling this UDF cell with a value after all UDFs to be completed If IsEmpty(Tasks) Then TasksInit If Permit Then Tasks.Add Application.Caller, Array(FileName, SheetName) ' pack arguments to array, the dictionary key is actually this cell object GetFirstRowSched = Transfer End Function Sub TasksInit() ' function for initial setting values Set Tasks = CreateObject("Scripting.Dictionary") Transfer = "" Permit = True End Sub Function GetFirstRowConv(FileName, SheetName) ' function that actually calculates the value, runs without UDF limitations like an usual function With Application.Workbooks.Open(FileName) GetFirstRowConv = .Sheets(SheetName).UsedRange.Row .Close End With End Function 

Put the code in the ThisWorkbook section of Microsoft Excel objects in VBAProject:

 Private Sub Workbook_SheetCalculate(ByVal Sh As Object) ' sheets recalc event that perform all scheduled calls, puts data to each of UDFs cells Dim Task, TempFormula If IsEmpty(Tasks) Then TasksInit Application.EnableEvents = False Permit = False For Each Task In Tasks ' cycle trough all stored cell objects TempFormula = Task.FormulaR1C1 Transfer = GetFirstRowConv(Tasks(Task)(0), Tasks(Task)(1)) ' unpack arguments from array to perform calculations Task.FormulaR1C1 = TempFormula Tasks.Remove Task Next Application.EnableEvents = True Transfer = "" Permit = True End Sub 

Answer 2

If UDF is called in a cell, it can return a value only to that cell. He cannot open another book.