VBA formula too long?

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: VBA formula too long? by Peter

Peter
Fri May 09 11:22:04 CDT 2008

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" <JohnP@discussions.microsoft.com> wrote in message
news:C3336077-468C-48F6-9D3D-B3FEA1725EFA@microsoft.com...
> 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


Top

RE: VBA formula too long? by Joel

Joel
Fri May 09 11:43:01 CDT 2008

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
Top