VBA: Conditional compilation with implements keyword

Question

Is it possible to use compiler conditional constants together with the "implements" keyword, where the interface is in an add-in?

I have the following in a class module in my workbook, let's call it book1:

#Const Condition1 = 0 ''will be replaced with 1 when add-in is opened
#if Condition1 then
    Implements myAddIn.iInterfaceFoo
#End if

I have the add-in myAddIn listed as a reference (i.e. in Tools -> References...).

I'm successfully using the interface with other classes in the add-in, but now I want to call the interface directly in my workbook book1. As long as the add-in is open, when I compile book1 (i.e. Debug -> Compile VBAProject) it compiles successfully.

However, when I try to compile book1 with the add-in closed, I get the error

Compile error: User-defined type not defined

This is precisely what I'm trying to avoid - otherwise if the add-in is missing (for example on someone else's computer) the spreadsheet itself will still work.

Thanks for the feedback, I have attempted to improve the question. I have instantiated the class, but this is a compiler error not a runtime error. Thanks for the link on `implements` but I'm quite comfortable with how it works :) — crunch, Mar 03 '15 at 13:17
Well, when the `Condition1` is false then your code should not even attempt to compile the `Implements myAddIn.iInterfaceFoo`. Do you have a problem setting the `Condition1` to true/false depending whether the add-in is loaded or not? Possibly see [this](http://stackoverflow.com/q/15951518/2140173) and [that](http://stackoverflow.com/q/19726791/2140173) — , Mar 03 '15 at 14:34
That's just it - it does try to compile the `Implements` line, even if `Condition1` is `False`. I'll have a look at those linked answers. — crunch, Mar 03 '15 at 15:39
That's rather a strange behaviour. I haven't been able to reproduce this. For me, when the condition is false, the `Implements` is not compiled. — , Mar 03 '15 at 15:47
Does it still error if you declare the `Condition1` as a conditional compilation argument in the VBProject Properties pane of the add-in workbook? — SierraOscar, Mar 28 '15 at 12:27
Are you implementing an interface on any of Excel's own classes (so on ThisWorkbook, or individual WorkSheets, in particular)? I've had serious trouble caused by doing this - not sure why, but I know my eventual solution was to implement the interface on one of my own classes, and then just store instances of them in Worksheets etc, and all of a sudden the problems went away. — tobriand, Mar 31 '15 at 21:15
Did you ever figure this out? I just had a go at it, and I can NOT get it to enter the conditional when `#Const` is set right before it. I even tried assigning False values to compiler constants like `VB7` and `Win32` to see if I could override them in case of a naming conflict, and sure enough I can force it to evaluate False when it isn't. — Michael Chad, Apr 18 '15 at 01:44
@MichaelChad, no I didn't, I changed my code in the workbook so that I only ever call the AddIn in a `sub`, not in a `function`, which means that although it might not compile correctly when the AddIn is closed, I don't get errors when trying to open the workbook. If I do manage to figure something out (I've run out of time on this for now), I'll let you know. — crunch, Apr 19 '15 at 10:45

Zvi Redler · Answer 1 · 2019-07-15T07:21:48.607

I was looking a lot and didn't find a good solution to that problem.

So i wrote the problematic function in another file, and if i need it i activate it like this:

On sp.mdb in a module:

Public Function soap30object() As Object
Set soap30object = New SoapClient30
End Function

On the main file:

Public Sub soap30object()
Dim ob As Object
Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("c:\sp\sp.mdb")
Set ob = appAccess.Run("soap30object")
End Sub

Have fun!

Another solution

Replace the code in Modul on Runtime...

     Public Sub replacemodel(mdlname As String, fnd As String, cngto As String)
        Dim toi As Long, oldlin As String, i As Long, firstchr As String, linnewnum As Long, last_ As Boolean
        Dim frm As Form,mdl As Module 
        DoCmd.OpenForm mdlname, acDesign
        Set mdl = Forms(mdlname).Module
        toi = mdl.CountOfLines
        With mdl
            For i = 1 To toi
                linnewnum = i
                oldlin = .lines(i, 1)
                If InStr(oldlin, fnd) <> 0 Then
                    oldlin = Replace(oldlin, fnd, cngto)
                    .ReplaceLine i, oldlin
                    goto nexx
                End If
            Next i
        End With
nexx:
        DoCmd.Close acForm, mdlname, acSaveYes
        Set mdl = Nothing
        'All variables reset when you edit modul on 
        msgbox "Program will restart now..."
        DoCmd.Quit acQuitSaveAll
    end Sub

VBA: Conditional compilation with implements keyword

1 Answers1