DeeKayy90 DeeKayy90 - 1 year ago 94 Question

VBA - Using .NET class library

We have a custom class library that has been built from the ground up that performs a variety of functions that are required for the business model in place. We also use VBA to automate some data insertion from standard Microsoft packages and from SolidWorks.

To date we have basically re-written the code in the VBA application macro's, but now are moving to include the class library into the VBA references. We've registered the class library for COM interop, and made sure that it is COM visible. The file is referencable, we have added the

<ClassInterface(ClassInterfaceType.AutoDual)> _
tag above each of the Public Classes, so that intellisense 'works'.

With that said, the problem now arises - when we reference the class library, for this instance let's call it
, it is picked up and seems to work just fine. So we go ahead and try a small sample to make sure it's using the public functions and returning expected values:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim test As New Test_Object.Formatting
Dim t As String
t = test.extractNumber("abc12g3y45")
Target.Value = t
End Sub

This works as expected, returning 12345 in the selected cell/s.

However, when I try a different class, following the exact same procedure, I get an error (
Object variable or With block variable not set
). Code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim test As New Test_Object.SQLCalls
Dim t As String
t = test.SQLNumber("SELECT TOP 1 ID from testdb.dbo.TESTTABLE") 'where the string literal in the parentheses is a parameter that is passed.
Target.Value = t
End Sub

This fails on the
t = test.SQLNumber
line. It also fails on another function within that
class, a function that returns the date in SQL format (so it is not anything to do with the connection to the database).

Can anyone assist in what could be causing this error? I've googled for hours to no avail, and am willing to try whatever it takes to get this working.


EDIT: (added in the

Function SQLNumber(query As String) As Double
Dim tno As Double
Using SQLConnection As SqlConnection = New SqlConnection(Connection_String_Current)
SQLCommand = New SqlCommand(query, SQLConnection)
tno = SQLCommand.ExecuteScalar
End Using
Catch ex As System.Exception
End Try
Return tno
End Function

For comparison, the

Function extractNumber(extstr As String) As Double
Dim i As Integer = 1
Dim tempstr As String
Dim extno As String = ""
Do Until i > Len(extstr)
tempstr = Mid(extstr, i, 1)
If tempstr = "0" Or tempstr = "1" Or tempstr = "2" Or tempstr = "3" Or tempstr = "4" Or tempstr = "5" Or tempstr = "6" Or tempstr = "7" Or tempstr = "8" Or tempstr = "9" Or tempstr = "." Then
extno = extno & tempstr
End If
i = i + 1
If IsNumeric(extno) Then
Return CDbl(extno)
Return 0
End If
End Function

Answer Source

With the help of vba4all, we managed to delve down right to the issue.

When I tried to create a new instance of an object using Dim x as new Test_Object.SQLCalls, I was completely oblivious to the fact that I had not re-entered this crucial line: <ClassInterface(ClassInterfaceType.None)> _.

Prior to doing this, I had this in my object explorer which has both the ISQLCalls and SQLCalls in the Classes section enter image description here

But wait, ISQLCalls isn't a class, it's an interface!

By entering the <ClassInterface(ClassInterfaceType.None)> _ back in the SQLCalls class, the object explorer looked a bit better:

enter image description here

And low and behold, I could now create a new instance of the class, and the methods were exposed.

tldr: I needed to explicitly declare the interface and use <InterfaceType(ComInterfaceType.InterfaceIsDual)> on the interface and <ClassInterface(ClassInterfaceType.None)> on the class.

Many thanks to vba4all, who selflessly devoted their time to assist in this issue.