Excel User Conference

Posted on June 23, 2005 by Dick Kusleika

There’s going to be an Excel User Conference in Fort Worth, TX this September. You can read about it at exceluserconference.com. They have a nice list of presenters. I’m considering going, I just need to see what my September schedule is going to be like. Leave me a comment if you’re going.

Download PivotTable Parameters

Posted on June 23, 2005 by Dick Kusleika

There were a lot of comments that people couldn’t get parameters into their pivot tables. I didn’t save the workbook with which I made that post, but I was able to recreate it. You can download it:

Download PivotTableParameters.zip

I didn’t have any trouble recreating it, other than changing the Sheet2 reference in the code to Sheet1. I’m sure I’m just blowing by a step and not explaining it. Hopefully looking at the workbook will shed some light on why others couldn’t get it to work.

Note that the external data that my pivot table points to will not likely be in the same path on your machine. From the Immediate Window, my stuff looks like this:

?sheet1.PivotTables(1).pivotcache.connection
ODBC;DSN=MS Access Database;DBQ=C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind.mdb;DefaultDir=C:Program FilesMicrosoft Office 2000OfficeSamples;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

?sheet1.PivotTables(1).pivotcache.commandtext
SELECT Invoices.PostalCode, Invoices.ExtendedPrice
FROM ‘C:Program FilesMicrosoft Office 2000OfficeSamplesNorthwind’.Invoices Invoices
WHERE (Invoices.PostalCode=?)

You’ll have to change those paths to get it to work on your machine. You may, however, not need it to work, but just seeing the differences between this workbook and yours may be all you need.

Finding cells matching a specific property using the CallByName function

Posted on June 23, 2005 by jkpieterse

Hi everyone!

I thought it would be nice to have a generic VBA function to which we could pass a range object, a string indicating a property of the object and the property’s value, which would then return all cells matching that criteria.

I decided it was time to explore the CallByName function, introduced with Office 2000 and put it to use in the code below.

Function FindCells(ByRef oRange As Range, ByVal sProperties As String, _         ByVal vValue As Variant) As Range     Dim oResultRange As Range     Dim oArea As Range     Dim oCell As Range     Dim bDoneOne As Boolean     Dim oTemp As Object     Dim lCount As Long     Dim lProps As Long     Dim vProps As Variant     vProps = Split(sProperties, “.”)     lProps = UBound(vProps)     For Each oArea In oRange.Areas         For Each oCell In oArea.Cells             Set oTemp = oCell             For lCount = 0 To lProps – 1                 Set oTemp = CallByName(oTemp, vProps(lCount), VbGet)             Next             If CallByName(oTemp, vProps(lProps), VbGet) = vValue Then                 If bDoneOne Then                     Set oResultRange = Union(oResultRange, oCell)                 Else                     Set oResultRange = oCell                     bDoneOne = True                 End If             End If         Next     Next     If Not oResultRange Is Nothing Then         Set FindCells = oResultRange     End If End Function

You can read a bit more about how this works here

Example (selecting the cells with a white background fill):

Sub UseFindCellsExample()     FindCells(ActiveSheet.UsedRange, “Interior.ColorIndex”,
vbWhite).Select End Sub

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Finding Modules and Procedures

Posted on June 21, 2005 by Dick Kusleika

Created by Stephen Bullen
The problem – you want to programatically obtain the name of the
VBComponent that contains a specified procedure. Stephen’s solution
was to look for unique strings, since the VBIDE object model does
not provide functionality for doing this directly.

Sub TestIt()

MsgBox fnThisVBComponent(ThisWorkbook, “This Unique String”).Name & “, “ & _
fnThisProcedureName(ThisWorkbook, “Another Unique String”)

End Sub

Function fnThisVBComponent(oBk As Workbook, sUniqueString As String) As VBComponent

Dim oVBC As VBComponent

‘Loop through the VBComponents in the given workbook’s VBProject
For Each oVBC In oBk.VBProject.VBComponents

‘Using it’s code module
With oVBC.CodeModule

‘See if we can find the unique string
If .Find(sUniqueString, 1, 1, .CountOfLines, 1000, True, _
True, False) Then

‘Found it, so return the VBComponent where it was found
Set fnThisVBComponent = oVBC
Exit For
End If
End With
Next

End Function

Function fnThisProcedureName(oBk As Workbook, sUniqueString As String) As String

Dim oVBC As VBComponent
Dim lStart As Long, sProcName As String, vaProcs As Variant, vProcType As Variant

‘Specify the row number of where to start the find. This is set by
‘the Find method to give the (starting) line number where the text
‘was found. lStart = 1

‘Loop through the VBComponents in the given workbook’s VBProject
For Each oVBC In oBk.VBProject.VBComponents

‘Using it’s code module
With oVBC.CodeModule

‘See if we can find the unique string
If .Find(sUniqueString, lStart, 1, .CountOfLines, 1000, True, _
True, False) Then

‘We found it, so make an array of the available procedure
‘types to check for

vaProcs = Array(vbext_pk_Proc, vbext_pk_Get, vbext_pk_Let, _
vbext_pk_Set)

‘Loop throguh the procedure types
For Each vProcType In vaProcs

‘Get the name of the procedure containing the line we
‘found above
sProcName = .ProcOfLine(lStart, CLng(vProcType))

‘Did we get a procedure name?
If sProcName <> “” Then

‘We did, so return it
fnThisProcedureName = sProcName
Exit For
End If
Next

Exit For
End If
End With
Next

End Function

Be sure to set a reference to the Microsoft Visual Basic for Application Extensibility Library.

Handling Worksheet control events using a class module

Posted on June 17, 2005 by jkpieterse

Hi everyone.

When utilising controls from the Control Toolbox on worksheets, often one needs to use event code to handle the actions a user takes with them.

If however a large number of controls is used, this may become unwieldy because one has to add an event sub for each one of them. This article gives an example how one can use a class module with a single event subroutine for a set of (identical) controls.

Here’s a screenshot of the controls I’ve hooked up to a single event handler:

Exposing VBA code through Excel objects

Posted on June 17, 2005 by Stephen Bullen

If we add some VBA code to a worksheet’s code module, such as:

Public Sub ShowName()
MsgBox “I am “ & Me.Name
End Sub

we can call that procedure from a different module (but within the same project) using code like:

Sheet1.ShowName

Now, as well as exposing the ShowName method through the internal Sheet1 object, Excel also makes it available through the Worksheet object, so we can call the same routine from any module in any workbook using code like:

Workbooks(“Book1.xls”).Worksheets(“Sheet1”).ShowName

We can also use object variables to call the method, but only if they’re declared As Object (it works with .Worksheets() as that returns a generic Object type rather than a Worksheet type):

Dim objTheSheet As Object
Set objTheSheet = Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
objTheSheet.ShowName

Similarly, any public procedures we add to the ThisWorkbook class can be accessed through the Excel workbook object, so we can do things like:

‘In a workbook’s ThisWorkbook module
Public Property Get Mine() As Boolean
Mine = True
End Property

Public Sub ShowName()
MsgBox “I am “ & Me.Name
End Sub

‘In a standard module in a different workbook
Sub ShowMyBooks()
Dim objBook As Object
Dim bMine As Boolean

For Each objBook In Workbooks

‘Does it have a “Mine” property, returning True?
bMine = False
On Error Resume Next
bMine = objBook.Mine
On Error Goto 0

‘Yes, so it’s one of ours that we can do stuff with
If bMine Then
objBook.ShowName
End if
Next
End Sub

As well as being more object-orientated (for what that’s worth), this neatly avoids some of the pitfalls of using Application.Run, such the (in)ability to pass parameters ByRef. We also avoid relying on custom document properties or defined names to identify the workbook/sheet as one of ‘ours’ (both of which can easily be broken by the user).

Regards

Stephen Bullen

Starting Handicaps

Posted on June 16, 2005 by Dick Kusleika

In the Handicap UDF, there’s a call to another UDF: GetStartingHC. Every golfer has a handicap to start and it’s stored in TblPlayers. For most golfers, it’s a carry over from last season. For new members, it can be their USGA handicap. When a golfer doesn’t have three scores, the starting handicap is used as one of the scores.

Public Function GetStartingHC(ByVal lPlyr As Long) As Long

Dim rsPlayers As ADODB.Recordset

Const sBEGHC As String = “BegHC”

If gadoCon Is Nothing Then
InitGlobals
End If

Set rsPlayers = New ADODB.Recordset
rsPlayers.Open “TblPlayers”, gadoCon, adOpenDynamic

rsPlayers.MoveFirst
rsPlayers.Find “Player = “ & lPlyr

If Not rsPlayers.EOF Then
GetStartingHC = CLng((rsPlayers.Fields(sBEGHC) / 0.8) + 36)
End If

End Function

Nothing too fancy, just using the Find method to get to the proper record quickly. This recordset is definitely a candidate for moving global so it doesn’t need to be created and destroyed every time the function is called. I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.

Outlook for the Blind Redux

Posted on June 14, 2005 by Dick Kusleika

My brilliant piece of altruistic code posted in Get Outlooks Currently Selected Time isn’t all that it’s cracked up to be. Had it been for anyone except a blind guy, it would have been just a poor hack job. But for him, it’s practically unusable. You would think the visually impaired would have no need for code such as Application.ScreenUpdating = False. You would think screen flicker would be the least of their concerns. However, the screen reading programs tries to read the screen when it’s updated, so it’s a big mess.

I haven’t been able to find a way, in Outlook, to stop the screen from updating. If you know of a way, please drop me a line.