VB.NET passing params as ByRef thru Excel.App Run doesn't update variables when using Excel.Application but works when using Object

Viewed 154

I have a class in VB.NET that runs an Excel App. The class has a property Excel.Application that returns the instance of the Excel Workbook/App.

Private m_ExcelProgramHandle As Microsoft.Office.Interop.Excel.Application

' Sets the Excel.App Instance
m_ExcelProgramHandle = somefunc()

Public ReadOnly Property ExcelProgramHandle() as Microsoft.Office.Interop.Excel.Application

     Get
          ExcelProgramHandle = m_ExcelProgramHandle
     End Get

End Property

Now, I want to call a macro within the Excel Application from the VB.NET App to pass and update some variables.

Here is the VBA Macro:

Public Function pass_string_byref(byref str as String) as Integer
     str = "abcde"
     pass_string_byref = 1
End Function

Public Function pass_arr_byref(byref arr() as String) as Integer
     ReDim arr(3)

     arr(1) = "a"
     arr(2) = "b"
     arr(3) = "c"

     pass_arr_byref = 1
End Function

Here is how VB.NET calls the macro:

strArr = New String() {""}

retShort = sampleObj.ExcelProgramHandle.Run("pass_string_byref", str)

retShort = ssampleObj.ExcelProgramHandle.Run("pass_arr_byref", strArr)

But the variables doesn't get updated. However, if I change the data type of the property from Excel.Application to Object then it works.

Private m_ExcelProgramHandleAsObj As Object

' Somewhere in the Constructor
' This is how its being set
m_ExcelProgramHandleAsObj = m_ExcelProgramHandle

Public ReadOnly Property ExcelProgramHandle() as Object

     Get
          ExcelProgramHandle = m_ExcelProgramHandleAsObj
     End Get

End Property

Can someone help explain why this happens?

Is there anyway I could use Microsoft.Interop.Excel.Application instead of Object? Other members of the class are using the ExcelProgramHandle property so changing it to Object might break the class. Thus, I think its better to use Excel.Application.

Thanks!

0 Answers
Related