tricks of gurus

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

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:

    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:

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.