VBA7 Compile Error Excel Windows 7

Question

Suppose I have a custom library (.dll) called; Library.dll

Within the library, there is a class called; class

I generate an object called testObject:

Dim testObject As New Library.Class

There is a function called testFunction:

Function testFunction(var1 As Double, var2 As Double, var3 As Double, var4 As Double, _
var5 As Double, var6 As Integer, var7 As Double, var8 As Double, var9 As Double)
    Call testSetup(var1, var2, var3, var4, var6, var7, var8, var9)
    testFunction = testObject.Field(var5)
End Function

This function calls testSetup:

Sub testSetup(var1 As Double, var2 As Double, var3 As Double, var4 As Double, _
var5 As Double, var6 As Integer, var7 As Double, var8 As Double, var9 As Double)
    testObject.Lat1 = var1
    testObject.Lon1 = var2
    testObject.Lat2 = var3
    testObject.Lon2 = var4
    testObject.mth = var6
    testObject.GMT = var7
    testObject.ssn = var8
    testObject.icf = var9
End Sub

The problem I have is that upon running, if I place a break point at the location

testObject.Lat1 = var1

Upon running and stepping over to the next line, if I hover over the object

testObject.Lat1

I see the error

object variable or With block variable not set

There is NO Crash and no explicit error message causing a fail.

Anyone have any ideas?

I'm not sure if the error is within the library or the code?

Also it's run from excel by typing into a cell =testFunction(var1, var2, var3....)

Edit: More info in comments

sorry, I don't really understand what you mean? I've declared testObject As Library.Class up the top of the code and then set testObject = New Library.Class in the sub testSetup — user2111939

Peter Albert Peter Albert · Accepted Answer · 2013-02-27T19:28:35

In VBA you can initiate an object via two methods:

Dim o as New Object
Dim o as Object: Set o = New Object

The difference is that in version one the object is initiated the first time it is access in the code! (Note: This is different to VB.NET). In the second case it gets initiated explicitly when your run the Set o = New Object.

Therefore, when you set your breakpoint in the line testObject.Lat1 = var1, testObject is actually not yet allocated and the debugger can therefore not access anything! Only after executing this line will you see a result.

If this bothers you, you simply need to add

Set testObject = new Library.Class

VBA7 Compile Error Excel Windows 7

2 Answers