How to split a VBA formula? (ERROR 13)

[rvExcelTip]

I've lost several productive(?) hours (stupid me) investigating th
extremely helpful -ERROR 13: Types do not correspond- Message on th
following case:

I had written a rather complicated VBA formula continued on two line
as follows:

a_varValues
rngYLabel.Offset(lngRunIndex + 1
rvxlColumnNameToNumber(c_strKolomEersteWeek) & _

-rvxlColumnNameToNumber(c_strKolomAfdelingsNaam)).Resize(1
intLastWeek).Value

where a_varValues was Dimmed as a Variant and rngYLabel was a singl
cell (valid) range.

Excel systematically refused to execute the statement with the abov
error message as an excuse.

After having tried all sorts of alternatives, I finally wrote th
statement on a single line:

a_varValues
rngYLabel.Offset(lngRunIndex + 1
rvxlColumnNameToNumber(c_strKolomEersteWeek)
rvxlColumnNameToNumber(c_strKolomAfdelingsNaam)).Resize(1
intLastWeek).Value

and Bingo! it worked.

Further investigating the expression in the immediate window, I naile
down the cause of the error to the subexpression
?rvxlColumnNameToNumber(c_strKolomEersteWeek) & _

-rvxlColumnNameToNumber(c_strKolomAfdelingsNaam) which evaluated t
6-2

wheras the single line expression
rvxlColumnNameToNumber(c_strKolomEersteWeek)
rvxlColumnNameToNumber(c_strKolomAfdelingsNaam)
evaluates to 4. And that seems to make a whole difference.

1. Has somebody an explanation for this strange behaviour?
2. How can I properly split the expression, so that it can be viewe
without scrolling

 

[Charles Williams]

Get rid of the &

When you use & (concatenate strings operator) you are telling VBA to convert
the arguments to strings and concatenate them , so you get 6 as a string
followed by -2 as a string.

and trying to use a string as an offset gives you a type mismatch.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[rvExcelTip]

Good eyes Charles!! I was fooled by the habit of splittin
concatenations of strings into multiple lines, earlier in the program.
Although this is no excuse.

Thanks!