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

"Jacob Skaria" wrote:

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


"Bernie" wrote:

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?

"Jacob Skaria" wrote:

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


"Bernie" wrote:

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...


"Jacob Skaria" wrote:

Referencing another open workbook named workbook.xls

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

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


"Bernie" wrote:

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.