The Old Joel on Software Forum: Part 4 (of 5) - Quirk: Setting a formula in Excel from VB

Quirk: Setting a formula in Excel from VB

I was creating an Excel worksheet through VB and I was to set the formula for each row in the sheet. The formula I wanted for a particular cell in, say, row 6 was:

=((D6*G6)+(E6*1.5*G6)+I6+J6+K6+L6)

Here's what I wrote:

code snippet:
--------------------------------------------------------------------------------
StrFormula = " =((" & GetColumnAlphabet(colRegularHours) & LngCurrentRow & "*" & GetColumnAlphabet(colPayRate) & LngCurrentRow & ")" & _
"+(" & GetColumnAlphabet(colOvertime) & LngCurrentRow & "*1.5*" & GetColumnAlphabet(colPayRate) & LngCurrentRow & ")" & _
"+" & GetColumnAlphabet(colSalary) & LngCurrentRow & "+" & GetColumnAlphabet(colCommission) & LngCurrentRow & _
"+" & GetColumnAlphabet(colBonus) & LngCurrentRow & "+" & GetColumnAlphabet(colOther) & LngCurrentRow & ")"
Debug.Print StrFormula
Range(GetColumnAlphabet(colGrossTotal) & LngCurrentRow).Formula = StrFormula
--------------------------------------------------------------------------------

When worksheet was displayed, the formula for the cells was not set. Instead, the formula for each row became the text/contents the cells in each row. This troubled me for a long time, since it was such a plain and easy thing, and I kept re-reading my code snippet to see what I could have been missing. I ran the code in debug mode, but couldn't see reason for error. I sought advise on many forums. People had different suggestions, but none seemed to help. Then one person on Usenet spotted the problem. It was just that there was a leading space with the first formula, and that is why it did not work.

--------------------------------------------------------------------------------
StrFormula = " =((" & GetColumnAlphabet(colRegularHours) & LngCurrentRow & "*" &
--------------------------------------------------------------------------------

Note the leading space "" =((" & Get..." there. I trimmed the space and it worked correctly.

Sathyaish Chakravarthy
Friday, April 16, 2004