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

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



On Dec 29 2006, 11:26 am, Jim Thomlinson
wrote:
For a good explanation of the VBA Compiling process give this a read...

http://cpap.com.br/orlando/VBADecompilerMore.asp

That being said you probably don't want to use hash code unless it is to
fundamentally change the way that the program operates (different versions of
XL for instance).

Post the code you are using and perhaps we can help speed it up for you.
--
HTH...

Jim Thomlinson

" wrote:
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