VBA formula too long?
 

Hi,

I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.

I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what today's
date is). See code below. In reformatting however the formula is pushed onto
a new line of code and the usual "_" does not seem to read as me wanting the
code to continue on the next line.

Is it possible to have a formula that is simply too long?

I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better way
of doing everything that I do :) I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more simple
solution.

(I haven't finished reformatting after I encountered the problem.)

EXCEL FORMULA

=(SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))

REFORMATTED VBA CODE

ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_
<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"


Thanks in advance,
JohnP

VBA formula too long?
 

Although you might not appreciate it I'd say Excel has done you a favour by
not accepting a formula longer than 255 characters.

I'm not going to attempt to read the purpose of your formula but I'm
guessing you could put those "words" in a list (ie cells), and with a
different formula end up with the same result. And a much shorter formula.
Another thing that might be relevant is naming both the word list and
A26:A391, perhaps with dynamic names.

Regards,
Peter T

"JohnP" wrote in message
...
Hi,

I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.

I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what
today's
date is). See code below. In reformatting however the formula is pushed
onto
a new line of code and the usual "_" does not seem to read as me wanting
the
code to continue on the next line.

Is it possible to have a formula that is simply too long?

I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better
way
of doing everything that I do :) I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more
simple
solution.

(I haven't finished reformatting after I encountered the problem.)

EXCEL FORMULA


=(SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I
391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A
391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")
))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*
(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMP
RODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26
:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUM
PRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))

REFORMATTED VBA CODE

ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R["
&
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R["
&
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_"

<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist""
)))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"") ))-(SUMPRODUCT((A26:A3
91<B1)*(I26:I391=""Maternity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Pate
rnity"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"


Thanks in advance,
JohnP

VBA formula too long?
 

Why don't you write you own UDF function. Here is a start

ActiveCell.Offset(-11, 0).formula = "=Myfunction(A26:I391)"


Myfunction(Target as Range)
NumberofRows = target.Rows
FirstPart = WorksheetFunction.SumProduct( _
"(" & target.Column(1) & "<B1)*(" & target.Column(9) & "<"")")

(A26:A391<B1)*(I26:I391<"")
end function

"JohnP" wrote:

Hi,

I am creating a utilisation tool in Excel which contains data on Sickness,
Holidays and Other Absence. I have created the formula below in Excel to
account for how much Other Absence has been taken year to date.

I have then transferred this into VBA and changed the format to R1C1 and
adapted the ranges so that they are defined by two variables (j - start of
year, based on what today's date is & k - end of year, based on what today's
date is). See code below. In reformatting however the formula is pushed onto
a new line of code and the usual "_" does not seem to read as me wanting the
code to continue on the next line.

Is it possible to have a formula that is simply too long?

I am getting more confident in Excel and VBA now and can pretty much do
everything I want to, but I am equally confident that there is a better way
of doing everything that I do :) I am not especially fond of the formula /
code below so please feel free to scratch it if that provides a more simple
solution.

(I haven't finished reformatting after I encountered the problem.)

EXCEL FORMULA

=(SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-((SUMPRODUCT((A26:A391<B1)*(I26:I391<"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Sick")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Education")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Child")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Doctor")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Dentist")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Emergency")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Maternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Paternity")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391="Flexi"))))

REFORMATTED VBA CODE

ActiveCell.Offset(-11, 0).FormulaR1C1 = "=(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-((SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C<"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1<R1C2)*(R[" &
j - 13 & "]C:R[" & k - 13 & "]C=""Sick"")))-(SUMPRODUCT((R[" & j - 13 &
"]C1:R[" & k - 13 & "]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Education"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 &
"]C1<R1C2)*(R[" & j - 13 & "]C:R[" & k - 13 &
"]C=""Child"")))-(SUMPRODUCT((R[" & j - 13 & "]C1:R[" & k - 13 & "]C1_"
<B1)*(I26:I391=""Doctor"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Dentist"")))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Emergency"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Maternity"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Paternity"") ))-(SUMPRODUCT((A26:A391<B1)*(I26:I391=""Flexi""))))"


Thanks in advance,
JohnP