Key Card Software (Auric)

[Michael Harrison]

Auric, Are you there?
A little while ago you wrote for me a routine which checked security key card data for absentees. The routine is quite useable but has some minor issues which I have been unable to fully understand, let alone resolve. I wonder if you would be kind enough to take a look and see if you can discover the problem. I enclose all of the relevant information at the end of this message.
Part of the problem is that when I run the two routines, setup4testing and absentee check, everything works perfectly except that absentee check does not use the last date in column A sheet one of setup4 testing.
When I run the routine, absentee check, on the actual data received from the key card software, the second row on sheet two, below the key card numbers, has a zero in each cell, the routine does include the last date in column A from sheet one, the results on sheet 2 are accurate but the routine hangs up on the line:-
“cards(Sheet1.Cells(L0, 3).Value)=True”
Calling a Run-time error “9” subscript out of range
The only apparent difference between the data generated by setup4testing and the actual key card software is that several cards on the key card software are used every day and therefore do not have any absentees to report on sheet two.
Here is the reference information:-
Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electronic lock to gain entrance to a building?
The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.
The key card software produces data which can be imported into Excel 2007 in the following manner:-
Sheet 1
Cell A1 contains the date on which the key card was used. (DD/MM/YY format)
Cell B1 contains the time that the key card was used. (This column is not pertinent to this problem)
Cell C1 contains the identification number of the key card used to open thedoor.
The dates in column A start at the earliest and proceed down the sheet, inorder, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used on one or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.
The key card numbers in column C are in random order.
The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)
The desired readout would be as follows:-
Sheet 2
Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.
The desired result when the formulae run would be:-
Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And soon all across the sheet.
Misc. Info
Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.
Sheet 1 Columns A, B & C will typically have about 10000 rows.
There may be typically up to approximately 30 key card numbers which are not used on any specific day.
Mike

Sub setup4testing()
x = 41275
For n = 1 To 10000
If (n Mod 323) = 0 Then x = x + 1
Cells(n, 1).Value = CDate(x)
Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)
Next
End
Sub
absenteeCheck()
Dim cards(100 To 540) As Boolean
Dim working As Worksheet
curdate = Sheet1.Cells(1, 1).Value
Set working = Sheets.Add
working.Activate
'column headings, plus filler
For L1 = 1 To 5
For L2 = 0 To 40
Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2
Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0
Next L2
Next L1
For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
If Sheet1.Cells(L0, 1).Value <> curdate Then
'report absenteeism for this date
For L1 = 1 To 5
For L2 = 0 To 40
If Not cards((L1 * 100) + L2) Then
Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _
((L1 - 1) * 41) + L2 + 1).Value = curdate
End If
Next L2
Next L1
Erase cards()
curdate = Sheet1.Cells(L0, 1).Value
Else
cards(Sheet1.Cells(L0, 3).Value) = True
End If
Next L0
Cells(2, 1).EntireRow.Delete
Set working = Nothing
End Sub

 

[Auric__]

Auric, Are you there?

I'm here.

For anyone else interested, the original thread is under the subject "Excel
2007 and Security key cards" from January 8, starting with msg-id

A little while ago you wrote for me a routine which checked security key
card data for absentees. The routine is quite useable but has some minor
issues which I have been unable to fully understand, let alone resolve.
I wonder if you would be kind enough to take a look and see if you can
discover the problem. I enclose all of the relevant information at the
end of this message.
Part of the problem is that when I run the two routines, setup4testing
and absentee check, everything works perfectly except that absentee
check does not use the last date in column A sheet one of setup4
testing.
When I run the routine, absentee check, on the actual data received from
the key card software, the second row on sheet two, below the key card
numbers, has a zero in each cell, the routine does include the last date
in column A from sheet one, the results on sheet 2 are accurate but the
routine hangs up on the line:-
"cards(Sheet1.Cells(L0, 3).Value)=True"
Calling a Run-time error "9" subscript out of range
The only apparent difference between the data generated by setup4testing
and the actual key card software is that several cards on the key card
software are used every day and therefore do not have any absentees to
report on sheet two.
Here is the reference information:-

[snip]

Run the code against your actual data. When you get to the runtime error,
tell it that you want to debug. This should drop you into the VBA editor.
Press ctrl+G to open the Immediate window, then run this in there:

Print L0, Sheet1.Cells(L0, 3).Value

Copy the results back here.

 

[Michael Harrison]

Auric, Are you there?

A little while ago you wrote for me a routine which checked security keycard data for absentees. The routine is quite useable but has some minor issues which I have been unable to fully understand, let alone resolve. I wonder if you would be kind enough to take a look and see if you can discoverthe problem. I enclose all of the relevant information at the end of this message.

Part of the problem is that when I run the two routines, setup4testing and absentee check, everything works perfectly except that absentee check does not use the last date in column A sheet one of setup4 testing.

When I run the routine, absentee check, on the actual data received from the key card software, the second row on sheet two, below the key card numbers, has a zero in each cell, the routine does include the last date in column A from sheet one, the results on sheet 2 are accurate but the routine hangs up on the line:-

“cards(Sheet1.Cells(L0, 3).Value)=True”

Calling a Run-time error “9” subscript out of range

The only apparent difference between the data generated by setup4testing and the actual key card software is that several cards on the key card software are used every day and therefore do not have any absentees to report on sheet two.

Here is the reference information:-

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electroniclock to gain entrance to a building?

The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.

The key card software produces data which can be imported into Excel 2007in the following manner:-

Sheet 1

Cell A1 contains the date on which the key card was used. (DD/MM/YY format)

Cell B1 contains the time that the key card was used. (This column is notpertinent to this problem)

Cell C1 contains the identification number of the key card used to open the door.

The dates in column A start at the earliest and proceed down the sheet, in order, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used onone or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.

The key card numbers in column C are in random order.

The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)

The desired readout would be as follows:-

Sheet 2

Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.

The desired result when the formulae run would be:-

Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And so on all across the sheet.

Misc. Info

Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.

Sheet 1 Columns A, B & C will typically have about 10000 rows.

There may be typically up to approximately 30 key card numbers which are not used on any specific day.

Mike



Sub setup4testing()

x = 41275

For n = 1 To 10000

If (n Mod 323) = 0 Then x = x + 1

Cells(n, 1).Value = CDate(x)

Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)

Next

End

Sub

absenteeCheck()

Dim cards(100 To 540) As Boolean

Dim working As Worksheet

curdate = Sheet1.Cells(1, 1).Value

Set working = Sheets.Add

working.Activate

'column headings, plus filler

For L1 = 1 To 5

For L2 = 0 To 40

Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2

Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0

Next L2

Next L1

For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row

If Sheet1.Cells(L0, 1).Value <> curdate Then

'report absenteeism for this date

For L1 = 1 To 5

For L2 = 0 To 40

If Not cards((L1 * 100) + L2) Then

Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _

((L1 - 1) * 41) + L2 + 1).Value = curdate

End If

Next L2

Next L1

Erase cards()

curdate = Sheet1.Cells(L0, 1).Value

Else

cards(Sheet1.Cells(L0, 3).Value) = True

End If

Next L0

Cells(2, 1).EntireRow.Delete

Set working = Nothing

End Sub

Auric,
I followed your instructions as carefully as I could. The first time I got the result "Run-time error 1004 application-defined or object defined error", the second and third time I tried I got the number 12466. It's possible I did something different the first time but I don't think so.
Mike

 

[Auric__]

Auric, Are you there?

A little while ago you wrote for me a routine which checked security
key card data for absentees. The routine is quite useable but has some
minor issues which I have been unable to fully understand, let alone
resolve. I wonder if you would be kind enough to take a look and see if
you can discover the problem. I enclose all of the relevant information
at the end of this message.

Part of the problem is that when I run the two routines, setup4testing
and absentee check, everything works perfectly except that absentee
check does not use the last date in column A sheet one of setup4
testing.

When I run the routine, absentee check, on the actual data received
from the key card software, the second row on sheet two, below the key
card numbers, has a zero in each cell, the routine does include the
last date in column A from sheet one, the results on sheet 2 are
accurate but the routine hangs up on the line:-

“cards(Sheet1.Cells(L0, 3).Value)=True”

Calling a Run-time error “9” subscript out of range

The only apparent difference between the data generated by
setup4testing and the actual key card software is that several cards on
the key card software are used every day and therefore do not have any
absentees to report on sheet two.

Here is the reference information:-

[snip]
Auric,
I followed your instructions as carefully as I could. The first time I
got the result "Run-time error 1004 application-defined or object
defined error", the second and third time I tried I got the number
12466. It's possible I did something different the first time but I
don't think so.

Okay, one thing that will give you error 9 at the indicated line is if
there's a blank cell in column C (the card numbers). My code assumes that
your data is formatted *perfectly*, so any deviation will result in errors.

Perhaps it's just a case of checking for blanks (although I'm not too clear
about that error 1004). Try this version of absenteeCheck:

Sub absenteeCheck()
Dim cards(100 To 540) As Boolean
Dim working As Worksheet
curdate = Sheet1.Cells(1, 1).Value
Set working = Sheets.Add
working.Activate
For L1 = 1 To 5
For L2 = 0 To 40
Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2
Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0
Next L2
Next L1
For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
If (Len(Sheet1.Cells(L0, 1).Value) > 0) And _
(Len(Sheet1.Cells(L0, 3).Value) > 0) Then
If Sheet1.Cells(L0, 1).Value <> curdate Then
For L1 = 1 To 5
For L2 = 0 To 40
If Not cards((L1 * 100) + L2) Then
Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row _
+ 1, ((L1 - 1) * 41) + L2 + 1).Value = curdate
End If
Next L2
Next L1
Erase cards()
curdate = Sheet1.Cells(L0, 1).Value
Else
cards(Sheet1.Cells(L0, 3).Value) = True
End If
End If
Next L0
Cells(2, 1).EntireRow.Delete
Set working = Nothing
End Sub

Run it against the data generated by setup4testing first, and if it's still
okay -- and it should be -- try it against your data. If you still get
errors, I'll need to see your workbook to figure it out.

 

[Michael Harrison]

Auric, Are you there?

A little while ago you wrote for me a routine which checked security keycard data for absentees. The routine is quite useable but has some minor issues which I have been unable to fully understand, let alone resolve. I wonder if you would be kind enough to take a look and see if you can discoverthe problem. I enclose all of the relevant information at the end of this message.

Part of the problem is that when I run the two routines, setup4testing and absentee check, everything works perfectly except that absentee check does not use the last date in column A sheet one of setup4 testing.

When I run the routine, absentee check, on the actual data received from the key card software, the second row on sheet two, below the key card numbers, has a zero in each cell, the routine does include the last date in column A from sheet one, the results on sheet 2 are accurate but the routine hangs up on the line:-

“cards(Sheet1.Cells(L0, 3).Value)=True”

Calling a Run-time error “9” subscript out of range

The only apparent difference between the data generated by setup4testing and the actual key card software is that several cards on the key card software are used every day and therefore do not have any absentees to report on sheet two.

Here is the reference information:-

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electroniclock to gain entrance to a building?

The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.

The key card software produces data which can be imported into Excel 2007in the following manner:-

Sheet 1

Cell A1 contains the date on which the key card was used. (DD/MM/YY format)

Cell B1 contains the time that the key card was used. (This column is notpertinent to this problem)

Cell C1 contains the identification number of the key card used to open the door.

The dates in column A start at the earliest and proceed down the sheet, in order, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used onone or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.

The key card numbers in column C are in random order.

The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)

The desired readout would be as follows:-

Sheet 2

Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.

The desired result when the formulae run would be:-

Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And so on all across the sheet.

Misc. Info

Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.

Sheet 1 Columns A, B & C will typically have about 10000 rows.

There may be typically up to approximately 30 key card numbers which are not used on any specific day.

Mike



Sub setup4testing()

x = 41275

For n = 1 To 10000

If (n Mod 323) = 0 Then x = x + 1

Cells(n, 1).Value = CDate(x)

Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)

Next

End

Sub

absenteeCheck()

Dim cards(100 To 540) As Boolean

Dim working As Worksheet

curdate = Sheet1.Cells(1, 1).Value

Set working = Sheets.Add

working.Activate

'column headings, plus filler

For L1 = 1 To 5

For L2 = 0 To 40

Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2

Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0

Next L2

Next L1

For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row

If Sheet1.Cells(L0, 1).Value <> curdate Then

'report absenteeism for this date

For L1 = 1 To 5

For L2 = 0 To 40

If Not cards((L1 * 100) + L2) Then

Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _

((L1 - 1) * 41) + L2 + 1).Value = curdate

End If

Next L2

Next L1

Erase cards()

curdate = Sheet1.Cells(L0, 1).Value

Else

cards(Sheet1.Cells(L0, 3).Value) = True

End If

Next L0

Cells(2, 1).EntireRow.Delete

Set working = Nothing

End Sub

Auric,
Thank you for your reply. I rechecked my data and there are no blanks in sheet 1 column C. I ran the revised “absentee check” against the data from “setup4testing” and the keycard software data. For the keycard software data there were no hang-ups and there was no row of zeros in sheet 2, however both of the tests failed to return any absentee reports for the last date in sheet 1 column A. This is the same problem that I had previouslywith the “setup4testing data”. It seems that somehow the loop is terminating before it reaches the last date.
Mike

 

[Auric__]

Thank you for your reply. I rechecked my data and there are no blanks in
sheet 1 column C. I ran the revised “absentee check” against the data
from “setup4testing” and the keycard software data. For the keycard
software data there were no hang-ups and there was no row of zeros in
sheet 2, however both of the tests failed to return any absentee
reports for the last date in sheet 1 column A. This is the same problem
that I had previously with the “setup4testing data”. It seems that
somehow the loop is terminating before it reaches the last date.

Sigh. Dumb mistake on my part. Try this version:

Sub absenteeCheck()
Dim cards(100 To 540) As Boolean
Dim working As Worksheet
curdate = Sheet1.Cells(1, 1).Value
Set working = Sheets.Add
working.Activate
For L1 = 1 To 5
For L2 = 0 To 40
Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2
Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0
Next L2
Next L1
For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
If Sheet1.Cells(L0, 1).Value <> curdate Then
For L1 = 1 To 5
For L2 = 0 To 40
If Not cards((L1 * 100) + L2) Then
Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _
((L1 - 1) * 41) + L2 + 1).Value = curdate
End If
Next L2
Next L1
Erase cards()
curdate = Sheet1.Cells(L0, 1).Value
Else
If (Len(Sheet1.Cells(L0, 3).Value) > 0) Then _
cards(Sheet1.Cells(L0, 3).Value) = True
End If
Next L0
Cells(2, 1).EntireRow.Delete
Set working = Nothing
End Sub

 

[Michael Harrison]

Auric, Are you there?

A little while ago you wrote for me a routine which checked security keycard data for absentees. The routine is quite useable but has some minor issues which I have been unable to fully understand, let alone resolve. I wonder if you would be kind enough to take a look and see if you can discoverthe problem. I enclose all of the relevant information at the end of this message.

Part of the problem is that when I run the two routines, setup4testing and absentee check, everything works perfectly except that absentee check does not use the last date in column A sheet one of setup4 testing.

When I run the routine, absentee check, on the actual data received from the key card software, the second row on sheet two, below the key card numbers, has a zero in each cell, the routine does include the last date in column A from sheet one, the results on sheet 2 are accurate but the routine hangs up on the line:-

“cards(Sheet1.Cells(L0, 3).Value)=True”

Calling a Run-time error “9” subscript out of range

The only apparent difference between the data generated by setup4testing and the actual key card software is that several cards on the key card software are used every day and therefore do not have any absentees to report on sheet two.

Here is the reference information:-

Can anyone please help me with an interesting problem which concerns Excel 2007 and the security type key cards which are used to open an electroniclock to gain entrance to a building?

The key cards are numbered 100 thro’ 140, 200 thro’ 240, 300 thro’ 340, 400 thro’ 440 and 500 thro’ 540.

The key card software produces data which can be imported into Excel 2007in the following manner:-

Sheet 1

Cell A1 contains the date on which the key card was used. (DD/MM/YY format)

Cell B1 contains the time that the key card was used. (This column is notpertinent to this problem)

Cell C1 contains the identification number of the key card used to open the door.

The dates in column A start at the earliest and proceed down the sheet, in order, to the latest date. There will be approximately 31 dates, It is possible though extremely unlikely that all the key cards may not be used onone or more days. Most key cards will be used one or more times each day. Some will not be used at all on some days.

The key card numbers in column C are in random order.

The result that is required is to find out which key cards were NOT used and on which days they were not used. (Sort of like an absentee check)

The desired readout would be as follows:-

Sheet 2

Cell A1 contains key card number 100, Cell B1 contains key card number 101 and so on across the sheet listing all of the key card numbers.

The desired result when the formulae run would be:-

Starting at A2 and working down the column, in order, would be all the dates that the key card 100 was NOT used. Similarly for column starting at B2, in order, would be all the dates that the key card 101 was NOT used. And so on all across the sheet.

Misc. Info

Sheet 1 Column A will typically have about 31 dates, each one occurring up to approximately 300 times.

Sheet 1 Columns A, B & C will typically have about 10000 rows.

There may be typically up to approximately 30 key card numbers which are not used on any specific day.

Mike



Sub setup4testing()

x = 41275

For n = 1 To 10000

If (n Mod 323) = 0 Then x = x + 1

Cells(n, 1).Value = CDate(x)

Cells(n, 3).Value = Int(Rnd * 41) + ((Int(Rnd * 5) + 1) * 100)

Next

End

Sub

absenteeCheck()

Dim cards(100 To 540) As Boolean

Dim working As Worksheet

curdate = Sheet1.Cells(1, 1).Value

Set working = Sheets.Add

working.Activate

'column headings, plus filler

For L1 = 1 To 5

For L2 = 0 To 40

Cells(1, ((L1 - 1) * 41) + L2 + 1).Value = (L1 * 100) + L2

Cells(2, ((L1 - 1) * 41) + L2 + 1).Value = 0

Next L2

Next L1

For L0 = 1 To Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row

If Sheet1.Cells(L0, 1).Value <> curdate Then

'report absenteeism for this date

For L1 = 1 To 5

For L2 = 0 To 40

If Not cards((L1 * 100) + L2) Then

Cells(Cells(1, ((L1 - 1) * 41) + L2 + 1).End(xlDown).Row + 1, _

((L1 - 1) * 41) + L2 + 1).Value = curdate

End If

Next L2

Next L1

Erase cards()

curdate = Sheet1.Cells(L0, 1).Value

Else

cards(Sheet1.Cells(L0, 3).Value) = True

End If

Next L0

Cells(2, 1).EntireRow.Delete

Set working = Nothing

End Sub

Auric,
Thank you for your reply and thank you very much for your patience and helpwith this problem. I took the liberty of adding “End If” near the end, and the routine works well. During my struggle to understand how all of this works, I bought Walkenbach’s books “Excel 2007 Bible” and “Excel 2007 VBA for Dummies” (I am humble, some say for good reason). I didn’t find them to be as much help as I had hoped. What I really wanted was a book which was like a dictionary of all of the allowable words in VBA with their meaning, and also listed the typical phrases used. In addition I would have liked it to have had a selection of example programs which were analyzed word by word preferably explaining why that syntax was chosen in preference to some other syntax which would also have worked in that location.Do you know of any books like that, or are there any sources on the web which use something like that format? Anyway, thank you again for your help.
Mike

 

[Auric__]

Thank you for your reply and thank you very much for your patience and
help with this problem. I took the liberty of adding "End If" near the
end,

....I don't see where I missed one. Shrug.

and the routine works well. During my struggle to understand how
all of this works, I bought Walkenbach's books "Excel 2007 Bible" and
"Excel 2007 VBA for Dummies" (I am humble, some say for good reason). I
didn't find them to be as much help as I had hoped. What I really wanted
was a book which was like a dictionary of all of the allowable words in
VBA with their meaning, and also listed the typical phrases used. In
addition I would have liked it to have had a selection of example
programs which were analyzed word by word preferably explaining why that
syntax was chosen in preference to some other syntax which would also
have worked in that location. Do you know of any books like that, or are
there any sources on the web which use something like that format?

The help file comes to mind.

I suppose one of O'Reilly's nutshell books would be good, or their pocket
references, if they have one for VBA (or VB6 or older, but *not* VB.Net, or
any VB version that includes a year in the name, e.g. VB 2010). O'Reilly's
books are generally about the best there is, and their pocket references seem
to be decent enough, but I've only used one; YMMV. (The "for Dummies" series
isn't as helpful as some might think; the ones I've read seem to focus more
on specific tasks, rather than getting an overall understanding.)

Since I already knew VB when I started working in VBA, I had something of a
head start, and just had to learn the Office-specific things. (I also had
about 15 years of BASIC programming under my belt before I started on VB.) I
*did* buy a Microsoft Press book on programming Office VBA, and while this
was in the late 90's (for Office 97), it still would be of some use if I
could find the damn thing.

Anyway, thank you again for your help.

Np.