VBA compiled - affects conditional compile?

[kevin.bourque]

I've recently discovered conditional compiling in VBA using the
hash-const (#Const). (Many thanks to Jim Thomlinson in this group.)

However, I'm not totally comfortable or familiar with the methods VBA
uses when compiling when a particular module or procedure is called.
I'm trying to determine if conditional compiling would help speed up my
application.

More specifically, I have a long Case statement which chews up time and
makes the screen flicker. I'm trying to determine if converting the
Case statement with a conditionally compiled #If...#ElseIf...#ElseIf
would eliminate unnecessary code to be compiled. However, if the
on-the-fly VBA compiling steps through the conditional code anyways,
there is no advantage.

Is there a resource somewhere which explains how VBA is compiled, what
steps it goes through, and how one may optimize one's code for
compilation time?

Many thanks,
Kevin

 

[kevin.bourque]

Thanks for the help on the VBADecompiler Jim. Excellent resource (I'll
likely use the application provided on the site.)
Returning to the code after the holidays, I see that conditionally
compiling it is not the way to go.

My code, although will be run on different versions of Excel (it's a
multilingual application), is simply to choose between a list of 20
options. Each option is activated on the SelectionChange event of the
Worksheet module since selecting the cell indicates that the option is
selected. The rub is this: these "active" cells are spaced 7 rows apart
(these 7 rows are shown/hidden depending on many things).

So, I initially thought that using conditional compiling could cut down
the 20-long Case statement to something simpler, but I think this is
not the way to go.

Here is the code, in the Worksheet module.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Calls various forms when the user clicks on cell
'Several cells (20) below A1 will launch a sub, each different
'Each "active" cell is separated by 7 rows of display data which are
hidden in the called sub (subA, subB, etc...)

Dim targetAddress As String
targetAddress = Target.Address

Select Case targetAddress
Case Sheet1.Range("A1").Address
Call SubA
Case Sheet1.Range("A1").Offset(8#).Address
Call SubB
Case Sheet1.Range("A1").Offset(16#).Address
Call SubC
'etc... multiplied by 20, calling subs A through T.

Case Sheet1.Range("A1").Offset(480#).Address
Call SubT

End Select

End Sub


I'm comfortable with this logic and it works well. Just trying to speed
it up.

Regards,

Kevin