********************************** COMMENTS Welcome to the 19th issue of the Excel Experts E-letter (or EEE), by David Hager. EEE is normally a monthly publication. Feel free to distribute copies of EEE to your friends and colleagues and to contribute your Excel gems to EEE so that others can benefit from your work. Back issues are available for download from the EEE web page located on John Walkenbach's web site. New issues are normally available on the 1st of each month. There will be periods when EEE is not published due to time and travel constraints. http://www.j-walk.com/ss/excel/eee/index.htm ********************************** Top Excel Sites See: http://www.officevba.com for VBA articles and downloadables files of the highest order. ********************************** POWER FORMULA TECHNIQUES by Leo Heuser and Eero Tibar How can you get a list of unique entries in an n * m array by using a worksheet formula? Example: Assuming data in B5 : GR10, enter this array formula in e.g. G12. G11 must be empty or, if it has a value, this value must not occur in B5 : GR10. =OFFSET($B$5,MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,ROW($B$5:$GR$10)- ROW($B$5))),MOD(MIN(IF(COUNTIF($G$11:G11,$B$5:$GR$10)=0,ROW($B$5:$GR$10)- ROW($B$5) +(COLUMN($B$5:$GR$10)-COLUMN($B$5))/1000)),1)*1000) Drag down until the value in G12 begins repeating. Here is slightly different approach to extract unique items from a N*M table (named as "tbl" in the formula). Type "Unique items from the table" in A1 and enter the following formula as an array into A2 and copy it down. =INDEX(tbl,MIN(IF(COUNTIF($A$1:A1,tbl)=0,ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0,COUNTIF($A$1:A1,INDEX(tbl,MIN(IF(COUNTIF($A$1:A1,tbl)=0,ROW(tbl) -MIN(ROW(tbl))+1)),,1)),0),1) ********************************** VBA CODE EXAMPLES by David Hager I like using the Pick List feature in Excel 97 (and later), but I don't like having to select the menu item for that feature everytime I go to a new cell. How can I solve this problem? Place this event procedure in the ThisWorkbook module. Then, any time you select a cell where the pick list would pop up when called from a menu item, it will instead pop up automatically. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _ As Excel.Range) On Error Resume Next With Target If .Value <> "" Then Exit Sub If .Row = 1 Or .Row = 65536 Then Exit Sub If .Offset(-1, 0).Value = "" And .Offset(1, 0).Value = "" Then _ Exit Sub If Selection.Address <> .Address Then Exit Sub End With Application.EnableEvents = False SendKeys "+{F10}k" Application.EnableEvents = True End Sub by Bob Umlas How can you give users a Print Preview and not allow them to change any of thesettings? Use the following procedure. It disables key buttons at the top of the preview window. Sub PrtPvw() ActiveSheet.PrintPreview False '"False"==> no changes allowed ActiveWindow.View = xlNormalView ' In case user switched to Page Brake Preview. End Sub by Bob Umlas How can I step through a For-Next loop by using a custom step process? If you need to loop through code with an index which takes on specific values like 1,4,5,18,28,33,34,85 instead of the more commom 1,2,3,4,5,6.. or 1,3,5,7,..., then you can use this technique: Sub OddLoop() For i = 1 to 8 j=Array(1,4,5,18,28,33,34,85)(i) 'Now use j as your subscript Next End Sub ********************************** POWER PROGRAMMING TECHNIQUE by Bob Umlas This procedure contains VBA code to add to your existing VBA code -- basically, it puts one statement at the beginning of each procedure in every module (class modules and event procedures not included). This statement is a call to a routine (which YOU need to write) and passes the sub/function name. For example... Before: Sub ABC() Dim i as Integer For each x in sheets Next End Sub Sub xyz() End Sub After: Sub ABC() MyProc "ABC" Dim i as Integer For each x in sheets Next End Sub Sub xyz() MyProc "xyz" End Sub Notice that afterwards, there's a new line immediately after the sub. It calls MyProc (this is changeable) and passes the name of the procedure it's in. You can use MyProc to trace flow, track the time, etc -- you can get creative here! There are 2 main routines: Addit, and Deleteit. Running Addit will insert the one-liner, running Deleteit will remove this one-liner. The code is inserted into the active workbook. The first line inside the VBE for AddALine.xls is: Public Const TheProcName As String = "MyProc" '============CHANGE THIS LINE and whatever you change "MyProc" to will be the routine called inside each procedure of your code. Public Const TheProcName As String = "MyProc" '===============CHANGE THIS LINE Sub Addit() '=========================== 'RUN THIS CODE TO INSERT THE LINE INTO THE ACTIVE WORKBOOK's CODE '=========================== AddALine MsgBox "Done....Don't forget to write procedure " & _ TheProcName & "!", vbExclamation End Sub Sub Deleteit() '=========================== 'RUN THIS CODE TO DELETE THE LINE '=========================== DelALine MsgBox TheProcName & " has been deleted from each procedure." End Sub Sub AddALine() Dim ProcName As String, ProcNames() As String, Boo As Boolean Dim LngR As Long, TheLine As Long, LngI As Long Set VBP = ActiveWorkbook.VBProject nocomponents = VBP.VBComponents.Count On Error Resume Next For i = 1 To nocomponents If VBP.VBComponents(i).Type = 1 Then 'module With VBP.VBComponents(i).CodeModule If .Name = "ModInserter" Then GoTo NextOne col = .CountOfLines codl = .CountOfDeclarationLines ProcName = .ProcOfLine(codl + 1, LngR) If ProcName = "" Then GoTo NextOne If LngR <> 0 Then GoTo NextOne TheLine = .ProcBodyLine(ProcName, vbext_pk_Proc) thetext = .Lines(TheLine, 1) If Right(thetext, 1) = "_" Then j = 2 Else j = 1 .InsertLines TheLine + j, TheProcName & """" & _ ProcName & """" LngI = codl + 1 col = col + 1 2: If LngI > col Then GoTo 1 If ProcName <> .ProcOfLine(LngI, LngR) Then ProcName = .ProcOfLine(LngI, LngR) If LngR <> 0 Then GoTo 3 TheLine = .ProcBodyLine(ProcName, vbext_pk_Proc) thetext = .Lines(TheLine, 1) If Right(thetext, 1) = "_" Then j = 2 Else j = 1 .InsertLines TheLine + j, TheProcName & """" & _ ProcName & """" col = col + 1 End If 3: LngI = LngI + 1 GoTo 2 1: End With End If NextOne: Next End Sub Sub DelALine() Dim ProcName As String, ProcNames() As String, Boo As Boolean Dim LngR As Long, TheLine As Integer, LngI As Integer If MsgBox("Are you sure you want to delete " & TheProcName & _ " from each procedure?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Set VBP = ActiveWorkbook.VBProject nocomponents = VBP.VBComponents.Count On Error Resume Next For i = 1 To nocomponents If VBP.VBComponents(i).Type = 1 Then 'module With VBP.VBComponents(i).CodeModule If .Name = "ModInserter" Then GoTo NextOne col = .CountOfLines codl = .CountOfDeclarationLines ProcName = .ProcOfLine(codl + 1, LngR) If ProcName = "" Then GoTo NextOne If LngR <> 0 Then GoTo NextOne TheLine = .ProcBodyLine(ProcName, vbext_pk_Proc) thetext = .Lines(TheLine, 1) If Right(thetext, 1) = "_" Then j = 2 Else j = 1 If Left(.Lines(TheLine + j, 1), 5) <> Left(TheProcName, 5) Then ' MsgBox TheProcName & " not found in procedure """ & _ ProcName & """... ignoring" GoTo 22 End If .DeleteLines TheLine + j, 1 22: LngI = codl + 1 2: If LngI > col Then GoTo 1 If ProcName <> .ProcOfLine(LngI, LngR) Then ProcName = .ProcOfLine(LngI, LngR) If LngR <> 0 Then GoTo 3 If ProcName = "" Then GoTo 3 TheLine = .ProcBodyLine(ProcName, vbext_pk_Proc) thetext = .Lines(TheLine, 1) If Right(thetext, 1) = "_" Then j = 2 Else j = 1 If Left(.Lines(TheLine + j, 1), 5) <> _ Left(TheProcName, 5) Then ' MsgBox TheProcName & " not found in procedure """ & _ ProcName & """... ignoring" GoTo 3 End If .DeleteLines TheLine + j, 1 End If 3: LngI = LngI + 1 GoTo 2 1: End With End If NextOne: Next End Sub Sub Showcode() MsgBox "Before running ""Addit"", activate the workbook whose code " & _ "this routine will update." MsgBox "Change ""TheProcName"" to the name of the procedure you want " & _ "to run for each sub." Application.SendKeys "{up}{up}" Application.Goto "Addit" End Sub ********************************** DEVELOPER TIPS by Chip Pearson and Stephen Bullen Why use class modules? Basically, a Class is the definition of an Object. The word "object" is deliberately vague. And object is anything that you want to design. It is defined entirely (mostly) by its properties, methods, and events. In Excel, there are hundreds of "built-in" objects, all defined by class modules. The "class" is the definition of an "object". For example, a Worksheet is an object. And there is a class module which defines just what a Worksheet really is. There are various properties of a Worksheet object (e.g., Visible). Properties simply define and set various attributes. Think of properties as "Adjectives" which describe an object. An object also has Methods. Methods are the "Verbs" of objects. For example, a Worksheet object has an Activate method. This causes something to happen. Finally there are Events. I can't think of a good grammatical analogy for events. Essentially, Events are how an object tells the rest of the world that something happened. For example, in a Worksheet object, there is a Change event. This is the Worksheet object's way of telling the rest of world, "Hey, look at me, I changed". The rest of the world can ignore that event, or it may take action. But the world has been told that object has done something (or had done something to it). Now, you use Class Modules to create your own objects. Suppose you were writing an application that was used for employee tracking. Using a class module, you would define your own object called "Employee". This class would define a single, generic, employee. With the DIM and SET statement, you can create a specific employee, based on the "template" or "definition" of a generic employee. The Employee class would have several Properties, such as Name, Address, and Salary. It could also have methods, such as Promote, GiveRaise, and Fire. In your application, the Promote method would do the same things -- e.g., increasing the Salary property, updating a central database, sending an email to another department to buy him a nicer computer, etc. These actions are all the same whenever you Promote any employee. By using a Class Module to define a "generic" employee, you only have to write the code once. Then to work with a *specific* employee, you just call the methods for that employee: Dim ThisEmp As CEmployee ' more code Set ThisEmp = New CEmployee ThisEmp.Name = "John Smith" ' more code ThisEmp.Promote All of the code related to the Promote event is contained in the Class modules (the definition of any employee), so you can simply call the Promote method. Once you've defined the Class, you never have to worry about what Promote actually does. Here's another way to think about it. In the Worksheet object, there is a PrintOut method. Within the PrintOut method, there is all the code that actually formats the worksheet for printing, determines what printer you have, and actually does all the work of printing the sheet. As a VBA programmer, you don't have to worry about any of that. You simply call PrintOut, and let that do all the work for you. You don't have to worry about what sort of printer the user has, whether it can print color, and a hundred other things. You just call PrintOut and let the Object do all the work. Class Modules let you create you own objects, or extend the functionality of other, existing objects. They are very useful because they allow you to write the code once, and then simply create new objects based on the class (think of it like a blueprint for a house). It is write the code once, and use it many times. For example, I have a class module that extends the functionality of a standard list box. The standard list box doesn't have a MoveUp method, which simply moves the selected item one row up in the list. By using a Class Module, I added a MoveUp method (as well as MoveDown, MoveToTop, MoveToBottom, etc). I wrote that class one time. Now, whenever I need to use "better" list boxes in my applications, I just use that Class. I don't have to "re-invent the wheel" for every application I write. This just scratches the surface of what a Class is and how to use them. If you've ever heard the term "object oriented", Classes are the foundation of this entire design philosophy. Just to provide the opposite end of the spectrum to Chip's excellent answer, class modules can also be though of as user-defined types (UDT) on steroids. A simple UDT can be used to store related information about a particular thing, such as Chip's employee: Type Employee Name As String DOB As String Grade As String Salary As Double End Type If you wanted to do stuff with an employee, you'd use a normal procedure somewhere: Sub RaiseEmployee(uEmp As Employee, sNewGrade As String) 'Validate Grade '... uEmp.Grade = sNew Grade 'Do stuff to work out new salary etc. '... End Sub Sub FireEmployee(uEmp As Employee) uEmp.Grade = "F" uEmp.Salary = 0 End Sub Sub SetSalary(uEmp As Employee, dNewSalary As Double) 'Validate Salary '... 'Does new salary mean a new grade? '... End Sub etc. That's fine as far as it goes and you can create some great programs without ever using class modules. The main thing wrong with it is that the *data* for the object (i.e. the contents of your UDT) is separated from the *actions* that are performed on the data (the RaiseEmployee and FireEmployee subs). Hence, you have to be very careful that the same validation is performed in each sub and that one sub doesn't alter the data in a way that will cause another sub to fail; this is often the cause of some of the hardest bugs to find - logic problems. If you use a class module instead, you can include the validation and other functionality *with* the data; to the extent that the data can *not* be changed unless it's valid. You can think of it as that the 'Grade' property of the Employee (for example) can validate *itself* and can refuse to be updated, or it can know *itself* that when it changes to a valid new grade, it needs to change the salary too. In the example above, with two simple procedures, think what would happen if we had to add another check before changing the grade, or introduce a new action to be performed if the grade is changed (such as notifying their manager). In the procedural approach, we'd have to change two or three routines to handle it - i.e. wherever the grade is set. In the clas module approach, it is simply another action to be performed by the 'grade' property *itself* - none of the other code needs to know about it. i.e: Dim msGrade As String 'Data that only code in the class can 'see' 'Property to read the grade Public Property Get Grade() As String: Grade = msGrade: End property 'Property to set the grade Public Property Let Grade(sNew As String) If Not sNew Is Valid Then Err.Raise "Not a valid grade" Exit Property End If 'Grade is valid, so we can safely store it msGrade = sNew 'Now what else do we need to do when the grade changes? Select Case sNew Case "F" 'Being fired, better ask for a redundancy slip Salary = 0 Case "M" 'Being made a manager, better ask for a better car 'Increase the Salary too Case "D" 'Being demoted, schedule for more frequent reviews 'Decrease the Salary 'etc End Select End Property Public Sub Fire() Grade = "F" End Sub Now, everything that needs to be done when the grade is changed has been made an *integral* part of changing the grade - there's no way that the grade can be changed by anywhere else in the system without those checks and actions happening. Really, though, it just boils down to a different design and development style, and one that hopefully takes us further down the road of improved code reuse, more stability and fewer opportunities for bugs to creep in. The hardest thing to work out, though, is to decide which functionality should be 'in' the class module and which should be on the outside, but using the class module. For example, do we have a '.Fire' method within the class, or a Fire(oEmp As Employee) procedure outside that just sets the grade to "F"? I find that I'm using class modules more and more; it's almost at the stage where if I'm asked "Why use a class module", my reply is "Why not?" ********************************** Issue No.19 OF EEE (PUBLISHED 31May2000) Next issue scheduled for 05July2000. BY David Hager dchager@compuserve.com **********************************