Pass Object Between Functions

[jsknight1969]

here is you script and it works

Code:

DoTheBizz "c:\test.xls"

Function DoTheBizz (path)
    On Error Resume Next
    Dim funcReturn
    Dim savePath

    savePath = path

     Set funcReturn = CreateExcelSht    '<====Changed this
    
    MsgBox "funcReturn is: " & funcReturn & " and savePath is: " & savePath

    Call WriteExcelSht(funcReturn)

    Call CloseExcelSht(funcReturn, savePath)

End Function


Function CreateExcelSht

    On Error Resume Next

    Dim excelObj
    Dim msg, title

    title = "Excel Reporting"

    Set excelObj = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
        MsgBox "Error in Create Object, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Obj set to: " & excelRptSht & " and failed"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.Visible = True
    If (Err.Number <> 0) Then
        MsgBox "Error in show excel, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Excel visibility is: " & excelObj.Application.Visible & " and failed"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.Workbooks.Add
    If (Err.Number <> 0) Then
        MsgBox "Error in create Workbook, Number: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Excel workbook create"
        Reporting msg, title, micFail, DETAIL
    End If

    MsgBox "In CreateExcelSht, excelObj is: " & excelObj
    Set CreateExcelSht = excelObj   '<====changed this

End Function


Function WriteExcelSht (ByVal excelObjIn)

    On Error Resume Next

    Dim excelObj
    
    Set excelObj = excelObjIn   '<===Changed this
    
    MsgBox "In WriteExcelSht, excelObjIn is: " & excelObjIn & " and excelObj (the function version) is: " & excelObj

    excelObj.Visible = true
    excelObj.Workbooks.Add
    excelObj.Cells(1,1).Value = "Random Schtuff"
    If (Err.Number <> 0) Then
        MsgBox "Error in write to excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to write"
        Reporting msg, title, micFail, DETAIL
    End If

End Function

Function CloseExcelSht (excelObj, savePath)

    excelObj.ActiveWorkbook.SaveAs savePath
    If (Err.Number <> 0) Then
        MsgBox "Error in save of excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to save"
        Reporting msg, title, micFail, DETAIL
    End If

    excelObj.ActiveWorkbook.Close
    If (Err.Number <> 0) Then
        MsgBox "Error in quit of excel: " & Err.Number & ". Desc: " & Err.Description & " and source: " & Err.source
        Err.Clear
        msg = "Error trying to quit"
        Reporting msg, title, micFail, DETAIL
    End If

    Set excelObj = Nothing

End Function

In my previous post, I said you had to set your object type, which is correct in some scenerios/languages, but in this one you just needed a few Set commands to pass the object ByVal instead of ByRef (default).

---

oops left some test code in the script.

Code:

'excelObj.Visible = true
    'excelObj.Workbooks.Add
    excelObj.Cells(1,1).Value = "Random Schtuff"

please comment or remove the extra visible and Add lines. The code still works without errors.