tricks of gurus

[Oorang]

Whilst we wait on the inestimable NateO, I would throw one more out there. Learn to use the Enum feature if you are building functions/subs that will be a lot later, it saves a ton of time to have the intellisense prefil for you.
Also put as a little of you Excel VBA code in non-private functions as possible usless you plan on it showing up in the "user-defined functions" of the formula wizard.
In that same vein, plan on any non-private sub showing up on the "run-macro" list.

 

[xld]

or just add

Option Private Module

before the declaratives.

 

[Greg Truby]

Also, learn about that conditional compiling stuff if you want to use ENUM across multiple versions. ENUM becomes available after XL97; which correspondends to the VBA6 compiling constant.

Just so no one thinks I'm against ENUM: I'm not. I use it frequently. I've just stubbed my toe on versions and use compiling constants to fix it. [see here for an example of what I mean]

 

[Oorang]

Or just add Option Private Module.

That definantly has it's uses. However if you intended for SOME things to be available, that might fix it a just a little too well

 

[Greg Truby]

...However if you intended for SOME things to be available, that might fix it a just a little too well
-oorang

And you can't stick them fellers in a different module because _________?

 

[Oorang]

...However if you intended for SOME things to be available, that might fix it a just a little too well
-oorang

And you can't stick them fellers in a different module because _________?

You can... I just don't like too. I use modules to keep things grouped and I try to keep the number of modules small when possible. So if I have a group of date functions and I want some available to the module, some to the project and some to the world then I prefer to leave all the date functions in the date functions module and just scope them to be available where I want them to be...

Is that considered bad practice?

And now that I think about it... How do you make a module's functions available to other projects but not to Excel?

 

[xld]

You can... I just don't like too. I use modules to keep things grouped and I try to keep the number of modules small when possible. So if I have a group of date functions and I want some available to the module, some to the project and some to the world then I prefer to leave all the date functions in the date functions module and just scope them to be available where I want them to be...

I would call that the tail wagging the dog!

And now that I think about it... How do you make a module's functions available to other projects but not to Excel?

Put it in that project.

 

[Fazza]

A bit like using F9 in the formula bar to see steps in a calculation, when entering functions to a worksheet I usually type in the name, such as =VLOOKUP and then use CTRL-A.

As each argument is entered its values show on the RHS of its input box and when available the result shows towards the bottom.

This can be handy enough as it is, and extra good if you have arrays 'cause you see them too on the RHS - well, as much as fits, anyway. You can see each element of the array.

 

[Fazza]

Experienced coders are also likely already familiar with generating RC style formulae and the handy tip of changing the setting - via tools, options, general - to R1C1 style then copying the formula from the formula bar and pasting it into the VBE. Such as, set up the line of code,

.Range(whatever).FormulaR1C1 = ""

Jumping into Excel, where the formula can exist or be created, setting R1C1 style, F2, CTRL-C from the formula bar, back to VBE, CTRL-V between the waiting "". Then the step I sometimes forget, setting Excel back to normal style.

And using RC style in VBA conditional formats. Until I learnt that, I used to activate a cell in the target range and then create normal formulae ...

Goal seek is a simple & handy little tool on the worksheet, too.

 

[xld]

Don't mean to be a party-pooper, but this seems to be getting off topic (and you would never catch me doing that ). Helpful as things such as F9 are, they are hardly guru tricks, not in the manner that conditional compilation is I would venture.

Here is one that I used today that I feel is underused - logical operators. We probably all know logical operators, and occasionally use them, such as

If Not Intersect ...

but when used in conjunction with Boolean properties you can utilise a nice trick to make the code shorter and more readable. Consider this code to toggle gridlines

    With ActiveWindow
        If .DisplayGridlines Then
            .DisplayGridlines = False
        Else
            .DisplayGridlines = True
        End If
    End With

and look at this alternative

    With ActiveWindow
        .DisplayGridlines = Not .DisplayGridlines
    End With

Shorter, easier to understand, and no messy Ifs which alwasy break the flow IMO. And then there is And, Or, ...