3

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!

mizzitdk
  • 31
  • 2
  • Please provide a [mcve]. – mjwills Dec 07 '20 at 04:12
  • 1
    [`Application.Run`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.run) cannot pass through byref arguments and passes them byval using the current provided values. This used to be documented but apparently not any more with the modern dumbed down help. – GSerg Dec 07 '20 at 08:02
  • @GSerg thanks for the info. I was trying to migrate classes built on VB6 to .NET. Apparently, this method of passing variables with ByRef from the VB6 App seems to work. I was assuming the behavior will be the same in .NET. But its also weird why it works if I use Object instead of Excel.App as mentioned on my workaround. I hope you can share some thoughts on this. Thanks! Appreciate it! – mizzitdk Dec 07 '20 at 08:56
  • 1
    Indeed, casting `Application` to Object does allow passing arguments byref via `Application.Run`, internally in Excel too. I don't know why it is happening, but it really shouldn't. – GSerg Dec 07 '20 at 09:08
  • 1
    I can only speculate that this is the same kind of [`VT_BYREF` shenanigans](https://stackoverflow.com/a/31637346/11683), but then I wonder why it would happen that way from .NET too. – GSerg Dec 07 '20 at 09:14
  • @GSerg thanks for taking your time in answering my questions. It seems that we were assuming that ByRef was always been supported by Application.Run since we were able to use it, but not until I encountered the issue. Anyway, thanks for the explanation. Appreciate it! – mizzitdk Dec 08 '20 at 09:22

0 Answers0