Use Classes Created in Excel

[zfisherdrums]

Admittedly, this topic may be of questionable value for most, but someone may have need of it in the future.

At my work, we have a significant amount of effort sunk into an Excel spreadsheet. This spreadsheet was augmented with classes that used workbook data to call out web services. In addition, the domain we were working in (testing financial software) required that these workbooks be usable by actuaries. In other words, the functionality we needed had to be built into a single deliverable file. As the project progressed, it became apparent that we could leverage these classes in our other testing efforts - those that use scripting languages.

Now that you know why I'd be suggesting this path - as opposed to programming something in C# - here is how one might proceed to expose these objects to outside interests.

In Excel, open the VBA Editor (alt-F11) and create a class.

You will need to set the class 'Instancing' property to "2 - PublicNotCreatable".

Inside the class, we'll create a method called "Test"

Public Sub Test(sArg As String)
    MsgBox sArg
End Sub

This class would contain the functionality you want to expose to outside consumers, e.g. an AutoIT Script.

Now, create a module. Inside the module, create a function similar to this:

Public Function MyClassFactory() As MyClass
    Set MyClassFactory = New MyClass
End Function

This is required because our Class cannot be directly created.

This function is responsible for creating an instance and returning it.

Save the workbook. In this example, I saved it to my desktop as "test.xls".

Below is an example of how to use the class in AutoIt.

#include <excel.au3>

$oXl = _ExcelBookAttach( @DesktopDir & "\test.xls" )

If Not IsObj( $oXl ) Then
    ConsoleWrite( "FAIL" & @CRLF)
    Exit
EndIf

Dim $obj = $oXl.Application.Run( "MyClassFactory" )
$obj.Test( "Hello, World" )

[picaxe]

Thanks for sharing.

[Fulano]

Sneaky! I like it