Have you guys checked out Excel User®'s book on dashboards?

I just recently stumbled upon Charley Kyd's definitive book - Dashboard Reporting with Excel - on creating and managing Excel-based management "dashboards," which are streamlined, multi-metric management reports that visually represent key data and trends in one easy-to-grasp document.

In a past life I worked for a company that was very into management dashboards, and this book would have certainly given me an edge. The book is available for less than $30, but if you're serious about starting a management dashboard system or enhancing the one you've got, spring for the whole kit, which is still less than $50.

I say, get a copy, read it.

Out.

Another project...separate names from a huge list

Yesterday I received another Excel project: a 19,000+ list of people's names, all in the same field -- i.e. First Middle Last Suffix. The kicker was that not all people had middle initials. Some had middle initials, while others had full middle names. and Some were Jr.'s, II's, etc. Then, of course, there's dealing with "Von Lastnames" and "van Lastnames" and "Del Lastnames" and "De Lastnames" and "De La Lastnames." It's crazy.

My task was to separate them all into First Name, Middle Name/Init., Last Name, Suffix fields.

I did it without using any VBA, though in my spare time I'm going to try to figure out how to use a Bayesian Algorithm and some code to accomplish the same task.

Thanks, Craig!

Thought I'd add an image to my profile

I thought I'd add an image to my Blogger profile, so here it is...

Geez..that was fast...my first $50 project

Boy, maybe I should rethink this $50 thing...

Within hours of making this change, I've already received my first request, completed the project, and have sent the code to the requestor.

Jim, I hope it does exactly what you asked for. I pretty sure it does.

What Jim needed was a macro in an Excel file that would check each cell in a selected range, and color-code the text in the cell based upon these criteria:

• If the cell contained a numeric value that's not a formula, it's red.


• If the cell contained a numeric value that is a formula, but only because it's preceded with an equal sign, it's green.


• If the cell contained a numeric value that is a formula with operators other than merely having an equal sign, it's blue.

Using a regular expression, the isNumeric function, some nested If_Then_Else's, and a few other things, I think I nailed it down pretty good.

Thanks for the project, Jim, and for $50.

Made a few changes to the blog yesterday...

and will continue today.

1) I've changed the heading. $50? Why not? Not sure when I'll have time to do the projects (assuming people take me up on it), but it'll be fun.

2) I'm adding in some links to other Excel or VBA blogs that I've found and like. Many of these I've actually gotten ideas and fixes for bugs from.

Later,

E_G

Fixed an annoying little problem with my email parser

I was having trouble with error handling on my email parsing macro. Whenever the script tried to return a bad URL or one that could not be returned as text, I was using "On Error Goto..." and directing to an error handling branch that ran (marking the URL as "BAD" by coloring it RED and then directed back to the next URL.

This would run just fine once (it could handle one bad URL), but when it encountered another bad URL, it would puke.

I have since changed to using "On Error Resume Next" and following the two lines of code prone to erroring out, I placed this bit of code:

If Err Then
GoTo BAD_URL ' Error Handling Branch
End If

Then, at the end of the error handling branch, just before directing back to the next URL, I cleared the Err object.

Err.Clear

Now it can handle errors an unlimited number of times. I'm so happy.

Later.

Here's the Google page parser

Here's v1.0 of the code...

Sub Parse_Google_Search_Results_Pages_For_URLs()

'**********************************************************
'****************** Code by Excel_Geek ********************
'**********************************************************

' This Excel macro calls up search results pages of a Google search
' using a search term specified in a cell named "SEARCH_TERM". Then it uses a
' regular expression to glean from this page all of the webpage addresses (URLs)
' of the search results and places them in a list in the spreadsheet.

' Setting up the regular expression that recognizes search result URLS on Google pages --
' The regular expression also ingores case, which by the way, i convert all to lower in the end.
Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = """http://([^6""]+)"""

' Now I reformat the search term as typed into the search term as used in Google search query URLs.
' i.e. I replace spaces with pluses and quote marks with %22s.
Range("SEARCH_TERM").Select
Selection.Replace What:="""", Replacement:="%22", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" ", Replacement:="+", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Set the top limit on the Google page of results to parse.
PAGES = Range("GOOGLE_PAGE").Value

' Need this, apparently, to generate random numbers...
' which we'll need at the bottom of this next FOR_NEXT...
Math.Randomize

' Create a FOR_NEXT loop to get all pages.
For x = 1 To PAGES
' Set the GOOGLE_PAGE value to the page to parse.
Range("GOOGLE_PAGE").Select
ActiveCell.FormulaR1C1 = x

' Selected the SEARCH_URL, as this will need to be passed to the objHTTP.Open command.
Range("SEARCH_URL").Select

' Get the page using the objHTTP object.
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", Selection.Value, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

' Define the pageToParse as teh response text of the "GET" method.
pageToParse = objHTTP.responseText
' Clear the objHTTP object.
Set objHTTP = Nothing
' Set expressionmatch as the strings matched using the regular expression defined above.
Set expressionmatch = regEx.Execute(pageToParse)

For Each expressionmatched In expressionmatch

' Got to get rid of any results on Google's servers.
If InStr(1, expressionmatched, "google", 1) > 0 Then

GoTo MOVE_ON

Else

Range("FIRST_URL_HERE").Select
ActiveCell.FormulaR1C1 = LCase(Left(Mid((expressionmatched), 2, 300), Len(expressionmatched) - 2))
Selection.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="FIRST_URL_HERE", RefersToR1C1:=Selection

End If
MOVE_ON:

Next

' Setting up code to pause by a random amount of time between 4 and 15 seconds between pages of results.
Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + Int((15 - 4 + 1) * Math.Rnd + 4)
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Next x

End Sub

New stuff coming

I've been working on some code that will help me generate a list of URLS from which to parse email addresses. Using Google as a search engine, i'm able to generate a targeted list of URLs using keywords relevant to my audience.

I'll show you once i've got a decent V1.0 ready...

Tiny little mod to email system

I added a tiny piece of code to the email system so that it checks to see if each email is marked as "BAD" or "REMOVED" before sending it.

More small updates to follow.