Issue No. 19 (June 1, 2000)

[Item URL]

**********************************

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

**********************************