tricks of gurus

[xld]

Want a list of the application constants?

First set a reference to the Typelib dll. You will need to browse for it, it won't be automatically loaded, On my machine it is called TLBINF32.DLL.

Then run this code. It is set for Outlook XP, you might need to check the Outlook OLB file, it might have a version prefix for 2000, or 2003.

Public Sub GetConstants() 
    Dim oOLB As Object 
    Dim sText As String 
    Dim oOLBc, oOLBm 
    Dim j As Integer 
     
    On Error Resume Next 
     
    With Worksheets("Constants") 
        With .Range("A1") 
            .Offset(0, 1).Value = "Outlook" 
            .Offset(1, 1).Value = "msoutl.olb" 
            .Cells(3, 1).Resize(.CurrentRegion.Rows.Count, 2).ClearContents 
            Set oOLB = TypeLibInfoFromFile(Application.path & "\msoutl.olb") 
            j = 2 
            For Each oOLBc In oOLB.Constants 
                For Each oOLBm In oOLBc.Members 
                    .Offset(j, 0).Value = oOLBm.Name 
                    .Offset(j, 1).Value = oOLBm.Value 
                    j = j + 1 
                Next oOLBm 
            Next oOLBc 
        End With 
        .Visible = True 
        .Activate 
        .Range("A1").Select 
    End With 
     
    Set oOLB = Nothing 
     
End Sub

Of course, the Outlook OLB could be substituted by any of the other application OLBs.

 

[Oorang]

I thought of one that can be relativly obscure. When you are editing formulas, if your cursor is on a range, you can can cycle through relative; relative-row, absolute-column; relative-column, absolute-row; and absolute by pressing F4. That one has saved me a ton of time since I was shown it.

 

[xld]

Here's another one.

One problem with dynamic range names is that you don't see them in the Names box, and the definition doesn't tell you which cells are covered.

Here is one way to see them.

Edit>GoTo, type the range name in the reference box, and the cells currently defined by that name get selected.

 

[Fazza]

I also use hidden names extensively. As well as the handy name manager addin, I often have a code module m_Unhide_Names with two routines. One to unhide all named ranges and one to hide them. So these can be quickly run when developing/editing the file.

Amongst other uses, as posted already, complex formulae that I would rather not expose to (casual) users are hidden. Another use is of course to tag cells that the code can identify.

Special cells offers some handy ways to achieve a few things.

ALT-; shortcut is handy for selecting visible cells.

Query tables I find extremely powerful. A little bit of SQL and magic can be done. Stuff that otherwise requires extensive programming just happens simply and quickly.

Using arrays within VBA can be a neat way to quickly work with lots of data.

VBA's immediate window is a great way to test / print / edit things. Including SQL for query tables. You can debug.print the SQL then modify in the immediate window. When you have it write, add a trailing double quote ", then remove line feeds to put multiple lines back into a single line, adding a space as required to separate each clause, add a leading double quote, edit the debug.print line to set the SQL to this string.

Kewl........? I like it, anyway!!

 

[SydneyGeek]

Not exactly a guru trick, but obscure and a time saver.
To sum all the columns in a table, select the table then one extra row and press Alt=
To sum all the rows, include an extra column.

Denis

 

[Fazza]

Another handy trick is to return NA() for chart data that is not to be plotted; within an IF such as

=IF(want to plot,normal formula,NA())

 

[litrelord]

ALT-; shortcut is handy for selecting visible cells.

You beauty. Didn't know that. I've been doing
Ctrl+G
Alt+S
Y
Enter

for so long now it's going to take some serious training to get out of it.

Thank you

 

[Oorang]

Also not a Guru trick, but just mildly useful:
ctrl-; willl insert today's date into your formula.

 

[NateO]

Here's a couple of thoughts...

Using F9 to Evaluate functions, or components thereof, is a brain-saver.

Using Byte Arrays to parse data, mostly Strings, in VB[A], is both efficient and effective.

Open-ended concatenation in a loop is very maintainable code, the intent is relatively clear... But, the flip-side is that it's code that probably is not worth maintaining.

 

[Greg Truby]

...Open-ended concatenation in a loop is very maintainable code, the intent is relatively clear... But, the flip-side is that it's code that probably is not worth maintaining.
-NateO

Estimado señor Oliver,

By virtue of reading the words and understanding the meaning of each individual word, I believe I understand the sentence. But I'm not quite clear on when/why you would do this. If'n I'm a tad puzzled, I'ma wonderin' if'n a newbie might be a-scratchin' his melon too. Care to provide a code snippet that illustrates what you mean and tell us of an instance where a feller'd use this?

Merci beaucoup,