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.
Month: June 2005
Download PivotTable Parameters
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:
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
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.
You can read a bit more about how this works here
Example (selecting the cells with a white background fill):
vbWhite).Select<br /><span style=”color:#00007F”>End</span> <span style=”color:#00007F”>Sub</span></font>
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Finding Modules and Procedures
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.
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
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
If we add some VBA code to a worksheet’s code module, such as:
MsgBox “I am “ & Me.Name
End Sub
we can call that procedure from a different module (but within the same project) using code like:
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:
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):
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:
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
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.
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
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.