Conditional Compile Question

[Paul_Hossler]

I have several 'common' modules that I use containing common functions, and I'd prefer to keep only a single version for ease of configuration management

I'd like to be able to include these modules both in 'application' workbooks as an Option Private Module, but also have them in my PESONAL.XLSM as NOT Option Private Module to expose the subs etc.

My idea was to set PERSONAL=1 as a conditional compile on my PERSONAL.XLSM, but not on 'application' workbooks, and 'bypass' the Option Private Module

I tried the obvious, but didn't work

Is it possible?
Am I missing something obvious?
Is there another way?

Option Explicit
#If Not PERSONAL = 1 Then
    Option Private Module
#End If

Sub Hello()
    MsgBox "Hello World!!!"
End Sub

Capture.JPG

[SamT]

Maybe?

#If ThisWorkbook.Name <> "Personal.xlsm" Then 
    Option Private Module 
#End If

But I suspect that Option Private Module might already be already more than just a compiler directive itself. It is used by Excel to determine what shows in the Tools >> Macros menu.

Do let us know how things work out.

[GTO]

Hi Paul,

FWIW if anything, I don't think that Option Compare|Private|Base|Explicit are effected at all by being wrapped in the conditional IF. I tried:

Option Explicit
  
#If Win64 = 1 Then
  Option Private Module
  Public Const Test As Long = 1
#ElseIf Win32 = 1 Then
  Public Const Test As Long = 2
#Else
  Public Const Test As Long = 3
#End If
  
Public Sub message()
  MsgBox "I was called and Test = " & Test
End Sub

I tested with Excel installed in 32-bit; 'Test' returns 2, but message() doesn't show in the macro run dialog as Sam mentions. message() is in Module2; which also holds a public sub called example(). I also had an example() public sub in Module3. Interestingly, the macro run dialog shows "wb1.xls!Module3.example", so it knows that there's an 'example()' sub in Module2, but as with message(), it is not listed. I didn't actually try accessing the vb project from another workbook, but am confident that the results would be consistent.

Mark

[Paul_Hossler]

I also had an example() public sub in Module3. Interestingly, the macro run dialog shows "wb1.xls!Module3.example", so it knows that there's an 'example()' sub in Module2

Hi Mark

1. Did not quite follow the above. Just because wb1.xls!Module3.example is exposed to Run Macros, I don't get to the conclusion. Yes, it does know (since wb1.xls!Module2.example is in the project) but I'm not seeing how the first part can make that conclusion

I don't think that Option Compare|Private|Base|Explicit are effected at all by being wrapped in the conditional IF

2. Unfortunately I'm beginning to believe that also. It would have made things much simpler

[SamT]

Comment marks work.

Comment out the line in Personal, then uncomment it when you put that module in another project.

Write a procedure to copy the module into the new project and uncomment the line.

[Paul_Hossler]

That'd work, but it's not very elegant

Besides, that's almost like having two versions floating around and I know I'd forget to change it

If I don't come up with another way (looking likely) I might have to do that

[GTO]

Hi Mark

1. Did not quite follow the above. Just because wb1.xls!Module3.example is exposed to Run Macros, I don't get to the conclusion. Yes, it does know (since wb1.xls!Module2.example is in the project) but I'm not seeing how the first part can make that conclusion

Hi Paul,

Happy New Year to you and yours as well.

I was just (non-articulately) mentioning that while I knew Win64 is 0, Option Private Module is still in effect; and that while while it "knows" that Module2's example() is there, it is not exposed. Sorry for the lack of clarity.

That'd work, but it's not very elegant

Besides, that's almost like having two versions floating around and I know I'd forget to change it

If I don't come up with another way (looking likely) I might have to do that

My take is in-line with Sam's. If programmatically inserting, I was thinking that it shouldn't matter what order the Options are listed, as long as they are at the top; so why not just tack it in after inserting the module?

Cheesy Example:

Option Explicit
  
' Define constants in case late-bound
Private Enum ModuleType
  StandardModule = 1    ' Standard module
  ClassModule = 2       ' Class module
  MSForm = 3            ' Microsoft Form
  ActiveXDesigner = 11  ' ActiveX Designer
  Document = 100        ' Document (Worksheet or Workbook) Module
End Enum
  
Sub example2()
Dim WB                    As Workbook
Dim vbaProj               As VBIDE.VBProject    ' or As Object for late-bound
Dim vbaComp               As VBIDE.VBComponent  ' SAA
  
  '// Ensure workbook is open...                                                        //
  On Error Resume Next
  Set WB = Application.Workbooks("B.xls")
  On Error GoTo 0
  '// ...or bail.                                                                       //
  If WB Is Nothing Then Exit Sub
  
  Set vbaProj = WB.VBProject
    
  For Each vbaComp In vbaProj.VBComponents
    If vbaComp.Type = ModuleType.StandardModule And vbaComp.CodeModule.Name = "MyModule" Then
      MsgBox "Yikes"
      Exit Sub
    End If
  Next
    
  ThisWorkbook.VBProject.VBComponents("MyModule").Export ThisWorkbook.Path & "\test.bas"
  DoEvents
  vbaProj.VBComponents.Import ThisWorkbook.Path & "\test.bas"
  DoEvents
  Kill ThisWorkbook.Path & "\test.bas"
  
  '// Tack in the Option here?//
  vbaProj.VBComponents("MyModule").CodeModule.InsertLines 1, "Option Private Module"
    
End Sub

It's late in the morning (still on graves...), check back later,

Mark

[Paul_Hossler]

Something that works, but seems to be more effort

1. No 'Option Private Module'
2. 'Private' on all subs and function that should never be exposed
3. Conditional compiles around the function or sub declarations that should be exposed in my Personal.xlsm

I think it'll be faster that reading/rewriting the modules

Option Explicit

#If PERSONAL Then
    Sub Hello()
#Else
    Private Sub Hello()
#End If
    
    MsgBox "Hello World!!!"
End Sub

Private Sub Check()
#If Not PERSONAL Then
    MsgBox "Production Mode"
#Else
    MsgBox "Personal Mode"
#End If
End Sub

The other approach is to have a 'calling' module in Personal.xlsm.

Option Explicit

Sub Hello()
    mod_Private.Hello
End Sub
Sub Hello2()
    mod_Private2.Hello2
End Sub

I'll have to experiment to see which one works better for me

[snb]

I'm not sure if I understand what you are after, but maybe:

Sub M_snb()
     If ThisWorkbook.FullName = Workbooks(1).FullName Then
          Application.Run Workbooks(1).Name & "!M_snb_001"   ' run M_snb_001 in personal.xlsb
    Else
         M_snb_001  '  run M_snb_001 in activeworkbook
    End If
End Sub

[Paul_Hossler]

@snb - thanks, but ...


What I wanted to do have one module that went both ways: Private when not in my PERSONAL.XLSM, but Public when it's in my PERSONAL (sort of like Dev vs. Production)

For easy CM I have 'toolbox' modules that I drop into a WB that provide a set of common functions in a particular area, e.g. mod_Shading_03. A module might have a sub that I want to explicitly call in development mode via Alt-F8, but which I don't want the user to call in production mode.

The trade-off is that for any given application WB there is unneeded code that is likely carried along within the module (bloat) but for me the advantages of better CM out weigh the extra baggage 99.9% of the time

[SamT]

I would rather keep the bloat in MyPersonal.xls

I bet there is some code in your ToolBox modules that is not needed for development.

Use different modules with some of the same Subs in them. Those Modules marked Private. for dragging into Production Books and one with the necessary development subs in Personal.

To be able to maintain Sync between the two sets, add comments to the Development Subs showing which Production module they're in and all development subs would be easily found in that one module.

If the "Module ID" comment gets copied to a production module, it won't matter.

[Bob Phillips]

That'd work, but it's not very elegant

Besides, that's almost like having two versions floating around and I know I'd forget to change it

It can be elegant, and you can avoid the 2-version syndrome.

Have it not commented in Personal.xls, and have some workbook_open code that adds a comment marker to that line. When you close Personal.xls, just don't save it, or have some workbook_beforesave code to remove it.

If you do copy it to another module, as copy is manual, just copy starting after the comment tick.