Absenteeism

Well I know there aren't many people who have even looked at my blog, but for those of you whom have sorry I dropped off the radar for 2 1/2 years. Wow I can't believe it's been that long, and for that matter that blogger never deleted my account! :) On that note, I have decided to delete this account soon, but no worries I have started a new blog that will be hosted on my very own spank'in new website http://www.derekpinkerton.com. (Insert lack of creativity joke here.) I'm hoping to start posting a bit more often, than once every 2 1/2 years. A modest goal I know but hopefully one that I can acheive. :) I have copied all posts up to but excluding this one to my new site already.

For my new website I am planning on more than just a blog, although I'm not quite sure exactly what that means yet. For starters the new site is running an ASP.Net blog engine called BlogEngine.Net (available here) I have created a customized theme for the site, but haven't had much time to do anything else just yet.

So again I bid you adieu from this blogger site and hope to see you on the other side.

Excel: my new favorite program :)

I have an excel spreadsheet with a custom vba function that will simply return the document property with the name given in the parameter. This function works wonderfully. I can enter it as the formula for a particular cell in the worksheet (such as =DocProperty("MyVariable")) and that cell will now display the value of that document property.

Although this does work as expected the first time the cell's value is calculated it does not automatically update if the document property changes. Excel seems to cache the value of each cell. This makes sense for performance issues however I am using DSOFile.dll to update these document properties from another application (namely Qualtrax.) With this setup the values are changed when the document is closed and I would like the new values of these variables to be displayed each time the document is opened.

I have seen similar behavior with "Fields" in Word documents where the displayed value does not correspond to the actual value stored. The solution in Word documents was to add a VBA macro that runs automatically when the document is opened, so I figured I would create a similar macro that would update my Excel documents. I figured something like the following would work:

Public Sub Auto_Open()
    Dim ws As Worksheet
   
    For Each ws In ThisWorkbook.Worksheets
        ws.Calculate
    Next ws
End Sub

However I wanted to be sure that in the event of a worksheet with a large number of calculated cells I only update the ones that need to be udpated. So I came up with the following code:

Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Calculate
            End If
        Next cell
    Next ws
End Sub

Much to my surprise this function did not work. I stepped thorough and discovered that the cell.Calculate call was being made for each cell whose formula was a call to my DocProperty function (shown below), however for some reason Excel is not actually recalculating the value! After a couple hours of torment I found that resetting the formula on the cell will force a recalculate so simply changing cell.Calculate to cell.Formula = cell.Formula fixes the problem and actually makes Excel recalculate the value of that cell. WHY OH WHY does the Range.Caclulate function not actually force a recalculate of a cell whose formula calls a vba function?!?!?

Complete working code.

Public Function DocProperty(property As String) As String
    Dim WB As Workbook
    On Error Resume Next
    If TypeOf Application.Caller Is Range Then
        Set WB = Application.Caller.Parent.Parent
    Else
        Set WB = ActiveWorkbook
    End If
    DocProperty = WB.CustomDocumentProperties(property)
    WB.Saved = True
End Function

Public Sub Auto_Open()
' - This macro will refresh any cells where the formula
' - is a call to the DocProperty function
'
    Dim ws As Worksheet
    Dim cell As Range
   
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange.Cells
            If InStr(cell.Formula, "DocProperty") > 0 Then
                cell.Formula = cell.Formula
            End If
        Next cell
    Next ws
End Sub

The Wild

Disney recently released it's latest animated film "The Wild" on video and DVD. My wife and I decided to rent it last night and watch it with our one and a half year old daughter. Did Disney fire all of their talented writers? This was the worst Disney movie I have ever seen. It was so incredibly boring that I kept thinking to myself "It has to get better." My wife was ready to just turn it off less than half way through. The plot to this awful movie is somewhat similar to the recent movie Madagascar, animals escape from the New York city zoo and go to the wild. The major difference between the two is that Madagascar was actually entertaining. Take it from me, don't waste your time or money on watching this pile of crap.

Pay it Forward

Over the weekend my wife, daughter and I went to the annual CCS company picnic. On our way home we decided to stop by Brusters for some ice cream. We had never been to a Brusters before (and only knew about it by having driven by the week before) so we had no idea what to expect. I ordered a single scoop of Chocolate Raspberry Truffle and my wife a Peanut Butter Cup Sunday. It was only after the ice cream had been scooped that I realized that there weren't any Visa or Mastercard logos to be seen. Me being someone that does not often carry cash and relies heavily on my debit card I had not even thought to ask if they accepted debit or credit cards. Well just as we were about to have to leave to get some cash one of the other patrons, a nice couple that looked to be in their 30's, offered to pay for us. I was completely amazed, and with a bit of reluctance accepted. They only asked that we "Pay it Forward" when we come across someone else in need. If you are reading this, thanks again, your generosity will not be soon forgotten!

Oh and in case anyone was wondering the ice cream was delicious! If you are ever in the Southwest Roanoke area I highly recommend stopping by Brusters for a waffle cone!

Fundraisers for Ian Herbst

The following restaurants have agreed to donate a portion of their sales for a certain night to Ian's campaign--so if you are in the area, let's get the crowds out! Hey--people have to eat anyway, right, so they may as well eat and contribute? Mark your calendars and foward to all your friends!

Sept 11th Arby's in Radford Charity night

Sept 21st Chic-fil-a in Christiansburg Charity night

Oct 24th Texas Roadhouse charity night

If you would like more information about Ian Herbst please check out www.ianfund.com.

Formatting QualTrax dates in Word headers and footers

When QualTrax inserts dates fields into Word documents the entire date and time is displayed, such as "Monday, July 17, 2006 2:59:44 PM". In some cases you may want to show the short date format instead, such as "7/17/06". Here are some simple instructions on how to format your QualTrax dates in Word documents.

1) First go to Insert->Field like you normally would to add a QualTrax field to the document.
2) Select DocProperty and then select the field name that you would like to add such as ##DATE_PUBLISHED##.
3) Now before clicking OK to close this window, click the "Field Codes" button in the lower left corner of the window.
4) You should now see a box titled "Field codes:" that should look like "DOCPROPERTY ##DATE_PUBLISHED##" we will need to add the formatting code to the end of this field. First add " \@ " (without the quotes) which tells word that the rest of the text we add is formatting text. If your formatting text will include any spaces you will need to enclose it with double quotes.
5) Next add one of the following formatting codes to get the desired date format.

MM/DD/YYYY => 02/06/1981
M/D/YYYY => 2/6/1981
"DDD M/D/YYYY"  => Fri 2/6/1981
"DDDD MMMM D, YYYY" => Friday February 6, 1981

Here's some more information on each of the formatting characters and what they mean.

Year
Displays a year as two or four digits, depending on how many letters 'y’ you use. The letter ‘y’ can be either uppercase or lowercase.

Parameter	Displays	Example
y or yy	The year in two digit form, with a leading 0 for years 00 to 09	06 or 06
yyyy	the year in four digit form	2006

Month
Displays a month as one or two digits, as a three-letter abbreviation or in full, depending on how many uppercase letters ‘M’ you use (lowercase letters ‘m’ represent minutes).

Parameter	Displays	Example
M	The number of the month as a number without a leading 0 for single-digit months	7
MM	the month as a two-digit number with a leading 0 for single-digit months	07
MMM	the month as a three-letter abbreviation	Jul
MMMM	the full name of the month	July

Day
Displays a day of the month or the day of the week, depending on how many letters ‘d’ you use. The letter ‘d’ can be either uppercase or lowercase.

Parameter	Displays	Example
d	The date of the month as a number without a leading 0 for single-digit days	27
dd	the date of the month as a number with a leading 0 for single-digit days	27
ddd	the day of the week as a three-letter abbreviation	Thu
dddd	the full name of the day of the week	Thursday

Hour
Displays an hour of the day as one or two digits based on either a 12-hour clock or a 24-hour (military) clock, depending on whether you use an uppercase or lowercase ‘h’. A lowercase ‘h’ bases time on the 12-hour clock. An uppercase ‘H’ bases time on the 24-hour (military) clock. You can also include ‘AM/PM’ in uppercase or lowercase.

Parameter	Displays	Example
h or H	The hour as a number without a leading 0 for single-digit hours	>8 or 8
hh or HH	the hour as a number with a leading 0 for single-digit hours	08 or 08
am/pm	‘AM’ or ‘PM’	AM

Note: You can change the AM and PM symbols that Word uses by changing the Time settings in the Regional Settings dialog box in the Windows Control Panel. For example, you could use this to force the ‘am/pm’ parameter to display as ‘a.m./p.m.’.

Minute
Displays the minute as one or two digits, depending on how many letters ‘m’ you use (uppercase letters ‘M’ represent months).

Parameter	Displays	Example
m	The minute as a number without a leading 0 for single-digit minutes	43
mm	the minute as a number with a leading 0 for single-digit minutes	43

Second
Displays the second as one or two digits, depending on how many letters ‘s’ you use. The letter ‘s’ can be either uppercase or lowercase.

Parameter	Displays	Example
s	The second as a number without a leading 0 for single-digit seconds	10
ss	the second as a number with a leading 0 for single-digit seconds	10

QualTrax Training

Yesterday we had a customer coming out for a one day training session on QualTrax, this is generally a two day course and is usually given by our Training Coordinator Patsy Clark. Patsy is currently out on vacation so it was up to me to train this person. Although I know the product inside and out I have never given a training session before. Well on my way in to work I got caught up in a mess of traffic on the 460 (Orange Ave.) in Roanoke that was all caused by a small mudslide. VDOT closed one lane which forced traffic to back up for miles. I ended up showing up 45 minutes later than I had planned on, which was only a few minutes later than the customers arrival. Luckily Vicky came to the rescue and started the training session on time in my absence. Thanks Vicky! :) To make matters worse as soon as Vicky left the room and I took over the training, the wireless router we use to connect each of the training computers suddenly stopped working and I had to get our IT Guru Kevin to come fix it. It turned out the power cord broke, but we did have a spare that we were able to use instead. After all this we finally got back into the training and the session seemed to go very well.

Gotta Love Word!

Last week I had a customer that wanted to add a calculated field "Effective Date" to his Word documents that would automatically be updated with each revision of the document. This field would be calculated as the published date of the document plus 2 weeks. QualTrax itself will only enter the actual date of an event (such as published) so in order to get a date calculated based upon the published date I had to turn to Word. In researching this I found that there were two ways to do to this. The first method is pretty straight-forward; you simply write a vb macro to get the date published (inserted automatically by QualTrax as a Document Property) add two weeks and store this date into a new Document Property, which can then be accessed in fields in the same way as all other QualTrax variables.


Method 1 (Macro): With this method you would add a macro to each document that would calculate the "Effective Date" based upon the Published Date and store it into a new document variable called ##EFFECTIVE_DATE##. This macro would be setup to run automatically each time the document is opened so that you are always viewing up to date information. the problem with this is that if the person to open this document has their Word security setting set to anything higher than low, they will get a warning message asking them to enable/disable the macros. If they decide to disable the macros within the document, the information will not get updated and they may be looking at an old copy of the data. If you have the document converted to PDF using the PDF Module you will not have this problem.

The steps to implement method 1 are as follows:
1) Open the document in question and go do "File->Properties" and click on the "Custom" tab.
2) In the "Name" field enter "##EFFECTIVE_DATE##" without the quotes.
3) Select "Date" in the "Type" drop-down list.
4) Enter any date in the "Value" field. It doesn't matter what date you enter as this will automatically be overwritten when the macro runs.
5) Click the "Add" button then click "Ok"
6) Insert the field into the appropriate place in the document using the "Insert->Field" command like you normally do.
7) Go to "Tools->Macro->Macros" if you have already entered the "AutoOpen" macro select it and click Edit, if not type "AutoOpen" into the Macro Name field and click the Create button.
8) Overwrite the auto-generated code with the following:

Sub AutoOpen()
Set tmpProps = ActiveDocument.CustomDocumentProperties
dPublishedDate = tmpProps.Item("##DATE_PUBLISHED##").Value
dEffectiveDate = DateAdd("d", 14, dPublishedDate)
tmpProps.Item("##EFFECTIVE_DATE##").Value = dEffectiveDate

Dim aStory As Range
Dim aField As Field
For Each aStory In ActiveDocument.StoryRanges
For Each aField In aStory.Fields
aField.Update
Next aField
Next aStory

ActiveDocument.Saved = True
End Sub

Method 2 (Field Code): With this method you would add in a field using the "Insert->Field" command and put in some custom field codes that will calculate the effective date. This is much more difficult than using the macro because you cannot simply copy and paste the field code into the Word document. Most of it can be copied and pasted however each pair of curly braces {} would have to be entered by pressing ALT+F9 which turns out to be a bit of a pain especially with how long the field codes are.

{QUOTE
{SET Delay 14}
{SET a{=INT((14-{DOCPROPERTY ##DATE_PUBLISHED## \@ M})/12)}}
{SET b{={DOCPROPERTY ##DATE_PUBLISHED## \@ yyyy}+4800-a}}
{SET c{={DOCPROPERTY ##DATE_PUBLISHED## \@ M}+12*a-3}}
{SET d{DOCPROPERTY ##DATE_PUBLISHED## \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
{=dd*10^6+mm*10^4+yy \# "00'-'00'-'0000"} \@ "dddd, d MMMM yyyy"}

I made each of the curly braces in the above code bold so you can tell them apart easier. Again each of these pairs of curly braces must be entered in Word by pressing ALT+F9 rather than typing them in.

You would also need to keep in mind that Word 2003 does not automatically update these fields so in order to force an update in this version of Word you would need to add in a macro that would do this.


Although you do have the security warning to worry about, method #1 would be my recommendation. It would be easier to implement and can easily be copied from one document to another.

*Credit for the Word field codes above goes to Woody's Lounge. See this forum post for more information.

Clear Type: Better readability for all!

A while back I came across some information about the Clear Type technology that is included in Windows XP, but disabled by default. Clear Type adds lines of resolution to font rendering by working with sub-pixel coloring which makes fonts appear smoother and more readable. According to Microsoft this technology has the biggest impact on LCD displays but can marginally improve some CRT displays as well. I have not turned on clear type font rendering on a computer with an LCD display yet so I have not bed of comparison, but I really love the improvement it has made on my CRT!

Microsoft has an online tool for turning on and tuning your clear type fonts available at http://www.microsoft.com/typography/ClearType/tuner/Step1.aspx. They also have a power toy available at http://www.microsoft.com/typography/ClearTypePowerToy.mspx which will install itself as a control panel applet.