Update

I realized that a few people have actually been visiting this site so I wanted to provide a brief update. My last publish was back in 2011 and soon there after I was hired on at Microsoft full time. What I thought was going to be a great experience and adventure turned into one of the the most difficult and trying experiences of my life. I may create a new post covering the topic, but for now I will say that I escaped in December to a small consulting company in Redmond called Brightlight Consulting.

Part of this move was to become a more well rounded BI developer and learn Linux and Netezza. I have been working in the Microsoft BI stack for 6 years and in Excel for 10 but after my Microsoft experience I just didn't like them anymore and had no desire to work with Microsoft products again. Hopefully with time I'll get the interest back, but right now I'm excited to be learning something new.

I will be continuing to update this site here and there as I develop Excel reports and tools in my new position. Thanks to those that are viewing this and hopefully you have found something useful.

More Excel Notes

I realized that I haven't posed since November. I'm not doing as well as I had planned. I'm also in the final stages of releasing a global reporting tool for my current client so I've been spending a lot of time getting things polished and working with the test team. However, I do have some new things to add. Simple stuff, but really helpful.

The tool I have been working on for the past two months uses a text box for a report name, that name is dropped into the Excel file as the tab name as well so I've had to do some checking of the name to make it tab compatible. Here is what to look for.

Excel only allows 31 characters for a tab name. This can easily be limited in the "MaxLenght" property of the text box. However, based on how you are using this users may need some additional information. In the tool I built I added a simple counter at the end of the text box that shows the user the current number of characters against the max allowed i.e. "0/31". Then used the text boxes "Change" event to update the first number based on the user input with this code:

charAvailable.Caption = CStr(Len(reportName.Text)) & "/31"

Another thing to check is any invalid tab characters in the text box. Excel does not allow :, \, /, ?, *, [, or ] to be in a tab name. There may be an easier way to do this but I simply use the Replace function to replace an invalid character with whatever other acceptable character I want (in most cases either an empty string "" or a space " "). Here is the code:

RptName = txtRptName.Text

RptName = Replace(RptName, ":", " ")

{same code as above for remaining invalid characters}

txtRptName.Text = RptName

Again, there may be a simpler way, but this way works and it's relatively fast since there is a max of 31 characters for the string.

Two more checks that I run are checking for blanks and checking for completely numeric names. Both of these are done via if statements. Here is the code:

If txtRptName = "" Then

End If

If isNumeric(txtRptName) Then

End If

I built a function called UserForm_Validation that returns True or False where all of this code runs, each section will exit the function with a setting of False when a validation error is found. The call on the main source line will exit the code completely if a value of "False" is returned.