Make Workbook global or send WorkSheet to Function

[Hubert_Los]

Hello,

How can I send WorkSheet (Excel) to Function?

Second option is making workbook global, but I can’t do it.

I added "Microsoft Access 16.0 Object Reference Library"

Public Sub Main()
    
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks
    Set objWorkbook = oBook.Open("E:\test.xlsx")
    Set oSheet = objWorkbook.Worksheets(1)
    
    Call Save_to_excel(ref_number, "A", oSheet )

    objWorkbook.Save
End Sub

Sub Save_to_excel(oSheet As WorkSheet)
    
    'do sth
    
End Sub

[WCrihfield · Accepted solution]

Hi @Hubert_Los.  I see that in the line within your Sub Main block of code, where you are 'calling' the other Sub to run, you are supplying 3 input objects, but the Sub itself is only designed to receive 1 input object.  You either need to not include the first 2 input objects in your line of code that is calling the Sub to run; or you need to include 2 more expected input variables of the same Type as the ones you are trying to supply to it, in the same order as the ones you are trying to supply to it.  Also, since you mentioned that you included the Excel object library, why are you not declaring the variables as their native Excel Type of objects (Excel.Application object, Excel.Workbook object, Excel.Worksheet object)?  I see that you are declaring the variable's Type as Worksheet within the Sub routines definition line, instead of Object, so why not the others similarly?

[Hubert_Los]

"Sub to run, you are supplying 3 input objects, but the Sub itself is only designed to receive 1 input object."

I copied the wrong part of the code, my mistake.

" Also, since you mentioned that you included the Excel object library, why are you not declaring the variables as their native Excel Type of objects (Excel.Application object, Excel.Workbook object, Excel.Worksheet object)"

It works, thank you