# tricks of gurus



## nbrcrunch

When I first learned about SUMPRODUCT() I was as excited as I was when I learned to write my first SUM() formula.

So I'm reading through Excel Help about SUMPRODUCT and doesn't even begin to hint about the power of that function.  This starts me wondering what other hidden gems are out there (regarding intrinsic functions).  For example, the help on FACT() says that FACT(5) is equal to 1*2*3*4*5.  Big deal.  Is that all there is?

What other expanded functionality of built-in functions have you gurus (and lesser stars) out there discovered that you are willing to share with us?


----------



## Tom Urtis

This is the kind of question that can lead to a thread of very interesting posts.   There are a lot of undocumented and underdocumented features in Excel.  It would be neat to collect a few, as there is no definitive list for this kind of stuff.

Here's a start.  Look into the N feature of formulas, which is largely undocumented, but which allows you to place a note in a formula to tell you something about it.   Example of N: 

On Sheet1, cell A2 you have the number 50 because in A1 you have the value "Price of widgets"
On Sheet2, cell B2 you have the number 125 because in B1 you have the value "Sales of widgets"
Needing to multiply them, thus: 
On Sheet3, cell C1 you have the formula =Sheet1!A2*Sheet2!B2, which returns the result 6250.

Now if you want to look at that formula in the formula bar, and have it make sense with text to explain what those numbers refer to...

...instead of the formula on Sheet3 cell C1 of... 
=Sheet1!A2*Sheet2!B2 

...you could have 
=Sheet1!A2*Sheet2!B2+N("50 times 125") 

...or
=Sheet1!A2*Sheet2!B2+N("Price of widgets 50 times Sales of widgets 125")

This will still return 6250 but lets you see (as an "N" note appended to the formula) what numbers and factors are being calculated.

Just a start as I said, I'm sure others reading this post will know of more interesting undocumented or underdocumented features.


----------



## Scott Huish

Just to expand on Tom Urtis's post:

N is not a note function, what it does is this:
Returns a value converted to a number.

The text string is just evaluating to 0, so is not affecting the outcome of the sample formulas.


----------



## nbrcrunch

Yes, I've used the N() function before.

In fact, in a file I've named Comments.xls, I have documented the three ways Excel permits comments

1. Using comment boxes (which most know)
2. Using Validation comments which some know
3. Using the N() function, which few know.  (I have nicknamed these "inline" comments.)

But has this thread died so quickly?  I was surprised to only find 2 posts after two days. Must be the weekend.


----------



## PA HS Teacher

That's cool. I never though of Using N that way.

I've recently become enamored with the use of the Row function in array formulas and sumproduct formulas.  You can use it kind of like a for next loop.

e.g.
=Row(1:20) array entered will return and array of numbers 1 to 20.

or in cojunction with the indirect function it can be used to loop through the characters of a string.

For example:
=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)

array entered across multiple cells will return a single letter from the text in A1 in each cell.


----------



## PA HS Teacher

How about the following for commenting functions that return a string.



		Code:
__


=A1&IF(1=0,"Make any comments you desire here. This has the advantage of working with strings as well. Doesn't seem to allow long comments, but its better than nothing.  If you need a number result you must coerce it with a +0, or -- etc..","")


----------



## nbrcrunch

okay, now that the weekend is over, time to shove this back up to the top of the heap.


----------



## Aladin Akyurek

PA HS Teacher said:
			
		

> ...I've recently become enamored with the use of the Row function in array formulas and sumproduct formulas.  You can use it kind of like a for next loop.
> 
> e.g.
> =Row(1:20) array entered will return and array of numbers 1 to 20.



Using that idiom as is will invariably lead to non-robust formulas, compromising their correctness.



> or in cojunction with the indirect function it can be used to loop through the characters of a string.
> 
> For example:
> =MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
> 
> array entered across multiple cells will return a single letter from the text in A1 in each cell.



Not without considerable performance cost...


----------



## PA HS Teacher

I recognize these limitations.  I understand that invoking the volatile indirect is expensive, and invoking it in an array formula is really expensive.  There are some situations I've encountered recently, mostly dealing with strings, where it would be nice to be able to loop through the characters of a string.  Hypothetically speaking, if you wanted an array containing the positions of every "a" in abracadabra, you could use the following array formula:

=(MID(A1,ROW(1:1000),1)="a")*ROW(1:1000)

(I just picked 1000 because it is much larger than a string I'd likely encounter.  I did this instead of using the indirect function to stop looping at the last character)

Is there another, more robust formula that will return the positions of all "a"s in a string located in A1?


----------



## Aladin Akyurek

PA HS Teacher said:
			
		

> I recognize these limitations.  I understand that invoking the volatile indirect is expensive, and invoking it in an array formula is really expensive.  There are some situations I've encountered recently, mostly dealing with strings, where it would be nice to be able to loop through the characters of a string.  Hypothetically speaking, if you wanted an array containing the positions of every "a" in abracadabra, you could use the following array formula:
> 
> =(MID(A1,ROW(1:1000),1)="a")*ROW(1:1000)
> 
> (I just picked 1000 because it is much larger than a string I'd likely encounter.  I did this instead of using the indirect function to stop looping at the last character)
> 
> Is there another, more robust formula that will return the positions of all "a"s in a string located in A1?



Insert a row before the formula cell and observe the result array. That's what I mean by robustness.

An option to avoid INDIRECT (here an almost perfect occasion for invoking the INDIRECT idiom) and to have the desired array result is:

{=IF(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)="a",ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))}


----------



## PA HS Teacher

Thanks for the advice Aladin.

I forgot about index, that is much better than using indirect.


----------



## nbrcrunch

Dang. this thing just up & died. Aladin was one of the those that I was hoping would contribute here. Instead, he just poo-poos the other posters   


Come on, throw us bone Aladin! A nice meaty one with lots of marrow.


----------



## erik.van.geit

it's a pitty this one died so early: perhaps the reason was you tried to keep it alive to strongly

something very simple about VBA
when I have some code which has a part I want to "turn on" and "turn off" for testing, it's tedious to comment (out) a bunch of lines
putting them between a "trivial" If ... End If makes the job quickly done


		Code:
__


Sub test()
'some codelines
    If True Then 'change to False when needed
    'test codelines to turn on and off
    End If
'some other codelines
End Sub

alternatively you can use
If 1 = 1 Then 'If 1 = 0 Then

voilà 
Erik


----------



## PaddyD

re the dying - I'll move to the Lounge

re Aladin 'poo pooing' - I'd rather interpret it as terse advice re implications of using some 'non-standard techniques' that those unfamiliar with them should be aware of

re other odd / undocumented offerings:

1) forgive shameless plug for own post  but - get.cell (& by implication other old xl4 marcos):

http://www.mrexcel.com/board2/viewtopic.php?t=21312

2) use of subtotal() to generate an array of filtered items in a list:

http://j-walk.com/ss/excel/eee/eee001.txt

...used, e.g, here:

http://www.mrexcel.com/board2/viewtopic.php?t=42085&highlight=subtotal

...see also the other EE letters for further 'power formula techniques'

3) arrayed use of frequency() to work with 'runs' in data - an example from Domenic:

http://www.mrexcel.com/board2/viewtopic.php?t=244297&highlight=frequency


----------



## ExcelChampion

A common, but not always obvious to novice or intermediate users, use a LOOKUP function or the INDEX/MATCH combination to return a value from a range of values instead of nested IF statements.

As well, use the INDEX/MATCH combination to look across columns, down rows or both.  Common knowledge to most on this board, but still a good one for novice and intermediate users.

Not necessarily a function, but formulas entered as NAMES are automatically array entered.

And of course don't forget about the (mostly) undocumented DATEDIF...


----------



## barry houdini

Very interesting links, Paddy. 

I particularly like Domenic's FREQUENCY formula, I can just never quite remember how that works when I want to do it.   

I've just been playing with some SUBTOTAL/OFFSET constructions myself to return a list of names according to values shown in another column like this:subtotal_offset.xlsABCD1namefrequencylist22barry5barry3phil1barry4steve3barry5gavin0barry6barry7phil8steve9steve10steve11 Sheet1

Formula in C2 copied down

=IF(ROW()-ROW(B$2)< SUM(B$2:B$5),LOOKUP(ROW()-ROW(B$2),SUBTOTAL(9,OFFSET(B$1,,,ROW(B$2:B$5)-ROW(B$2)+1,)),A$2:A$5),"")

I was asked to produce something like this in the past but couldn't do so without a helper column. The SUBTOTAL/OFFSET part is crucial because it allows you an array of ranges, each range one cell greater than the next.

Finally, I like to think that my own "specialist subject" is dates and times and date and time-related formulas. I'm particularly keen on this simple formula which counts the number of Saturdays in a date range (between A1 and B1 inclusive)

=INT((WEEKDAY(A1)+B1-A1)/7)

and which can be adapted to count other days or as a NETWORKDAYS substitute (without the holidays)


----------



## ExcelChampion

Speaking of DATES, again, not undocumented, but still, in my opinion, not used to it's potential:-

=DATE(2006,1,0)

which returns the last day of the previous month specified.  I've used this one rather creativly to solve some interesting problems.


----------



## barry houdini

In a similar vein....

=A1-DAY(A1) 

a simple way to give the first day of the month of any date in A1.

I first saw this used here by Aladin (who else?)


----------



## Richard Schollar

> In a similar vein....
> 
> =A1-DAY(A1)
> 
> a simple way to give the first day of the month of any date in A1.
> 
> I first saw this used here by Aladin (who else?)



Wouldn't that be

=A1-DAY(A1)+1

Barry?


----------



## xld

> it's a pitty this one died so early: perhaps the reason was you tried to keep it alive to strongly
> 
> something very simple about VBA
> when I have some code which has a part I want to "turn on" and "turn off" for testing, it's tedious to comment (out) a bunch of lines
> putting them between a "trivial" If ... End If makes the job quickly done
> 
> 
> Code:
> __
> 
> 
> Sub test()
> 'some codelines
> If True Then 'change to False when needed
> 'test codelines to turn on and off
> End If
> 'some other codelines
> End Sub
> 
> alternatively you can use
> If 1 = 1 Then 'If 1 = 0 Then
> 
> voilà
> Erik



Surely, you would use conditional compilation?


----------



## Richard Schollar

> Surely, you would use conditional compilation?



Care to enlighten me, Mr P? I could probably stretch to another metaphorical gin in return


----------



## barry houdini

> Wouldn't that be
> 
> =A1-DAY(A1)+1
> 
> Barry?



Most definitely, Richard, good catch, as they say

just testing if anybody's actually out there


----------



## xld

> Surely, you would use conditional compilation?
> 
> 
> 
> 
> Care to enlighten me, Mr P? I could probably stretch to another metaphorical gin in return
Click to expand...


Conditional compilation allows you to create testing and production versions of your code. 

For example, you may want to include message boxes, or  Debug.Print  or Debug.Assert  statements while you are developing and testing your code, but you don't want those to be active when you release the code. VBA allows you to include or exclude blocks of code with conditional compilation.  

Conditional compilation uses If,  Then, and Else statements to include or exclude a block of code.  All conditional compiler directives start with #. 
Note that this is the directive, not the code that is conditional upon those directives.

So a typical use of conditional compilation would look like



		Code:
__


#If TestRun Then
‘some normal code that is executed if a test run
#Else
‘some other normal code that is executed when it is NOT a test run
#End If


You will notice that a variable is tested, this is called TestRun in the example and would also be a conditional compiler directive,



		Code:
__


#CONST TestRun = True


The #CONST directive is used to create the variable.

Then, delimit the blocks using the compiler directives to include various blocks of code.

Many other conditional blocks of code can use this conditional constant, so there is only one place to change when releasing to production.

There are a few built-in conditional constants as well that can be tested, such as VBA6, which I use to include an Excel 97 split method



		Code:
__


#If VBA6 Then 
#Else 
'-----------------------------¬¬-----------------------------¬-¬------ 
Function Split(Text As String, _ 
        Optional Delimiter As String = ",") As Variant 
'-----------------------------¬¬-----------------------------¬-¬------ 
Dim i As Long 
Dim sFormula As String 
Dim aryEval 
Dim aryValues 
    If Delimiter = vbNullChar Then 
        Delimiter = Chr(7) 
        Text = Replace(Text, vbNullChar, Delimiter) 
    End If 
    sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & _ 
        """}" 
    aryEval = Evaluate(sFormula) 
    ReDim aryValues(0 To UBound(aryEval) - 1) 
    For i = 0 To UBound(aryValues) 
            aryValues(i) = aryEval(i + 1) 
    Next 
    Split = aryValues 
End Function 
#End If


And there is also a Mac and a Win32 constant.


I reckon we are upto a case now  . I am only about 50 miles away from you, so you can deliver


----------



## Richard Schollar

Thank you Bob 

I can obviously see where it's very useful (the xl97 and Split thing), but does it have a big advantage over using a standard If Then Else?  Does it do anything differently?

I had a couple of Sapphire bottles put aside for you, but unfortunately was *forced* into drinking them myself...


----------



## xld

Yes of course it does. If you have an  If ... Else ... End If all of that code is compiled, if you have #If .. #Else ... #End If then only the part of the code that satisfies the condition is compiled. Leaner, meaner and better all round.

Plus, you have the built-in constants that you don't have to maintain.

How would you do the Split thing your way?

The Excel 97 is just a simple example, I use it to build early/late binding all in one file, testing/production where my testing/production variable is set by an environment variable, etc. I agree you could do all that with your own code, but that GETS COMPILED as well.

As for the Sapphire, I don't mind new bottles, as long as they are full.


----------



## Richard Schollar

I see, I see.  So, am I right in thinking that compiled in this instance refers to when you create an Add-In rather than say simple code in an every day workbook that just exists in a standard module?  There's no compilation in the second instance, or am I incorrect here?  In which case (other than the built in constants) the #IF would not have an advantage over standard If (unless you will compile at some future point)?  Am I getting this right, or have I missed the point/got it uterly wrong?  Maybe I am misinterpreting what is meant by 'compiled'?

I've picked up a new bottle of Sapph - 'course, there's no guarantee it'll survive past tomorrow night


----------



## xld

No, it is not addin specific, all code gets compiled, at run-time at the latest.

VBA is not stored in the workbook as text, but is converted to something else
when a file is saved. This can easily be demonstrated, it is not visible in 
the file when viewed with a text editor.

Equally clearly, VBA is not fully compiled to machine language as the code 
runs on Windows PCs and Macs.

It is also clear that the code is interpreted at run-time, a programming error,
such as incorrectly setting a range variable, is not trapped until that code
is executed.

VBA code is translated and stored as machine independent codes, Opcodes. When 
the code is compiled or executed, the Opcodes are translated to Excodes, which 
are specific to the platform.

When you run VBA code which has not been compiled, the compilation is performed
as-needed.

In addition to syntax checking, compiling a VBA project writes out the ExCodes 
and stores them in the workbook.  This improves the workbook's startup time, 
since object references are already resolved where possible, and there is no need
for a compilation when the code is first run. It does not make the VBA code faster,
as VBA compiles at run time if necessary, that is the code is always compiled. 

The #If will always score over a standard IF, as the compilation doesn't compile the 
#If, only the code that satisfies the condition makes it to Excodes. In a standard If, 
all code makes it to Excodes, so that IF is always evaluated at run-time.


If you are interested, there is a white paper on the subject, on the Baarns Group
site, "VBA - Maximum Performance vs. File Size", at http://archive.baarns.com/IE4/index_devonly.asp.


----------



## Richard Schollar

Thanks Bob - I feel like I've learned something today! I am going to check out that site too.

Sapph on the rocks, or as it comes?


----------



## xld

So now you know why I wondered why he didn't use conditional compilation.

Chilled glass, chilled alcohol, but don't water it (I only water my scotch!).


----------



## erik.van.geit

> So now you know why I wondered why he didn't use conditional compilation.
> 
> Chilled glass, chilled alcohol, but don't water it (I only water my scotch!).


Hi, xld,

do not wonder, but try to see the things from the point of view of "lower-skilled" people
You have a large experience and are (close to or real) professional programmer: I'm not at all, just quite interested in this stuff. As you have seen perhaps, I just like to "play"  (a search for bleeding_nose would be helpful  )

I do not know if you're playing piano concerts, but wouldn't wonder if you didn't use your thumbs the first time.   It's just a matter of experience and knowledge. On this webpage I found you a good teacher. So thanks for the explanation.

have a nice "last-day"2006
Erik

PS: now nbrcrunch, are you happy your thread is alive again


----------



## xld

> do not wonder, but try to see the things from the point of view of "lower-skilled" people
> You have a large experience and are (close to or real) professional programmer: I'm not at all, just quite interested in this stuff. As you have seen perhaps, I just like to "play"  (a search for bleeding_nose would be helpful  )



I am glad that you re-joined the thread. Richard asked the questions, but my original query related to code that you posted, so it is good that I now understand.



> On this webpage I found you a good teacher. So thanks for the explanation.



Good, I am glad you enjoyed it and found it useful. Passing knowledge on is one of the most enjoyable aspects of the support forums. 

As you have probably gathered, like Richard I enjoy a drop of the hard stuff. I do not know if you do also, but as you are from Belgium, you could thank me by sending me a 'virtual' Duvel.



> PS: now nbrcrunch, are you happy your thread is alive again



Indeed, there are many interesting topics that could be covered.


----------



## erik.van.geit

thank you for the reply, xld
it's good to start the year on this board reading our last replies   


> As you have probably gathered, like Richard I enjoy a drop of the hard stuff. I do not know if you do also, but as you are from Belgium, you could thank me by sending me a 'virtual' Duvel.


I do not have any beer in my house and "devil" would be the last one to import here  
5 or 6 Kriek (I love "Lindemans" Kriek) each year is my average
I enjoy others things in life  :wink:


----------



## xld

> I do not have any beer in my house and "devil" would be the last one to import here



Never occurred to me that Duvel meant Devil. It's a good beer though, see http://www.thebackpacker.net/worldbeers/duvel_1368_worldbeers.htm



> 5 or 6 Kriek (I love "Lindemans" Kriek) each year is my average
> I enjoy others things in life  :wink:



Am I hearing this right, you average 5-6 beers a YEAR! I average that many (and more) on a night out. But I guess I am English, and we are famous throughout Europe as over-indulging.


----------



## Richard Schollar

> I average that many (and more) on a night out.



You heard the man: self-confessed alcoholic!  I'm sure I have the number for AA somewhere around here - I'll pass it on when I find it 

As for great beers - I love Ringwood Best - I truly believe it *is* the ultimate quaffing ale.  I presume, given your neck of the woods, that you're well acquainted with it Bob?


----------



## xld

Alcoholic, I'm considered a woos by real drinkers!

But I am familiar with Ringwood beers, 12 miles down the road. I have a (small) penchant for their Old Thumper and FortyNiner beers. Haven't gotten round to Bold Forrester, BoonDoggle, Huffkin or Porter beers (I love Porter beers) as pubs just don't seem to stock them, even the brewery doesn't sell two of them (?).

I also have a taste for Tanglefoot, and Waddies 6X, so we are well served (and Palmers down in Bridport).

Anything of note up your way?


----------



## Richard Schollar

Nothing to compare to 6X or Ringwood Best.  I used to audit Palmers Brewery many years ago when I was an auditor.  They had some great tied pubs - the Marquis of Lorne at Nettlecombe was where we were put up by the brewery for a couple of weeks each year.  **** fine place! I must admit I was never taken with the brewery's home-brewed ales though.  Really nice staff at the Brewery - the owners were very nice too.  One audit job I do miss (but not the rest - I hated being an auditor).

Despite my location in my profile, I actually hail from Winchester (well, mid-way between Southampton and Winchester, but I sound classier if I say Winchester!), and the family's originally from Portsmouth way.  I just work in the miserable grey town that is Basingstoke.

Having said that, the Loddon Brewery's Hoppit ain't at all bad.  From nearer to home, the Cheriton Flower Pots' Cheriton Pots Ale is a good 'un, and the pub itself is a great summer pub.  My personal preference for pubbing has to be the Wykeham Arms in Winchester though.

My, my - I'm doing a grand impression of Mr Truby in topic mis-direction here!


----------



## Jon von der Heyden

Hang on there BFG!  I see tonight on BBC that Winchester was voted Britains no. 1 town to live / buy...!

But then unless I'm very much mistaken you're from Eastleigh!  Isn't that the borough where all of Winchesters riff-raff was exiled to?


----------



## Greg Truby

xld,

Nice info on conditional compiling.  Your knowledge of the subject quite outstrips mine.  I do wish you would have been around when I was first trying to sort out conditional compiling.

To anyone else reading this; conditional compiling is something that can come in quite handy when building complex applications.  As _xld_ has already shown, it can come in quite useful when trying to write for multiple versions of Excel, especially knowing about the VBA6 built-in constant.  

Another place I've used it when building an app is that I like to use a userform as a class module for progress indicators.  But when building the app and testing, things get tangled and stepping through code difficult if I'm launching the main routine from a userform.  So I'll put the progress indicator-using code inside conditional compiling directives and set a constant in the VB project properties dialog.  So I can slip the progress indicator code into the project but it doesn't get compiled until I flip the conditional compiling constant's value, making testing and stepping through code much simpler.


And in case anyone needs to know, while you can set a conditional compiling constant to TRUE in code (*#const ccTesting = True*); if you try to set to TRUE in the project properties dialog you will get an invalid syntax error. You have to use 1 or -1 for TRUE and 0 for FALSE.


----------



## Richard Schollar

*OH MY GOD!!!*



> xld,
> 
> Nice info on conditional compiling.  Your knowledge of the subject quite outstrips mine.  I do wish you would have been around when I was first trying to sort out conditional compiling.
> 
> To anyone else reading this; conditional compiling is something that can come in quite handy when building complex applications.  As _xld_ has already shown, it can come in quite useful when trying to write for multiple versions of Excel, especially knowing about the VBA6 built-in constant.
> 
> Another place I've used it when building an app is that I like to use a userform as a class module for progress indicators.  But when building the app and testing, things get tangled and stepping through code difficult if I'm launching the main routine from a userform.  So I'll put the progress indicator-using code inside conditional compiling directives and set a constant in the VB project properties dialog.  So I can slip the progress indicator code into the project but it doesn't get compiled until I flip the conditional compiling constant's value, making testing and stepping through code much simpler.
> 
> 
> And in case anyone needs to know, while you can set a conditional compiling constant to TRUE in code (*#const ccTesting = True*); if you try to set to TRUE in the project properties dialog you will get an invalid syntax error. You have to use 1 or -1 for TRUE and 0 for FALSE.



I can see the headlines now:

TRUBY GUIDES THREAD BACK ON TOPIC!!!!  SHOCK KILLS HALF OF MREXCEL FORUM MEMBERS...


----------



## xld

> Another place I've used it when building an app is that I like to use a userform as a class module for progress indicators.  But when building the app and testing, things get tangled and stepping through code difficult if I'm launching the main routine from a userform.  So I'll put the progress indicator-using code inside conditional compiling directives and set a constant in the VB project properties dialog.  So I can slip the progress indicator code into the project but it doesn't get compiled until I flip the conditional compiling constant's value, making testing and stepping through code much simpler.



I use that case also. Stepping through is bad enough, but it also interferes badly with checking the worksheet, so best switched off (as long as you remember to test again with it switched back on at the end, many times my progress controller hasn't been the best  <G>.



> if you try to set to TRUE in the project properties dialog you will get an invalid syntax error. You have to use 1 or -1 for TRUE and 0 for FALSE.



I must admit, I rarely use the properties to set CC, find it far easier (and more intuitive) using the constant, it is very easy to just set it up as a global constant (even though I abhor global variables in principle). You cane even set it as module scope variables, have different values in different modules, thereby focussing on particular code.

As I mentioned earlier to Richard, I tend to have the testing conditional constant in every module, and I have code to go through every module and set/reset.


----------



## Greg Truby

> I can see the headlines now:
> 
> *TRUBY GUIDES THREAD BACK ON TOPIC!!!!  SHOCK KILLS HALF OF MREXCEL FORUM MEMBERS...*
> -R.Schollær



Aye, 'twas a bitter, bitter pill to swallow, laddie.  And what with it bein' a _lounge_ thread to boot, took me three tries to click that [SUBMIT] button. 



> ...I must admit, I rarely use the properties to set CC, find it far easier (and more intuitive) using the constant, it is very easy to just set it up as a global constant (even though I abhor global variables in principle). You cane even set it as module scope variables, have different values in different modules, thereby focussing on particular code...
> -xld



Shoot, I hadn't ever even paid any attention to the box for it in the project props dialog until I saw something in ****'s blog a while back.  Felt like a real moron when I hopped into the VBE to verify that there really was such a box in that dialog..."hmmm, how long has that been under my nose?"  I do put a comment at the top of any modules that reference the CC indicating that it's to be found in that dialog so that someone reading the code isn't left vainly looking for a #CONST statement.


----------



## Oorang

I have found conditional compiling great for code I plan on releasing using a late-binding approach. I prefer to use the intelisense when I am developing. So I make a #Const that determine early vs. late. Then set it to early and develop away. Then I just go back through my work, and add in the late bound approach. Which is pretty easy considering you just have to replace a few constants and change how you instantiate your object. And presto! To give an example of use, I was making extensive use of the Excel-Libraries from Access. Some users (me included) had Office 2003, which is Excel 11. Some however were on 2000. If you early-bind to Excel on 2003, when a user opens up your app in 2000, the app will break with a missing reference (it won't automatically find the 10.0 library.) To fix it, I needed to use a late bound approach, but I didn't feel like developing everything else without the intelisense. Here is an example for those who care   


		Code:
__


Option Explicit
#Const CCLateBind = True
Sub Example()
    #If CCLateBind Then
        Dim FS As Object
        Dim TS As Object
        Dim F As Object
        Set FS = CreateObject("Scripting.FileSystemObject")
        #Else
        'To use early binding, set a reference to "Microsoft Scripting Runtime", and
        'change the compiler-constant CCLateBind to "False".
        Dim FS As Scripting.FileSystemObject
        Dim TS As Scripting.TextStream
        Dim F As Scripting.File
        Set FS = New Scripting.FileSystemObject
        #End If
    MsgBox "See how this runs without freaking out... Yay!"
End Sub


----------



## Greg Truby

Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about.  Named ranges (or "named formulae" if you like) have a VISIBLE property.  I.e. one can create _hidden_ named items.  In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration?  But I haven't really seen anything that was explicite about when they're particularly useful.  Has anyone used hidden names?  If so, where did the come in useful for you?


----------



## erik.van.geit

> Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about.  Named ranges (or "named formulae" if you like) have a VISIBLE property.  I.e. one can create _hidden_ named items.  In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration?  But I haven't really seen anything that was explicite about when they're particularly useful.  Has anyone used hidden names?  If so, where did the come in useful for you?


I used hidden names to store a passwordsystem on a hidden part of a sheet. The project had hundreds of users with mainly low Excelskills and was updated often. So it was quite safe.
If the names were not hidden, choosing for example the correct item from the namelist and pasting some password would have been too easy: now they needed a button.

I've seen projects with hidden formulas: the developers wanted to "protect" their formulas from being copied.

of course we know all those protecting systems are not very reliable   any more-skilled user can easily undo protections of the kind

kind regards,
Erik


----------



## Oorang

Hiya Greg, thanks for the tip! I figured out how to add invisible names through VBA... It is possible to do so via GUI?  

Hi Erik! Just as an FYI, I noticed invisible names still appear in the names collection. I am sure you understand the implication.


----------



## xld

> Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about.  Named ranges (or "named formulae" if you like) have a VISIBLE property.  I.e. one can create _hidden_ named items.  In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration?  But I haven't really seen anything that was explicite about when they're particularly useful.  Has anyone used hidden names?  If so, where did the come in useful for you?



I use hidden names extensively for any 'application' names, that I don't want users messing with. Using Jan Karel Pieterse's NameManager eases the maintenance.


----------



## xld

> Which is pretty easy considering you just have to replace a few constants and change how you instantiate your object.



Why replace the constants, declare them in the CC



		Code:
__


#Const EarlyBound = False

#If Not EarlyBound Then
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
#End If

Public Sub GetData()
#If EarlyBound Then
Dim oConn  As ADODB.Connection
Dim oRS As ADODB.Recordset
#Else
Dim oConn  As Object
Dim oRS As Object
#End If
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

    sFilename = "c:\Mytest\Volker1.xls"
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & sFilename & ";" & _
                "Extended Properties=Excel 8.0;"

    sSQL = "SELECT * FROM [Sheet1$]"
    
#If EarlyBound Then
    Set oRS = New ADODB.Recordset
#Else
    Set oRS = CreateObject("ADODB.Recordset")
#End If
    
    sSQL = "SELECT * FROM BookLevelName"

    Set oRS = New ADODB.Recordset
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' Check to make sure we received data.
    If Not oRS.EOF Then
        Sheet1.Range("A1").CopyFromRecordset oRS
    Else
        MsgBox "No records returned.", vbCritical
    End If

    ' Clean up our Recordset object.
    oRS.Close
    Set oRS = Nothing

End Sub


----------



## Oorang

I sometimes do that, It just depends on what I think is going to be quicker


----------



## xld

> Hiya Greg, thanks for the tip! I figured out how to add invisible names through VBA... It is possible to do so via GUI?



As with very hidden sheets, not the Excel GUI.But JanKarel Pieterse's NameManager handles them okay.


----------



## xld

> I sometimes do that, It just depends on what I think is going to be quicker



If you keep a list tucked away, this is always quicker.


----------



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



		Code:
__


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 
	
	
	
	
	
	




		Code:
__


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


----------



## 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 _________?
Click to expand...

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,


		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



		Code:
__


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



		Code:
__


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


and look at this alternative



		Code:
__


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


----------



## 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:
	
	
	
	
	
	




		Code:
__


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:



> Code:
> __
> 
> 
> 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



		Code:
__


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?


		Code:
__


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:



		Code:
__


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.


----------



## PA HS Teacher

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



Good find Oorang.   That's much nicer than resorting to print screen like I normally do.


----------



## hatman

How about Offset()?  The first time I found that function through the Equation Builder Wizard, I said to myself "What the heck is THAT good for?"  The help file is nebulous in terms of how it could possibly be useful.  When I finally stumbled upon it's use in Dynamic Named Ranges, I nearly had an apoplexy.  Now I can't imagine building a robust application without it.

And don't forget the use of Match() and Index() in lieu of Vlookup()... invaluable, but these two functions are treated separately in the help files without a single mention that they can be used in conjunction with very powerful results.


----------



## litrelord

Perhaps with the standard warning that OFFSET is volatile etc etc

Very useful all the same though.


----------



## erik.van.geit

this may not be a real "trick" but what looks to a lot of us as a "trick" isn't more then some knowledge gathered through the means available to all of us

so
one of the main tricks of gurus is that they
use the HELPfiles
and are able to find their way on the internet to get what they need


----------



## hatman

Of course, knowing when the downside of volatility is outweighed by the benefits (and knowing how to get the same result with non-volatile functions, if possible) is also part of being a guru.  Though in the example I listed above, I believe that the volatility might be unavoidable.  In other words, the Dynamic Named Range structure, even when implemented with non-volatile functions, seems to behave in a volatile manner anyway.


----------



## hatman

> one of the main tricks of gurus is that they
> use the HELPfiles
> and are able to find their way on the internet to get what they need



EXCELLENT point, Erik.  I am constantly amazed by people who see what I do, ask where I learned, and then skake their heads when I tell them I taught my own self using Help-Files and Internet Articles.  They see that as impenetrable magic.


----------



## Oorang

On the OFFSET function, I tend to really like the MATCH/OFFSET combination for mining data in weird ways. It's slightly more powerful than VLOOKUP (with of course the warnings about volatility as mentioned).
An example would be if you had your data laid out like this "Account#|Name|Balance" and you wanted the name of the guy with the biggest balance you could do =OFFSET(B1,MATCH(MAX(C:C),C:C,0)-1,0).


----------



## hatman

On the other hand, if you are looking for a single cell, Index() will get you there without the volatility of Offset().  As a general rule of thumb, I try to reserve Offset() for those cases where I need a Range or Array as a result, and Index() for all others. In your example, you can do this instead, with the same results:

=INDEX(B:B,MATCH(MAX(C:C),C:C,0))


----------



## Oorang

Good advice hatman


----------



## PaddyD

"On the other hand, if you are looking for a single cell..."

FWIW, index() can return a range:

"INDEX(array,row_num,column_num) returns the value of a specified cell or *array of cells *within array."

...not to mention the index():index() idiom


----------



## hatman

> "On the other hand, if you are looking for a single cell..."
> 
> FWIW, index() can return a range:
> 
> "INDEX(array,row_num,column_num) returns the value of a specified cell or *array of cells *within array."
> 
> ...not to mention the index():index() idiom



I don't follow that (either one)... can you give a couple of examples?


----------



## PaddyD

As in:Book1ABCD112324563789451567Index Examples

=SUM(INDEX(A1:C3,2,0))

...specifying 0 in the column / row argument returns the entire row / column

=SUM(INDEX(A1:C3,1,2):INDEX(A1:C3,2,2))

evaluates to sum(b1:b2). I strongly suspect that the overhead of two calls to index() is less than that of constantly recalculating the offset() equivalent.


----------



## hatman

I had no idea that a zero did that... wow, that's even more powerful than I suspected...

Thanx PaddyD


----------



## Greg Truby

Never had call to use that 2<sup>nd</sup> syntax, Paddy.  Question is will I remember it when I need it.  As for the other, I've always done that by leaving the arg blank.   Are you aware of any downside to leaving it blank versus explicitely dropping in a zero?


----------



## PaddyD

"Are you aware of any downside to leaving it blank versus explicitely dropping in a zero?"

Proves you mean it, much like putting in the brackets to make explicit the fact that you want a calculation to be calculated in the order of operator precendence.  Can't think of anything else.


----------



## hatman

> Never had call to use that 2<sup>nd</sup> syntax, Paddy.  Question is will I remember it when I need it.



Isn't that always the challenge?


----------



## xld

> =SUM(INDEX(A1:C3,2,0))
> 
> ...specifying 0 in the column / row argument returns the entire row / column



Not the whole row/column, just the intersection of the array with the specified row/column.


----------



## PaddyD

"Not the whole row/column, just the intersection of the array with the specified row/column."

Indeed - given the earlier quote for the syntax:

INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells _within array_." 

...had taken that as read, but may as well be explicit about these things!


----------



## nbrcrunch

My goodness. I'm the one that started this thread nearly 2 years ago, complained that no one seemed interest, then disappeared for a while. I come back to find this wealth of contributions! I've transcribed the ones I can use. Thanks to all you experts.

(For the past several months I learned a great deal about SQL and performing "business analysis." What little need I've had for VBA has come in handy, but I definitely am not working at the level many of you seem to need for VBA.)

I've wanted to learn, but unless there's a business need, I usually focus on the matters at hand.


----------



## erik.van.geit

> My goodness. I'm the one that started this thread nearly 2 years ago, complained that no one seemed interest, then disappeared for a while. I come back to find this wealth of contributions! I've transcribed the ones I can use. Thanks to all you experts.


Aha, WELCOME back,
I did almost email you to inform you of the good news


----------



## ExcelChampion

Going back a couple of posts regarding named ranges and INDEX/MATCH, I personally like to throw the INDIRECT function in there as well...

=INDEX(INDIRECT("Data!$F:$F"),2):INDEX(INDIRECT("Data!$F:$F"),lRow)

(where lRow is MATCH(9.99999999999999E+307,'Data'!$I:$I)

Now before you all start jumping on me telling me INDIRECT is volatile, let me explain why I do this...better yet, set up your own example with data in column F and use the formula above _with out _the INDIRECT function, and then delete column F...You will get the REF error in your named range...something you won't get with INDIRECT. 

And it works with rows, too.  Something very common that happens is when an end user wants to start a data set over (say for a new year) with new data, instead of just clearing the rows' contents, he deletes the rows...resulting in the REF error.


----------



## hatman

Keep a library of useful functions and routines.  Encapsulate where possible for easy code re-use in multiple projects.

Along these lines, as you build your library, have a site this up your sleeve, with tried and true code snippets for basic operations.


----------



## mortgageman

This is a link to John's tip for find (ctrl F).


----------



## Oorang

Nice! I would never have found that one.


----------



## hatman

Hmmm, I guess I never thought much about it.  Shift-Tab moves you backwards through any form, as everyone knows.  I tend to use the combination in any case where forward movement is default, but I want to move backwards instead... you'd be surprised where it works, once you start experimenting in various applications


----------



## hatman

Learn to use the ListView control in UserForms rather than the more common ListBox.  Yes, you'll need to ADD it to your toolbox.  Right click the ToolBox and check off "Miscrosoft Forms 2.0 ListBox".

ListViews are incrediably powerful, and make your projects look and feel more professional.  In the .View = 3 (report) mode, they alow you to present multiple columns of data with HEADERS for each column.  With the addition of this piece of code:



		Code:
__


Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

    With ListView1 '// change to the name of the list view
        Static iLast As Integer, iCur As Integer
        .Sorted = True
        iCur = ColumnHeader.Index - 1
        If iCur = iLast Then .SortOrder = IIf(.SortOrder = 1, 0, 1)
        .SortKey = iCur
        iLast = iCur
    End With

End Sub

 the user can click a column header, and get the list sorted on that column, either Ascending or Descending, just like Windows Explorer.

Another nice creature feature for the user is the ability to control the column width... something that can't be done with a ListBox, and can get annoying when the data length varies greatly, especially f the programmer failed to make a column wide enough for some of the larger text strings.

The addtion of the Key property for each ListItem also gives the programmer a very fast and efficient method of building a list of unique entries when the original data contains duplicates.  Check out how compact this code is for building a two column list that has duplicate Part Numbers in the original data:



		Code:
__


    For Cnt6 = 2 To Sht.Range("C65536").End(xlUp).Row
    
        P_Num = Sht.Range("C" & Cnt6).Value
        
        On Error Resume Next
            
        Set L_Item = Tool_Chooser.ListView1.ListItems(P_Num)
        
        On Error GoTo 0
        
        If L_Item Is Nothing Then
        
            Tool_Chooser.ListView1.ListItems.Add Key:=P_Num, Text:=P_Num
            
            Set L_Item = Tool_Chooser.ListView1.ListItems(P_Num)
            
            L_Item.SubItems(1) = Sht.Range("D" & Cnt6).Value
            
        End If
        
        Set L_Item = Nothing
     
    Next Cnt6


----------



## Oorang

I see you can add items to this control manually, is their a way to link it to a record source (Like in Access)? (I found a nice example online here: http://www.freevbcode.com/ShowCode.asp?ID=7554 but it loads a recordset and loops through it. I prefer to avoid that when possible.)


----------



## hatman

I don't think there is anything native, like with a Listview... so if that is your application, you need to balance the pros and cons.  But  here is a site claiming to have some code to do the binding using a function.

Something neat I found while I was looking into this is the AllowColumnReorder property.  It does just what it says, and effect is superb with many columns.

Also of note is the ability to specify 0 width columns... very cool for holding data that needs to stay with the records, but don't want the user to view.


----------



## Oorang

I could see how that would be helpful. In fact this opens up a whole branch of interesting controls which are the windows common controls. Since I started digging around, I have found that I rather like the imagelist and treeview controls. I have hit on something I did not know by accident:
If you are like me and prefer to use early binding (so I can cheat and use the intellisense) for everything you can early bind with active x controls using the object property:


		Code:
__


Dim tvc As MSComctlLib.TreeView
Set tvc = Me.TreeView1.Object


----------



## hatman

Yeah, Treeview is VERY cool.  I have only scratched the surface on that one, having used it for the first time on a project last month for my boss (man, I needed to hand his eyes back to him so he put hem back in his head, when he saw it).  One thing that seems cumbersome, though, is the need of the ImageList control to store images.  Though in all honesty, I haven't tried using that, so I don't know how bad it is.


----------



## Greg Truby

Paul,

If you ever get stuck on treeview stuff, let me know.  I did quite a bit with one in a project last year.  Like you said, a very neat control, but can also be a bit frustrating if you're a self-edumacated programmer like myself and having to bootstrap it.


----------



## hatman

Actually, found this little article on Colo's page, which was VERY useful.  I had also purchased the 3-volume Microsoft Visual Basic 6.0 Reference Library a few years ago, and used the second volume for the first time as I tried to get my brain around the TreeView control.  Between the 2 resources, I think I have a pretty good handle on it.  

And yes, I also don't have a diplomica that says 'Puter Programming on it.  But I have gotten pretty good at boot-strapping it.


----------



## Greg Truby

Oh, neat link.  Thanks.  Won't have time to really study it and play any time soon, but someday... someday...


----------



## Oorang

OK Here's one. I was making a change password form for Access and I noticed that it was displaying the username all funky-like. I wanted to display the username in the actual case of the username (yes I know the username is not case-sensitive) and it was displaying all lower case. And that was when I noticed that Access.CurrentUser returns the username that the user logged in with. So if they logged in with all caps, you get the username in all caps. However Access.DBEngine.Workspaces(0).UserName will return the username in the case that it was assigned in. Not a huge deal but useful to know to avoid having to worry about case conversion etc. (Not mention it displays prettier too.)


----------



## hatman

Okay, so how does that translate into Excel?  I use environ("username"), and just got burned by a case discrepency... changed to strcomp(), but might be nice to have an alternative.


----------



## SydneyGeek

I use Environ("username") too. Very simple, but in Access 2003 it's a pain because of the Sandbox mode that MS introduced for security. 

Aaron's approach of listing the current user works if you use user-level security in Access. AFAIK it doesn't translate to Excel 

Denis


----------



## Oorang

Hi Hatman, 
It is somewhat limited in application, but it was just one of those things that bugged me because the usernames were assigned like this:  JDoe FSmith, etc. There is no case conversion that will make them display properly if the user changed case when they logged in. But since it was bugging me and I eventually found the solution, I thought I would post it up. 
As far as Excel goes, there are actually half a dozen dlls that will grab the username in some way... A few of those methods are described here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=768 (They did not mention the DAO method but I think this is more Excel targeted.) I am not sure, but I suspect most dlls are just tapping the API call in the first example anyway. So if you were looking for the "right way" you could use the API. The other thing to consider instead of strcomp might be "Option Compare Binary" but remember that will affect the comparisons of the entire module.


----------



## hatman

Rock ON!  I'll have to look at that in more depth this afternoon.


----------



## Oorang

Hmm not sure if this is a guru trick either but still good to know. In Microsoft Access, if you turn on "Require Variable Declaration" in the VBE, it will put "Option Explicit" underneath "Option Compare Database". Which is a bit of a problem because "Option Explicit" needs to be above "Option Compare Database" to work properly. If you do not correct it when building a module, it could cause some unanticipated results.


----------



## hatman

One I just learned, after using the msgbox() function for many years..

The msgbox() function supports multiple arguments in the Style parameter: use them if it makes your message more meaningful: 
	
	
	
	
	
	




		Code:
__


MsgBox "Do you want to try again?", vbMsgBoxHelpButton + vbYesNo + vbCritical, "Oops", ThisWorkbook.Path & "\" & Help_File_Name, 2


----------



## Greg Truby

> ...The msgbox() function supports multiple arguments in the Style parameter...
> ~hatman



That's what's so amazingly funny about programming in VBA.  Here you've developed this amazing app w/ userforms and literally hundreds of controls and yet you just learned something very basic about MsgBox() of all things.  (I just learned of the existance of the VBA function MONTHNAME() a couple of weeks ago.)  Personally, I'll also add in the vbDefaultButton2 arg on OKCancel boxes when requesting confirmations on a particularly potent action where I cannot or have not developed an "undo".

Just a note to newbies: when using the vbYesNo and testing the response, you must test against the *VB* constants, *not the XL* constants.  xlYes≠vbYes and xlNo≠vbNo.

<h3>Correct</h3>
	
	
	
	
	
	




		Code:
__


If vbYes = MsgBox("Is Hatman a stud?", vbQuestion + vbYesNo + vbDefaultButton2, "Hi, Paul") Then


<h3>Incorrect</h3>
	
	
	
	
	
	




		Code:
__


If xlYes = MsgBox("Is Hatman a stud?", vbQuestion + vbYesNo + vbDefaultButton2, "Hi, Paul") Then


----------

