A
Andrew Testa
Hello all,
I have a question on the possibility of using a complex algorithm on
worksheet repeatedly, as if it were a function or subroutine bein
called by other worksheets. I have a need to perform multipl
calculations using the same algorithm, but I don't think it can be don
in VBA. In general, I have a separate worksheet that takes a number i
and provides a number out; simple enough. However, the sheet contain
complex calculations, list lookups, iterative functions, weird alchemy
and many nested functions. I couldn't write the algorithm as a linea
function in VBA, particularly since I've never used VBA.
The problem that arises is that I have to apply this algorithm to
growing number of values. To date I've been just adding copies of th
sheet for each calculation required, but that won't fly. I may reach
point where I need to perform this calculation on up to 100 arra
elements, so being able to use just a single sheet and feeding it
list of the elements is a necessity.
If anyone knows a way to do this I'd really appreciate the help. I'
pretty much stumped.
Andy
btw, if anyone needs more detail, here's what's going on. I'm workin
with chains of matrix multiplications where each element of each matri
needs to pass through an algorithm that massages the value and drops i
into a parallel matrix. So I have two results; a matrix derived fro
the unaltered elements, and a matrix derived from all of the altere
elements. Right now each matrix element has to link to its own shee
for the calculation. for a simple multiplication of two 3 x 3 matrice
that means 18 algorithm sheets for the source matrices, and 9 more fo
the product matrix. All 27 of the sheets are identical, which is a hug
waste. And I will have to do many matrix multiplications. As you ca
tell I'm up the creek unless I can use that sheet like a function.
Thanks
I have a question on the possibility of using a complex algorithm on
worksheet repeatedly, as if it were a function or subroutine bein
called by other worksheets. I have a need to perform multipl
calculations using the same algorithm, but I don't think it can be don
in VBA. In general, I have a separate worksheet that takes a number i
and provides a number out; simple enough. However, the sheet contain
complex calculations, list lookups, iterative functions, weird alchemy
and many nested functions. I couldn't write the algorithm as a linea
function in VBA, particularly since I've never used VBA.
The problem that arises is that I have to apply this algorithm to
growing number of values. To date I've been just adding copies of th
sheet for each calculation required, but that won't fly. I may reach
point where I need to perform this calculation on up to 100 arra
elements, so being able to use just a single sheet and feeding it
list of the elements is a necessity.
If anyone knows a way to do this I'd really appreciate the help. I'
pretty much stumped.
Andy
btw, if anyone needs more detail, here's what's going on. I'm workin
with chains of matrix multiplications where each element of each matri
needs to pass through an algorithm that massages the value and drops i
into a parallel matrix. So I have two results; a matrix derived fro
the unaltered elements, and a matrix derived from all of the altere
elements. Right now each matrix element has to link to its own shee
for the calculation. for a simple multiplication of two 3 x 3 matrice
that means 18 algorithm sheets for the source matrices, and 9 more fo
the product matrix. All 27 of the sheets are identical, which is a hug
waste. And I will have to do many matrix multiplications. As you ca
tell I'm up the creek unless I can use that sheet like a function.
Thanks