| I’ve had the honor of being named a Cell Master by Colo. Actually, I was nominated by Rob Bovey, so thanks Rob. You can read my Cell Master answers on Colo’s page. If you haven’t been there before, read some of the past Cell Masters’ answers. They’re pretty entertaining. |  |
Author: Dick Kusleika
INDIRECT and Dynamic Ranges
This isn’t a new issue, but it’s new to me. Charlie, a loyal reader, was trying to use INDIRECT with a dynamic range name and kept getting errors.
=MAX(INDIRECT(“List2”))
returns the #REF! error. It appears to be a limitation of INDIRECT (yes, another one).
One way to get around the problem is to just reproduce the dynamic range name formula in the cell.
=MAX(OFFSET(Sheet1!$B$6,0,0,COUNTA(Sheet1!$B:$B)-1))
That works, but now when you change List2, you have to remember to change this formula too, not to mention all the other formulas that use this.
Option #2 is a UDF.
=MAX(DINDIRECT(“List2”))
Public Function DINDIRECT(sName As String) As Range
‘It stands for Dynamic Indirect
Dim nName As Name
‘Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0
‘Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DINDIRECT = nName.RefersToRange
Else
DINDIRECT = CVErr(xlErrName)
End If
End Function
‘It stands for Dynamic Indirect
Dim nName As Name
‘Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0
‘Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DINDIRECT = nName.RefersToRange
Else
DINDIRECT = CVErr(xlErrName)
End If
End Function
Gee, as simple as that function is you’d think Microsoft would have put in the program.
Does anyone have an option #3?
WordPress Upgrade
You may notice some differences today. I upgraded to version1.5 and I’m sure something is broken. Comments, for one, look a little different to me. Anywho, if you note something missing or changed and you liked it the old way, drop me a line and I’ll see if I can fix it up.
Carl sent me an email a while back about adding the author to the RSS feed. It’s a great idea and now that I’m upgraded I plan to do it (as well as fix up the comments RSS as Jon requested). Secretly, I’ve been hoping the upgrade will take care of that stuff, but that’s probably a pipe dream.
Oh, what the heck, I’ll just start a To Do list:
- Fix the footer as Root suggested
- Add author, category to comments RSS
- Add author to rss2 (it’s already in atom)
- Add a random post link to the menu (gee, you think there’s enough crap on that menu yet?)
- Take categories off the menu and make a separate page
- Add a page of books
- Fix up the author profile page to look more like the rest of the site
Leave your addition to this list as a comment. Leave any other comments you have about the site. Thanks.
Browser Links
Jendra found an interesting bug while automating Internet Explorer. The Document object has a dozen or so collections that allow you to access various components of the html document. For instance, you can use the Links collection to iterate through all the hyperlinks (anchor tags with an href attribute) or you can use the All collection to iterate HTMLElements of any type.
Jendra has an image tag with a name attribute of “links”. It seems when you have an element with a name attribute equal to the name of a collection. I’ve tested it with Links and All and I assume it screws up on any collection name.
I set up a couple of html docs; TestLinks.htm and TestLinks2.htm. In the TestLinks2, I changed the name attribute to “links1? and the code did not fail. Below is the code I used to test it.
I don’t see a decent workaround to this bug. Jendra decided to test the Typename of the objects in the loop and exit gracefully if it was different than expected. Of course that means the code won’t actually work, but at least it won’t throw an ugly run-time error.
Here’s the test code. When the Navigate line is changed to TestLinks2, no error. As it is, I get error 438 “Object doesn’t support this property or method”. In debug mode from the immediate window you can see why
?Typename(ieDoc.Links)
HTMLImg
Of course an HTMLImg object doesn’t have a Length property so the For i = 1 to… line fails.
Sub TestLinksColl()
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim i As Long
Set ieApp = New InternetExplorer
ieApp.Navigate “/excel/TestLinks.html”
Do
DoEvents
Loop Until ieApp.ReadyState = READYSTATE_COMPLETE
Set ieDoc = ieApp.Document
Debug.Print “******Links collections”
For i = 1 To ieDoc.links.Length
Debug.Print i, Mid(ieDoc.links(i – 1).innertext, 1, 20), TypeName(ieDoc.links(i – 1))
Next i
Debug.Print
Debug.Print “******All collection”
For i = 1 To ieDoc.all.Length
Debug.Print i, Mid(ieDoc.all(i – 1).innertext, 1, 20), TypeName(ieDoc.all(i – 1))
Next i
End Sub
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim i As Long
Set ieApp = New InternetExplorer
ieApp.Navigate “/excel/TestLinks.html”
Do
DoEvents
Loop Until ieApp.ReadyState = READYSTATE_COMPLETE
Set ieDoc = ieApp.Document
Debug.Print “******Links collections”
For i = 1 To ieDoc.links.Length
Debug.Print i, Mid(ieDoc.links(i – 1).innertext, 1, 20), TypeName(ieDoc.links(i – 1))
Next i
Debug.Print
Debug.Print “******All collection”
For i = 1 To ieDoc.all.Length
Debug.Print i, Mid(ieDoc.all(i – 1).innertext, 1, 20), TypeName(ieDoc.all(i – 1))
Next i
End Sub
Hardcore VB
Sorry I don’t have anything meaningful to post today. I’m just linking to this free book about Visual Basic 5 so I can read it when I get time. Okay, you can read it too.
http://vb.mvps.org/hardcore/
For those of you have sent me suggestions, thanks. I’m working through them and will post them when I get time. My lack of meaningful content isn’t because it’s not there, it’s because I don’t have time to write it.
HTML in Cells II
While I was working on this newsgroup post to copy formatted text from Word to Excel while preserving formatting and maintaining line breaks, I stumbled on a way to enter HTML in cells.
becomes
with this macro
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Private Sub Worksheet_Change(ByVal Target As Range) Dim objData As DataObject Dim sHTML As String Dim sSelAdd As String Application.EnableEvents = False If Target.Cells.Count = 1 Then If LCase(Left(Target.Text, 6)) = "<html>" Then Set objData = New DataObject sHTML = Target.Text sHTML = Replace(sHTML, "<html>", "<html><style>br{mso-data-placement:same-cell;}</style>") objData.SetText sHTML objData.PutInClipboard sSelAdd = Selection.Address Target.Select Me.PasteSpecial "Unicode Text" Me.Range(sSelAdd).Select End If End If Application.EnableEvents = True End Sub |
I haven’t quite got the line break part worked out here. You may notice that I try to put a style tag in the string before I put it in the clipboard. This doesn’t seem to have any effect. The style tag worked with the aforementioned newsgroup post, but didn’t work in this situation. In this case, text after a br tag is moved to the cell below, when I would prefer that it act like an Alt+Enter and create a new line in the same cell. I’m not quite sure why there’s a difference.
I’ve used the PasteSpecial method of the Worksheet object instead of the Range object. It gives me the “format” argument that I need to specify Unicode Text where the Range’s method doesn’t have that argument. That means I had to account for any “move selection after entry” situation. I couldn’t think of a better way to do that, but I’m sure there was.
You may also notice that I, in a most cowardly fashion, limited the event to only work when one cell was being changed. I like to allow Change events to work on multiple cells and I had originally set this up that way. Then I deleted about 20 columns, which means the Change event was checking 1.2 million cells. Ugh. I may change it to loop though the cells using the Find method instead of checking with the Left function. That would solve the problem I had deleting a bunch of columns, but who knows what other problems it would create.
Now I need to figure out a way to do the reverse. I suppose a lengthy SaveAs HTML will be in order for that.
Limit a Listbox
Data in Listboxes can get unruly causing the users to do a lot of scrolling. One way to rein them in is to allow the user to filter the Listbox by typing in a Textbox. It’s kind of like autocomplete. In this example, a Listbox is populated with all the customer names from the Northwind database’s Customers table. The user types in the Textbox and the Listbox is automatically filtered.
 |
 |
The Change event of the Textbox is used to filter the Listbox.
Private Sub tbxFind_Change()
Dim i As Long
Dim sCrit As String
‘Add asterisks around text for all matches
‘UCase is used to make filter case-insensitive
sCrit = “*” & UCase(Me.tbxFind.Text) & “*”
With Me.lbxCustomers
‘Start with a fresh list
.List = vaCustNames
‘Loop through the list backward – always a good
‘idea when you’re deleting stuff
For i = .ListCount – 1 To 0 Step -1
‘Remove the line if it doesn’t match
‘UCase used again here
If Not UCase(.List(i)) Like sCrit Then
.RemoveItem i
End If
Next i
End With
End Sub
Dim i As Long
Dim sCrit As String
‘Add asterisks around text for all matches
‘UCase is used to make filter case-insensitive
sCrit = “*” & UCase(Me.tbxFind.Text) & “*”
With Me.lbxCustomers
‘Start with a fresh list
.List = vaCustNames
‘Loop through the list backward – always a good
‘idea when you’re deleting stuff
For i = .ListCount – 1 To 0 Step -1
‘Remove the line if it doesn’t match
‘UCase used again here
If Not UCase(.List(i)) Like sCrit Then
.RemoveItem i
End If
Next i
End With
End Sub
I put the customer names in a variant array in the userform’s Initialize event. The array variable is a module level scope so I can use it anywhere in the userform’s module. It’s a little brute force to refresh the list with every change, but it wasn’t very slow for me and the 91 names in the list. If you had a much bigger list, you might track the length of the Textbox and only start fresh when the length goes down.
With Me.lbxCustomers
If Len(Me.tbxFind.Text) < lOldLen Then
‘Start with a fresh list
.List = vaCustNames
End If
lOldLen = Len(Me.tbxFind.Text)
If Len(Me.tbxFind.Text) < lOldLen Then
‘Start with a fresh list
.List = vaCustNames
End If
lOldLen = Len(Me.tbxFind.Text)
lOldLen is another module level variable that stores the previous length of the text in the Textbox. That may speed things up a bit with a large list.
Workday Function
NETWORKDAYS is a function in the Analysis Toolpak that returns the number of working days between two days. If you need to find a day that is a specified number of working days away, then use the WORKDAY function (or its alternative).
This example shows the syntax for WORKDAY, but doesn’t use the optional holidays argument. The other two formulas are B3: a straight subtraction of two days and D3: Frank Kabel’s alternative array formula.
To find a day that’s a number of days away from a start date, but returning Friday if the result is Saturday and returning Monday if the result is Sunday, you can use a formula like this:
=A3-2+CHOOSE(WEEKDAY(A3-2),1,0,0,0,0,0,-1)
This computes the day of the week number and uses the CHOOSE function to add a day if WEEKDAY returns 1 (Sunday to Monday) and subtract a day if WEEKDAY returns 7 (Saturday to Friday).