How to call a worksheet (as a function)

[Bernie]

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes

 

[Bernie]

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

If this post helps click Yes
---------------
Jacob Skaria

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

Hi - it will not be in another workbook. It can even be on the same worksheet.

UDF ... OK, its the first time I hear this. Pls. let me check it out.
Thanks.

Bernie

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

If this post helps click Yes
---------------
Jacob Skaria

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

Does UDF require VBA programming? The calculation is about 30 worksheets
long, and about 50 lines on each worksheet, and 200 cells on each line. Are
you saying I have to rewrite 300,000 cells or thousands of arrays in VB?

Now I am really confused. If I already have a working set of worksheets,
why do I have to rewrite the whole thing into VB to make a UDF out of that?

If I just have to have a 1 liner VBA, could you then pls. write it for me,
and tell me how to enter that into Excel? Thanks a lot.

thanks

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

If this post helps click Yes
---------------
Jacob Skaria

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

Sorry, could you please let us know what the calculation is about..and what
formula is used..
--
If this post helps click Yes
---------------
Jacob Skaria

Does UDF require VBA programming? The calculation is about 30 worksheets
long, and about 50 lines on each worksheet, and 200 cells on each line. Are
you saying I have to rewrite 300,000 cells or thousands of arrays in VB?

Now I am really confused. If I already have a working set of worksheets,
why do I have to rewrite the whole thing into VB to make a UDF out of that?

If I just have to have a 1 liner VBA, could you then pls. write it for me,
and tell me how to enter that into Excel? Thanks a lot.

thanks

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

If this post helps click Yes
---------------
Jacob Skaria

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

Its a massive financial calculation, up to 30 worksheets and uses a lot of
functions and operators and conditionals. It takes 2 parameters as input and
produces one result output. I want to call this massive function over and
over again from within the same workbook, but with different input parameters
(and lets say chart the output or create a table of the output). Thanks

Sorry, could you please let us know what the calculation is about..and what
formula is used..
--
If this post helps click Yes
---------------
Jacob Skaria

Does UDF require VBA programming? The calculation is about 30 worksheets
long, and about 50 lines on each worksheet, and 200 cells on each line. Are
you saying I have to rewrite 300,000 cells or thousands of arrays in VB?

Now I am really confused. If I already have a working set of worksheets,
why do I have to rewrite the whole thing into VB to make a UDF out of that?

If I just have to have a 1 liner VBA, could you then pls. write it for me,
and tell me how to enter that into Excel? Thanks a lot.

thanks

You cannot pass values to a calculation residing in another workbook. You can
acheive this by using user defined functions. The complex calculations needs
to be written as User Defined functions UDFs

If this post helps click Yes
---------------
Jacob Skaria


:

Hi Jacob - But everytime I refer to this worksheet, I want to pass it a
different parameter (i.e. the values in column A and B in the calling
worksheet). So for example the 4th row of the caller will want to pass the
value (4,14) to WS. (what you have indicated tells me how to use the return
values - so thanks for this part).

:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Jacob Skaria]

If it is a table you can lookup and get the values. You cannot pass values
and return the calculations. You need to write a UDF once which does the
calc's and then use the UDF from cells as shown below..

which picks values from A1 and B1,. Does the calculation and returns the
value..

=MyUDF(A1,B1)

If this post helps click Yes
---------------
Jacob Skaria

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

:

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Dave]

Hi Bernie,
When you say:

"In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value."

I'm not sure what you mean. Could you give an example of the formulas you
are currently using in Column C?

Regards - Dave.

 

[Bernie]

Hi Dave - let me start over - I have a 30 page worksheet that takes 2 inputs
and creates one result.

On another page of same workbook, I want to "call" this worksheet 100 times
with different input parameters and get the 100 results. This could be a
table for example. How do I call a worksheet or calculation (very
complicated calculation that cannot be put in VB as its like 2000 lines of
code)?

The calculation done to the 2 input parameters is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 100 times with 100 different input parameters.
Obviously I cant write a 300,000 cell, 2000 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again.

So imagine a table 100 rows and 3 columns. The first two parameters are in
the first 2 columns and the result goes into the 3rd column on the same row.
How do I fill this table (dont want to use macros or VBA)?

 

[Bernie]

OK I understand - but are you saying the UDF I have to write will be as large
as the 30 page worksheets (thousands of lines)? Or are you saying there is
an easy way to write a UDF (that uses the worksheet). Please be clear. I
really dont want to write a huge UDF when I already have a debugged huge
worksheet.

If it is a table you can lookup and get the values. You cannot pass values
and return the calculations. You need to write a UDF once which does the
calc's and then use the UDF from cells as shown below..

which picks values from A1 and B1,. Does the calculation and returns the
value..

=MyUDF(A1,B1)

If this post helps click Yes
---------------
Jacob Skaria

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria


:

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

:

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[dhstein]

You have 2 choices here.

Option 1)
Move all formulas around so they are all on the same row. So the first
parameter is in cell A2, the second parameter is in cell B2 the result is in
cell C2. your first calculation is in cell D2, second calc in cell E2 . If
you need other worksheets, all of those calculations are in Row 2 using
whatever columns you need. Then copy all formulas down and you can build
your table that way. In that case the first result is in row 2, the second
result is in row 3, etc. You have a lot of worksheets so this may not be a
good option.

Option 2)
Write a simple VBA loop that takes values from a table somewhere - say
WSTABLE!A1 and WSTABLE!B1, puts them into your cells WS1!A1, and WS1!A2 -
then takes the value in WS1!B1 and copies it into WSTABLE!C1 -

Something like this (Not debugged - may have errors - just as an example):

Sub MakeTable()

Dim J As Integer

Dim Range1 As String
Dim Range2 As String
Dim Range3 As String


For J = 1 To 100

Range1 = "WSTABLE!A" & J
Range2 = "WSTABLE!B" & J
Range3 = "WSTABLE!C" & J

Range("WS1!A1").Value = Range(Range1).Value
Range("WS1!A2").Value = Range(Range2).Value

' Might need a calc statement here - not sure about that

Range(Range3).Value = Range("WS1!B1").Value

Next J


End Sub

OK I understand - but are you saying the UDF I have to write will be as large
as the 30 page worksheets (thousands of lines)? Or are you saying there is
an easy way to write a UDF (that uses the worksheet). Please be clear. I
really dont want to write a huge UDF when I already have a debugged huge
worksheet.

If it is a table you can lookup and get the values. You cannot pass values
and return the calculations. You need to write a UDF once which does the
calc's and then use the UDF from cells as shown below..

which picks values from A1 and B1,. Does the calculation and returns the
value..

=MyUDF(A1,B1)

If this post helps click Yes
---------------
Jacob Skaria

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

:

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria


:

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

:

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.

 

[Bernie]

Thanks DHStein, I believe what you say is my solution. It will take a few
days for me to try out both methods and will be back. Also thanks to Jacob
and Dave.

There should be a method in Excel to Define a NAME for a piece of WS
calculation (in the same way that names can be defined for cells), and then
call that function name "=UDF(....)" and treat it as a simple function. All
the Define has to do is to associate the function parameters to cells in the
worksheet. Of course the question arises, if you switch to the worksheet,
what values will you see there? The last calculation?

You have 2 choices here.

Option 1)
Move all formulas around so they are all on the same row. So the first
parameter is in cell A2, the second parameter is in cell B2 the result is in
cell C2. your first calculation is in cell D2, second calc in cell E2 . If
you need other worksheets, all of those calculations are in Row 2 using
whatever columns you need. Then copy all formulas down and you can build
your table that way. In that case the first result is in row 2, the second
result is in row 3, etc. You have a lot of worksheets so this may not be a
good option.

Option 2)
Write a simple VBA loop that takes values from a table somewhere - say
WSTABLE!A1 and WSTABLE!B1, puts them into your cells WS1!A1, and WS1!A2 -
then takes the value in WS1!B1 and copies it into WSTABLE!C1 -

Something like this (Not debugged - may have errors - just as an example):

Sub MakeTable()

Dim J As Integer

Dim Range1 As String
Dim Range2 As String
Dim Range3 As String


For J = 1 To 100

Range1 = "WSTABLE!A" & J
Range2 = "WSTABLE!B" & J
Range3 = "WSTABLE!C" & J

Range("WS1!A1").Value = Range(Range1).Value
Range("WS1!A2").Value = Range(Range2).Value

' Might need a calc statement here - not sure about that

Range(Range3).Value = Range("WS1!B1").Value

Next J


End Sub

OK I understand - but are you saying the UDF I have to write will be as large
as the 30 page worksheets (thousands of lines)? Or are you saying there is
an easy way to write a UDF (that uses the worksheet). Please be clear. I
really dont want to write a huge UDF when I already have a debugged huge
worksheet.

If it is a table you can lookup and get the values. You cannot pass values
and return the calculations. You need to write a UDF once which does the
calc's and then use the UDF from cells as shown below..

which picks values from A1 and B1,. Does the calculation and returns the
value..

=MyUDF(A1,B1)

If this post helps click Yes
---------------
Jacob Skaria


:

The calculation done to the 2 input parameters (1 and 11) is huge. Thousands
of NPV() and PPMT() and functions and conditional statements - you name it,
including trigonometrics etc. are used in these 30 pages of financial
calculations.

So I need to "call" this 500 times with 500 different input parameters.
Obviously I cant write a 300,000 cell, 1500 line, 30 page VBA. I just simply
want to use my 30 page worksheet, which has been painfully debugged, over and
over again. I am not sure if I am answering your questions properly. But
what I need is called a function - i.e. making the worksheet set to act like
a function, that can be called with different input parameters. Its a very
simple thing really.

thanks

Bernie Glass

:

Understood. Still you have not mentioned what is the calculation done to get
the result by passing 1 and 11.

Based on the formula you use to get the result; we should be able to make a
UDF

If this post helps click Yes
---------------
Jacob Skaria


:

OK, assume my very complicated set of worksheets (WS1 thru WS30) require
values entered in WS1!A1 and WS1!A2, and the result is found in WS1!B1.

In the same workbook, I want this calculation to be done over and over
again. So lets say I want to call =WS1(1,11)!B1 which means put 1 in WS1!A1
and 11 in WS1!A2, and then return WS1!B1, and then further down I want to
call =WS1(2,13)!B1, and then =WS1(8,17)!B1, and so forth (500 different
calls).

How do I make the UDF, and is it a simple VBA function?

:

Sorry Bernie, you cannot do this coz the calculation is already referenced to
another cell. Meanwhile could you please post an example..

If this post helps click Yes
---------------
Jacob Skaria


:

In other words, I wish to "Call" a worksheet (in same or different workbook)
with a list of parameters (just like calling a VBA function or "subroutine").

This is a very basic thing to do. How do I pass parameters and call a
worksheet, or even a calculation elsewhere on the same worksheet?

Thanks...


:

Referencing another open workbook named workbook.xls

='[Workbook.xls]Sheet1'!$A$1

If this post helps click Yes
---------------
Jacob Skaria


:

This is a very fundamental question. If I have a worksheet (or a set of
worksheets) containing a complicated calculation, how do I refer to this
calculation from elsewhere with different input values (i.e., how do I call
this worksheet) and get its results?

Example: A worksheet called WS has input cells A1 through A2, and results
appear in B1 to B2.

In another worksheet I have a table with 10 rows and 4 columns. Column A
has values 1 thru 10, column B values 11 thru 20. column C is the result of
"calling" WS with the values A and B of the same row, and should receive the
B1 value of WS. column D again calls WS, but displays the B2 value.

I don't want to make 10 copies of the very complicated set of worksheets WS.

How do I "call" a worksheet and obtain one of many resulting value? I do
not want to do macro programming or VBA programming as it should be
unnecessary (I am doing spreadsheet programming already).

Thanks for all the help, in advance.