How to programmatically change conditional compilation properties of a VBA project

Question

I'm currently working on a VBA code generator/injector that adds VBA functionality to Excel workbooks by using the VBA Extensibility. This all works fine.

However, the original code that is injected uses conditional compilation, referring to some global conditional compilation arguments:

Is there any way I can programmatically modify/add the conditional compilation arguments of a VBA project?

I checked all properties of the VBProject but couldn't find anything.

Micronesian

YOu mean like this? msdn.microsoft.com/en-us/library/aa240847%28v=vs.60%29.aspx – Endothermic

@SiddharthRout: Yes - only that I wouldn't have any vb6.exe as its all in Excel/VBA. – Micronesian

That code is compatible with VBA as well :) – Endothermic

@SiddharthRout: i know - but you'd need a lot of #CONST statement on a lot of modules - which I'd like to avoid... – Micronesian

But keep all of them in the one module and declare them as PUBLIC? – Endothermic

That was also my first take - but unfortunately, the #CONST Is always scoped locally. :-( – Micronesian

let us continue this discussion in chat – Endothermic

Answer 1

Inspired by this approach, shown by SiddharthRout, I managed to find the following solution using SendMessage and FindWindow:

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Declare Function GetWindowTextLength Lib "user32" Alias _
"GetWindowTextLengthA" (ByVal hwnd As Long) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Const WM_SETTEXT = &HC
Const BM_CLICK = &HF5


Public Sub subSetconditionalCompilationArguments()
    Dim strArgument As String
    Dim xlApp As Object
    Dim wbTarget As Object

    Dim lngHWnd As Long, lngHDialog As Long
    Dim lngHEdit As Long, lngHButton As Long

    strArgument = "PACKAGE_1 = 1"

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False

    Set wbTarget = xlApp.Workbooks.Open("C:\Temp\Sample.xlsb")

    'Launch the VBA Project Properties Dialog
    xlApp.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

    'Get the handle of the "VBAProject" Window
    lngHWnd = FindWindow("#32770", vbNullString)
    If lngHWnd = 0 Then
        MsgBox "VBAProject Property Window not found!"
        GoTo Finalize
    End If

    'Get the handle of the dialog
    lngHDialog = FindWindowEx(lngHWnd, ByVal 0&, "#32770", vbNullString)
    If lngHDialog = 0 Then
        MsgBox "VBAProject Property Window could not be accessed!"
        GoTo Finalize
    End If

    'Get the handle of the 5th edit box
    lngHEdit = fctLngGetHandle("Edit", lngHDialog, 5)
    If lngHEdit = 0 Then
        MsgBox "Conditional Compilation Arguments box could not be accessed!"
        GoTo Finalize
    End If

    'Enter new argument
    SendMessage lngHEdit, WM_SETTEXT, False, ByVal strArgument

    DoEvents

    'Get the handle of the second button box (=OK button)
    lngHButton = fctLngGetHandle("Button", lngHWnd)
    If lngHButton = 0 Then
        MsgBox "Could not find OK button!"
        GoTo Finalize
    End If

    'Click the OK Button
    SendMessage lngHButton, BM_CLICK, 0, vbNullString

Finalize:
    xlApp.Visible = True
    'Potentially save the file and close the app here
End Sub

Private Function fctLngGetHandle(strClass As String, lngHParent As Long, _
    Optional Nth As Integer = 1) As Long
    Dim lngHandle As Long
    Dim i As Integer

    lngHandle = FindWindowEx(lngHParent, ByVal 0&, strClass, vbNullString)
    If Nth = 1 Then GoTo Finalize

    For i = 2 To Nth
        lngHandle = FindWindowEx(lngHParent, lngHandle, strClass, vbNullString)
    Next
Finalize:
    fctLngGetHandle = lngHandle
End Function

Micronesian

+ 1 For finding an answer yourself :) – Endothermic

Answer 2

For Access 2000 I used:

Application.GetOption("Conditional Compilation Arguments")

for getting,

Application.SetOption("Conditional Compilation Arguments", "<arguments>")

for setting.

That's all.

Adenoma

Answer 3

The only way to affect anything in that dialog box is through SendMessage API functions, or maybe Application.SendKeys. You'd be better off declaring the constants in code, like this:

#Const PACKAGE_1 = 0

And then have your code modify the CodeModule of all your VBA components:

Dim comp As VBComponent
For Each comp In ThisWorkbook.VBProject.VBComponents
    With comp.CodeModule
        Dim i As Long
        For i = 1 To .CountOfLines
            If Left$(.Lines(i, 1), 18) = "#Const PACKAGE_1 =" Then
                .ReplaceLine i, "#Const PACKAGE_1 = 1"
            End If
        Next i
    End With
Next comp

Saccharase

Thanks for the answer - a sure +1. Unfortunately, it doesn't help as I'm working in an international environment, so the SendKeyswould fail. And the whole idea of using the central Conditional Compiler Arguments was to prevent lots of #CONST statements on top of each module. :-( – Micronesian

Answer 4

This is how to get and set multiple arguments in Access after 2010:

To set them this is the code:

application.SetOption "Conditional Compilation Arguments","A=4:B=10"

To get them:

Application.GetOption("Conditional Compilation Arguments")

They are printed like this: A = 4 : B = 10

That is how to test it:

Sub TestMe()

    #If A = 1 Then
        Debug.Print "a is 1"
    #Else
        Debug.Print "a is not 1"
    #End If

End Sub

Overcasting