tricks of gurus

[Richard Schollar]

Yep that's cool.

 

[mortgageman]

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. <Big Snip>

OTOH, I suspect a whole lot more people will use F9, then will ever use
conditional compilation.

 

[Greg Truby]

...generating RC style formulae and ... changing the setting - via tools, options, general - to R1C1 style then copying the formula from the formula bar and pasting it into the VBE...
-fazza

I actually took it a step further than Fazza. I use my own toolbars instead of the built-ins. And one of them has a button for just that purpose. Click the button and it toggles A1/R1C1. Hold down the Ctrl key when clicking and it automatically just drops the R1C1 formula from the active cell onto the clipboard:

Sub ToggleA1toR1()
    '// use of DataObject object requires reference to MSForms library
    Dim doNew As DataObject
    If KeyPressed(VK_Control) Then
        Set doNew = New DataObject
        With doNew
            .SetText ActiveCell.FormulaR1C1
            .PutInClipboard
        End With
    Else
        With Application
            .ReferenceStyle = IIf(.ReferenceStyle = xlA1, xlR1C1, xlA1)
        End With
    End If
End Sub

I believe xld's posts on conditional compilation et al are indeed the kind of thing that separate experts from the average feller and more fit what I would call a "guru's trick". So I tend to agree that for many power Excel users, things like the F9 key stuff, hitting the F3 key to insert a name when editing a formula (not mentioned thus far, I don't think) or the F4=$ toggles will probably seem mundane. But I rather suspect that Gene is correct in that it's the simpler tricks that many newbies/novices are gonna go "oh, cool!" Heck, I didn't know about the Alt+; thing for visible cells. But there are so many hot keys we could spend pages on them. I still get "how'd you do that?!" when I hit the F11 key and presto- a chart! If you don't know, try holding down your shift key while clicking some of the build-in buttons like font-size-up/down, right/left-align, etc. A lot of them do the opposite when clicked with shift depressed. There's a thread in the lounge on this somewhere...

 

[NateO]

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.

No worries, Bob...

I'm often surprised how few people use F9 or know about it. Guru trick? Perhaps not... I'm not so sure this is a Guru trick, either:

    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, ...

Is this not obvious for a read/write Boolean Property toggle?

In any event, what was I saying about Concatenation...? Right. When building a string you want to avoid a large number of Concatenations, especially in a potentially large, dynamic loop; which I see people doing all the time.

While one does want to write self-documented, maintainable code, and the intent when concatenating in a loop is apparent, the performance degradation can be significant.

Take the following:

http://msdn.microsoft.com/library/en-us/script56/html/2d4e6d4c-5cac-49de-b0b4-2e96ce56dd5f.asp

Note the comment in their code:

' Regarding code quality:

' 1) The following code does a lot of string manipulation by
' concatenating short strings together with the "&" operator.
' Since string concatenation is expensive, this is a very
' inefficient way to write code. However, it is a very
' maintainable way to write code, and is used here because this
' program performs extensive disk operations, and because the
' disk is much slower than the memory operations required to
' concatenate the strings. Keep in mind that this is demonstration
' code, not production code.

Why is this so expensive? Because of the way BSTRs are allocated, you're throttling the Heap:

http://msdn.microsoft.com/library/en-us/dngenlib/html/heap3.asp

What Are Common Heap Performance Problems?
Here are the most common obstacles you will encounter when working with the heap:

- Slowdown as a result of frequent allocs and reallocs. This is a very common phenomenon when you use scripting languages. The strings are repeatedly allocated, grown with reallocation, and freed up. Don't do this. Try to allocate large strings, if possible, and use the buffer. An alternative is to minimize concatenation operations.
{snip}

Rethink algorithms and data structures
- If you make extensive use of Automation structures, consider factoring out Automation BSTRs from your mainline code, or at least avoid repeated operations on BSTR. (BSTR concatenation leads to excessive reallocs and alloc/free operations.)
{snip}

So, often times it makes a lot more sense, performance-wise to work with arrays, whether it's String & Byte Arrays, which directly coerce, or Array->String via Join().

Another efficient way to build a String is to allocate a number of characters to it, building a buffer before you manipulate it, and stack it with the Mid$() function.

Guru trick? Not sure, perhaps this is obvious, too. But, it's some more food for thought...

 

[xld]

Is this not obvious for a read/write Boolean Property toggle?

Of course it obvious ... if you know it . If you understand properties and operators it is patently obvious. But many people use things they have seen knowing that they work, but without necessarily understanding why. How many times have you seen code like

If object.boolean_property = TRUE then

My point was that the thread started along certain lines, and then just moved onto a list of tips and tricks (more along the lines of Bob Umlas' offerings, neat and useful but mainly pretty common knowledge). I was wading through many posts without learning anything. Whereas, I would venture that even though I started the condutional compilation topic, I read Greg's piece with interest.

I accept that the logical operator may not be as useful as this topic really deserves, but I was trying in my small way to get in back on course .

 

[Oorang]

And I read Nate's piece with interest too. Nate get's all hard-core when you least expect it. Sort of makes me wonder what else is stored in his head I actually wasn't aware that concatenation was bottleneck. Just to make sure I understand... Does the code below represent what you are saying correctly?

Option Explicit
Sub DoIHaveItRight()
    Dim strVal(0 To 3) As String
    strVal(0) = 0
    strVal(1) = 1
    strVal(2) = 2
    strVal(3) = 3
    'Do this:
    Faster strVal
    'Not this:
    Slower strVal
End Sub
Sub Faster(strVal() As String)
    Debug.Print VBA.Join(strVal, Empty)
End Sub
Sub Slower(strVal() As String)
    Debug.Print strVal(0) & strVal(1) & strVal(2) & strVal(3)
End Sub

 

[NateO]

Nate get's all hard-core when you least expect it. Sort of makes me wonder what else is stored in his head

You don't really want to know!

I actually wasn't aware that concatenation was bottleneck. Just to make sure I understand... Does the code below represent what you are saying correctly?

It can be... Concatenation may or may not be fast or slow... Join() with your posted code looks to me like it's slightly slower, and using a Mid$() loop with a buffered string looks slightly faster, to me:

Public Declare Function QueryPerformanceFrequency _
    Lib "kernel32.dll" ( _
    lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
    Lib "kernel32.dll" ( _
    lpPerformanceCount As Currency) As Long

Sub Faster(strVal() As String)
Dim tmpStr As String, i As Long
Let tmpStr = Space$(UBound(strVal) + 1)
For i = LBound(strVal) To UBound(strVal)
    Mid$(tmpStr, i + 1, 1) = strVal(i)
Next
End Sub

Sub Slower(strVal() As String)
Dim tmpStr As String
Let tmpStr = strVal(0) & strVal(1) & strVal(2) & strVal(3)
End Sub

Sub foo()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency
Dim i As Long
Dim strVal(0 To 3) As String
strVal(0) = 0
strVal(1) = 1
strVal(2) = 2
strVal(3) = 3
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For i = 1 To 100000
    'Call Faster(strVal)
    Call Slower(strVal)
Next
QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

But Join() will continue to gain, performance-wise, as the performance of concatenating is degrading, at a compounding rate, as the string/array in question grows, either in terms of each element's size, or the total number of elements.

I'm not sure I would *always* avoid concatenation, the case you've presented might be one of these cases, one of the simplest, and fastest cases of concatenation. I.e., your Strings are small, the number of concatenations is small.

Here's two factors to consider:

1) How many times are you concatenating on a String?
2) How large are the Strings in question?

If the answer to either one of these questions is 'large', you might want consider an alternative approach to working with Strings. If you're concatenating in a large loop, you really should reconsider what you are doing.

If you're interested, I'd recommend reading the following post by Microsoft's Kevin Williamson on the implications of concatenation as it pertains to BSTRs and VB[A]:

http://groups.google.com/group/microsoft.public.vb.com/msg/a4738fcf6faa185a

Ken Getz's optimization techniques:

Chapter 15: Application Optimization

I'm glad I finally found that article again, Microsoft moved my cheese!!

And, a kb on String Concatenation:

http://support.microsoft.com/kb/170964

While this becomes much more important in larger applications, like building rather large Strings, e.g., writing to files, it's a good concept to keep in mind.

And there's a few ways to build a String without concatenating, depending what you are doing... Here's another example, featuring the Byte Array:

http://www.mrexcel.com/board2/viewtopic.php?p=849596#849596

This particular UDF will absolutely blow the doors off a UDF attempting to do this via concatenation with very large Strings, say 100 characters. But, it also appears to outpace such an approach on smaller String returns, say 6 characters.

Hope this helps.

 

[Oorang]

Good Stuff Nate
Here is one from Dave Hawley that I found today looking around for something else. You can take a screen shot of just the selected area of a worksheet by selecting what you want a shot of, holding down the Shift key, select the edit menu then Copy Picture.

 

[litrelord]

Good Stuff Nate
Here is one from Dave Hawley that I found today looking around for something else. You can take a screen shot of just the selected area of a worksheet by selecting what you want a shot of, holding down the Shift key, select the edit menu then Copy Picture.

Or use the camera (in the Commands > Tools section when you customise the toolbar buttons) to get a linked picture of a section of the worksheet. I've used that a few times and found it quite useful.

Not sure it would pass as a guru tip but definitely not a tool that advertised widely.

 

[Oorang]

I think I just discovered why the Camera Button feature is buried. I only have tried this on 2003, but when you copy it pulls the picture data and the text data at the same time. So when you paste in a pic-app you get picture data automatically and if you paste in a text app you get text. To give a simple illustration, you could just ctrl-c to copy and then paste in either mspaint or notepad.