From: Tammo Freese <tammo.freese@...>
Date: 2001-05-29T18:28:51+09:00
Subject: [ruby-talk:15918] OT: Problems with Win32OLE (long)


Hello all,

I currently have to work on development/maintenance of a VBA Excel
application.
As testing is a real pain in VBA, I want to use Ruby for unit testing the
VBA classes.

First of all, I started Excel and the VBA editor in Excel.
I defined a SpikeClass:

---------------------------------------
Option Explicit

Dim aReference As SpikeClass
Dim aName As String

Public Function returnMessage() As String
    returnMessage = "Test 123"
End Function

Public Sub printMessageInDebug(message As String)
    Debug.Print message
End Sub

Public Sub printVariantMessageInDebug(message As Variant)
    Debug.Print message
End Sub

Public Property Get MyReference() As SpikeClass
    Set MyReference = aReference
End Property

Public Property Set MyReference(value As SpikeClass)
    Set aReference = value
End Property

Public Property Let MyReference2(value As Variant)
    Set aReference = value
End Property

Public Property Set MyReference3(value As Object)
    Set aReference = value
End Property

Public Sub setReference(value As SpikeClass)
    Set aReference = value
End Sub

Public Sub setVariantReference(value As Variant)
    Set aReference = value
End Sub

Public Property Get name() As String
    name = aName
End Property

Public Property Let name(value As String)
  aName = value
End Property
---------------------------------------

For accessing this class, I defined a kind of factory method in a module:

---------------------------------------
Option Explicit

Public Function createSpikeClass() As SpikeClass
    Set createSpikeClass = New SpikeClass
End Function
---------------------------------------

Now I saved the whole stuff in SpikeClass.xls, and began testing in irb:

  irb(main):070:0> excel = WIN32OLE.connect("Excel.Application")
  #<WIN32OLE:0xa0b0750>
  irb(main):071:0> spike = excel.run("SpikeClass.xls!createSpikeClass")
  #<WIN32OLE:0xa09ea38>
  irb(main):072:0> spike.returnMessage
  "Test 123"

  ...

The problems:

- Property Set does not work. Both MyReference and MyReference3 can be
  called, but the reference returned by Property Get MyRefernce
  is nil. Property Let seems to work with all types, also with objects
  (MyReference2 sets the object refernce correctly).

- Only Variants seem to work as arguments to method calls. printMessageInDebug
  does not work, as well as setReference.

As I am no OLE specialist, is there a reason for this?
Or are this bugs in the Win32OLE implementation?

-
Tammo Freese
freese@acm.org