Jump to content
Sign in to follow this  

Use Classes Created in Excel

Recommended Posts


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)

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

Share this post

Link to post
Share on other sites

Thanks for sharing. :D

Share this post

Link to post
Share on other sites

Sneaky! I like it :D

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this