PDA

View Full Version : [SOLVED] Conditional Compile Question



Paul_Hossler
01-04-2017, 08:23 AM
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




17942

SamT
01-04-2017, 08:53 AM
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
01-05-2017, 04:23 AM
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
01-05-2017, 06:57 AM
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
01-05-2017, 08:45 AM
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
01-05-2017, 09:43 AM
That'd work, but it's not very elegant :devil2:

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
01-05-2017, 10:22 AM
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 :devil2:

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
01-06-2017, 09:10 AM
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
01-06-2017, 03:22 PM
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
01-07-2017, 07:50 AM
@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
01-07-2017, 10:20 AM
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
01-08-2017, 12:04 PM
That'd work, but it's not very elegant :devil2:

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.