How to pass and return the variant type between nim and vba.

[xrfez]

I have been experimenting with using a .dll to interact with excel through VBA. I am able to pass most standard types but am struggling with the complexities of the variant type in winim/com. I have successfully passed and returned a BSTR through a variant as well as typed arrays but cannot handle variant arrays. Idealy I would be able to in VBA:

Dim arr as variant
arr = sheets(1).range(sheets(1).cells(1,1),sheets(1).cells(10,4))
arr = nimCall(arr)
range(sheets(1).cells(1,1),Sheets(1).cells(10,4)) = arr

where through nimCall the data from that range could be operated on from nim in an idiomatic way. What would that look like in nim?

proc nimCall*(arr: ptr variant): VARIANT {.exportc, dynlib, stdcall.} =
  ## broken and not idiomatic
  var variantArray = fromVariant[COMArray1D](arr[])
  variantArray[0] = toVariant(10.int32)
  result = toVariant(variantArray)

So far the results of my experiments in VBA and nim where I am able to pass pretty much every other type I can think of:

# nvba.nim
# nim c -d:release --app:lib --noMain --gc:orc  nvba.nim
import winim/com
import winim/lean

proc NimMain() {.cdecl, importc, used.}

proc nimInit*(): void {.exportc, dynlib, stdcall.} =
  NimMain()

proc deinit*() {.exportc, dynlib, stdcall.} =
  COM_FullRelease()
  GC_fullCollect()

proc nimAddTwoNumbers*(a: int32, b: int32): int32 {.exportc, dynlib, stdcall.} =
  result = a + b
  deinit()

proc nimSum*(
    nums: ptr UncheckedArray[int32], n: int32
): int32 {.exportc, dynlib, stdcall.} =
  result = 0
  for number in 0 ..< n:
    result += nums[][number]
  deinit()

type Rectangle = object
  s1, s2: int32

proc nimRectangleArea*(r: ptr Rectangle): int32 {.exportc, dynlib, stdcall.} =
  result = r[].s1 * r[].s2
  deinit()

proc nimRectanglePerimeter*(r: ptr Rectangle): int32 {.exportc, dynlib, stdcall.} =
  result = 2 * (r[].s1 + 2) * r[].s2
  deinit()

proc nimGetRectangle*(s1, s2: int32): Rectangle {.exportc, dynlib, stdcall.} =
  result = Rectangle(s1: s1, s2: s2)
  deinit()

proc nimCallProcedure*(p: proc() {.stdcall.}): void {.exportc, dynlib, stdcall.} =
  p()
  deinit()

proc nimBSTRByVal*(arg: BSTR) {.exportc, dynlib, stdcall.} =
  MessageBoxW(nil, arg, L"BSTR passed ByVal", MB_OK)
  SysFreeString(arg)
  deinit()

proc nimBSTRByRef*(arg: ptr BSTR) {.exportc, dynlib, stdcall.} =
  MessageBoxW(nil, arg[], L"BSTR passed ByRef", MB_OK)
  deinit()

proc nimReturnBSTR*(): BSTR {.exportc, dynlib, stdcall.} =
  result = SysAllocString(L"Hello World!")
  deinit()

proc nimLPSTR*(arg: LPSTR) {.exportc, dynlib, stdcall.} =
  MessageBoxA(nil, arg, "LPSTR", MB_OK)
  deinit()

proc nimLPWSTR*(arg: LPWSTR) {.exportc, dynlib, stdcall.} =
  MessageBoxW(nil, arg, L"LPWSTR", MB_OK)
  deinit()

proc nimPassVariant*(arg: variant) {.exportc, dynlib, stdcall.} =
  if arg.vt == VT_BSTR:
    MessageBoxW(nil, arg.bstrVal, L"BSTR passed ByVal", MB_OK)
    SysFreeString(arg.bstrVal)
  deinit()

proc nimReturnVariant*(): VARIANT {.exportc, dynlib, stdcall.} =
  result.vt = VT_BSTR
  result.bstrVal = SysAllocString(L"Hello World!")
  deinit()

' any module in the editor
Public init As Boolean
Public driveSet As Boolean
Public dirSet As Boolean

Public Declare PtrSafe Sub nimInit _
Lib "nvba.dll" ()

Public Declare PtrSafe Function nimAddTwoNumbers _
Lib "nvba.dll" ( _
ByVal Num1 As Long, ByVal Num2 As Long) As Long

Public Declare PtrSafe Function nimSum _
Lib "nvba.dll" ( _
ByRef Nums As Long, ByVal N As Long) As Long

Public Type TRectangle
    S1 As Long
    S2 As Long
End Type
Public Declare PtrSafe Function nimRectangleArea _
Lib "nvba.dll" (ByRef Rect As TRectangle) As Long
Public Declare PtrSafe Function nimRectanglePerimeter _
Lib "nvba.dll" (ByRef Rect As TRectangle) As Long
Public Declare PtrSafe Function nimGetRectangle _
Lib "nvba.dll" (ByVal S1 As Long, ByVal S2 As Long) As TRectangle

Public Declare PtrSafe Sub nimCallProcedure _
Lib "nvba.dll" (ByVal Func As LongPtr)

Public Declare PtrSafe Sub nimBSTRByVal _
Lib "nvba.dll" (ByVal Arg As String)
Public Declare PtrSafe Sub nimBSTRByRef _
Lib "nvba.dll" (ByRef Arg As String)
Public Declare PtrSafe Function nimReturnBSTR _
Lib "nvba.dll" () As String
Public Declare PtrSafe Sub nimLPSTR _
Lib "nvba.dll" (ByVal Arg As String)
Public Declare PtrSafe Sub nimLPWSTR _
Lib "nvba.dll" (ByVal Arg As LongPtr)
Public Declare PtrSafe Sub nimPassVariant _
Lib "nvba.dll" (ByVal Arg As Variant)
Public Declare PtrSafe Function nimReturnVariant _
Lib "nvba.dll" () As Variant
    
Public Sub useDll()
    ChDrive Left$(ThisWorkbook.path, 1)
    ChDir ThisWorkbook.path
    If init = False Then
        Call nimInit
        init = True
    End If
    If driveSet = False Then driveSet = True
    If dirSet = False Then dirSet = True
End Sub

Public Sub addTwoNumbers()
    Call useDll
    Dim Result As Long
    Result = nimAddTwoNumbers(1, 2)
    Debug.Print Result
End Sub

Public Sub sum()
    Call useDll
    Dim arr() As Long
    ReDim arr(0 To 4)
    
    arr(0) = 1
    arr(1) = 2
    arr(2) = 3
    arr(3) = 4
    arr(4) = 5
    
    Dim ArrSize As Long
    ArrSize = UBound(arr) - LBound(arr) + 1
    
    Dim Result As Long
    Result = nimSum(arr(LBound(arr)), ArrSize)
    Debug.Print Result
End Sub

Public Sub rectangle()
    Call useDll
    Dim Rect As TRectangle
    Rect.S1 = 10
    Rect.S2 = 20

    Dim Area As Long
    Area = nimRectangleArea(Rect)
    Debug.Print "Area: " & Area

    Dim Perimeter As Long
    Perimeter = nimRectanglePerimeter(Rect)
    Debug.Print "Perimeter: " & Perimeter

    Dim Rect1 As TRectangle
    Rect1 = nimGetRectangle(5, 10)
    Debug.Print Rect1.S1 * 2
    Debug.Print Rect1.S2 * 2

End Sub

Public Sub functionCall()
    Call useDll
    Call nimCallProcedure(AddressOf helloWorld)
End Sub

Public Sub helloWorld()
    MsgBox "Hello, World!"
End Sub

Public Sub strings()
    Call useDll
    Dim Message As String
    Message = "Hello, World!"

    Dim VariantMessage As Variant
    VariantMessage = "Hello, World!"

    'Pass BSTR ByVal
    nimBSTRByVal StrConv(Message, vbUnicode)

    'Pass BSTR ByRef
    nimBSTRByRef StrConv(Message, vbUnicode)

    'Return BSTR
    Dim BStrResult As String
    BStrResult = nimReturnBSTR()
    MsgBox StrConv(BStrResult, vbFromUnicode), Title:="Returned BSTR"

    'Pass BSTR ByVal which looks like LPSTR to nim
    nimLPSTR Message

    'Pass pointer to 2-byte character string
    nimLPWSTR StrPtr(Message)

    'Pass variant containing BSTR
    nimPassVariant VariantMessage

    'Return variant containing BSTR
    Dim VariantResult As Variant
    VariantResult = nimReturnVariant()
    MsgBox VariantResult, Title:="Returned Variant BSTR"

End Sub