I know Basic but not Visual Basic in Excel,
but so far I have been able to create several specialized financial
functions that I have needed in Excel.
Now I need Excel to calculate a number and fill the cells to the right
with some data.
Let assume I inserted a module in Excel with the following function
Public Function numberfun(num1, num2)
ib1 = InputBox("Enter Your Name: ", "NAME")
ib2 = InputBox("Enter Age: ", "AGE")
ib3 = InputBox("Enter Todays Date (yyyy/mmm/dd): ", "DATE")
numberfun = num1 + num2
End Function
If I write in cell A10, for example, the following formula:
=numberfun(100,2)
and someone enters "Joe Smith" (for the name-ib1)
and "33" (for age-ib2)
and "2004/JUN/17" (for the date-ib3)
I want the VB code to return in Cell A10 ->102,
ib1 in Cell b10-> Joe Smith, ib2 in cell c10->33 and
ib3 in Cell D10->2004/JUN/17
For what I am doing, I do not want separate formulas in cells b10,
c10, d10 asking separately in each one for the Name, age, date.
In other words, when cell A10 runs (or the cell with the formula), it
should fill the cells to the right with the proper info.
Or if I enter =numberfun(100,2) in cell D5 then it should fill cells
to its right (E5<=ib1, F5<=ib2, G5<=ib3).
(I think this is called relative references).
So far, I have only created functions in Excel (not familiar yet with
Excel VBA subroutines)
and I don't know whether I need a function or subroutine for what I need.
I need the VB code to put in an Excel module to fill cells to the right. |
Clarification of Question by
drcl-ga
on
17 Jun 2004 14:33 PDT
The answer of justabum-ga (on 17 Jun 2004 10:45 PDT)
was a great comment and trick! Thanks! It worked like magic for
the sample code. However, the sample code I included when I asked the
question was an oversimplification of my real financial calculation
problem (which really does not use inputboxes nor it just simply add
two numbers) where I calculate and keep track of several probabilities
and breakevens of pairs of stock options. Those numbers that I
calculate and keep track in the routine are the ones I need to appear
in the cells to to right. If I had one row in Excel with the
information of just one pair of options your answer and code works
very good. The problem is that the Excel spreadsheet with my real
financial problem has literally thousands of rows (each with
information about a paricular combination of a unique pair of options)
and it would not be practical for me to follow your logic (select four
cells, edit, push CTRL+SHFT+ENTER) for each of the thousands of rows I
have. Or would it work anyhow? (In my initial test of your logic it
repeated the same answers for every row. Could it be that I did
something wrong?). If I need to explain something more elaborated,
let me know. I hope there is a convenient way (function, subroutine,
or whatever) of sending values to cells to the right that also can be
copied or used for thousands of rows.
Thanks!
|
Request for Question Clarification by
mathtalk-ga
on
17 Jun 2004 15:29 PDT
Hi, drcl-ga:
Perhaps you should look into the idea that justabum-ga has presented
under the Excel Help topic "Create an array formula", which allows you
to assign a matrix valued expression to a range of cells. The cells
must be "tied together" by the Ctrl+Shift+Enter keystroke in creating
the array formula (so that all cells are assigned a value with the
evaluation of the single matrix expression).
Now you seem to have shifted gears, and you are focusing not on how to
do this with one formula and one set of cells, but on how to repeat
this for (say) a thousand sets of cells. It's possible that if you
give a clearer explanation of what you are evaluating, then a more
satisfactory suggestion could be made on how to automate the
construction of the desired spreadsheet.
regards, mathtalk-ga
|
Clarification of Question by
drcl-ga
on
17 Jun 2004 22:37 PDT
I hope this helps to clarify (if not, let me know). A few days ago I
finished one Excel function called BSEXP (which for me is an
abbreviation of Black-Scholes Expected return of an option spread).
Below is a summary of the VBA module:
Public Function BSEXP(call1P01, days1, strike1 As Double, optprice1 As
Double, call1p02, days2, strike2 As Double, optprice2 As Double,
numdays, stockprice As Double, Volatmedian As Double, intrate As
Double, divyield As Double, qty1, qty2) As Double
Application.ScreenUpdating = False
...
Invest = (I have here a formula to calculate the cost of a particular
option spread, and this might be the first number I will need in a
cell to the right of the current cell)
?
(then I have a FOR/NEXT that loops at least 100 times and keeps track
of several variables)
For Pricex = LoPrice To HiPrice Step incremx
. . .
Breakeven1=(I keep track of the first sign change of returns, for
positive to negative or viceversa)
Breakeven2=(Similar to Breakeven1 because there is frequently, but
not always, a second sign change for an option sptread at higher stock
prices)
(I will need Breakeven1 in a cell more to the right of the current
row, and another for Breakeven2. )
. . .
ProfitProbability=Profitprobability + (I have a formula here)
Cumexpectedreturn=cumexpectedreturn+(I have formula here)
Next
. . .
. . .
BSEXP= Cumexpectedreturn
(After the FOR/NEXT loop I need the value of ProfitProbability in a
cell somewhere in the right of the current row. And for other
variables)
? BSEXP= ProfitProbability
? BSEXP= Invest
? BSEXP= Breakeven1
? BSEXP=Breakeven2
. . .
End Function
The way I am getting the numbers I need in Excel in the same row
(INVEST, BE1,BE2, PROFITPROBABILITY, ETC) where I have info for a
particular pair of option spreads is by having a different formula in
each cell to the right of the option spread information row with
virtually identical functions except for the ending value assignment
(instead of BSEXP= Cumexpectedreturn, I would have BSEXP=
ProfitProbability, and so on). For example in row 1 of Excel (cells
A1 to K1) I would have info for a particular pair of option spreads,
then in Cell :
L1 = BSEXP(with its parameters taken from info in that row 1 to
calculate Cumexpectedreturn)
M1 = BSEXPPROFITPROBABILITY(same parameters of row 1 used in BSEXP)
N1 = BSEXPBE1(same parameters again)
O1 = BSEXPBE2(same parameters again)
And so on.
The second row in Excel would have information for a different
combination of another particular pair of option spreads (in row 2
cells A2 to K2). There are many rows with different combinations of
option spreads (usually over 3000 rows or option spread combinations,
perhaps up to A3000 to K3000). I just copy the formulas in L1 to O1 up
to row 3000 (for example). This works getting the numbers I need, but
this is a very inefficient and time consuming process of getting the
values because I use many slightly different functions (with the same
loops that repeat at least 100 times, etc.) for the same parameters in
the row that only differ in the end assignment. That is the reason I
posted the question. I hope there is a more elegant way.
I thought that the answer to my question would be a macro or
subroutine within my function similar to (the code below did not work
but it might give an idea to those that know VBA for Excel. Perhaps I
don?t know how to declare something since I know very little VBA):
Public sub putnextnum(numberx)
. . .
Activecell.offset(0,1).Range(?A1?).select
Activecell.formular1c1=numberx
. . .
End Sub
So I expected that within the function BSEXP there could be some code
or subroutine like ?putnextnum? where I could have it to write to the
cells to the right the numbers I need doing something similar to the
following:
. . . putnextnum(Cumexpectedreturn)
. . . putnextnum(ProfitProbability)
. . . putnextnum(invest)
. . . putnextnum(Breakeven1)
. . . putnextnum(Breakeven2)
Etc.
I have tried many variations of the code above but it does not work or
gives me errors (such as VALUE). Maybe the idea of "cell selection and
Activecell.formular1c1=numberx" is in the right track, but I still
can't get the function to write the values I need to the cells in the
right.
|
Request for Question Clarification by
mathtalk-ga
on
20 Jun 2004 15:41 PDT
Hi, drcl-ga:
Perhaps, rather than a function which would be called from within a
cell, you would be better off with an approach that would define rows
of input data as you seem to have them now in your Excel spreadsheet,
and tie the evaluation of the "function" (really a procedure) that
writes results into independent cell ranges to the pressing of a
button. Excel's user-interface allows you to create a "button" on a
form which invokes arbitrary VBA code.
I'm sure that you have a clear idea of what you're trying to do, but
it will surely be evident that spending the time to pin down your
requirements to a point where actual code can be written is taking an
effort far in excess of what might reasonably be expected for $3.50.
Please review the Pricing Guidelines here:
[Google Answers: How to price your question]
http://answers.google.com/answers/pricing.html
and suggest how you wish to proceed. My suggestion is that a working
example of an Excel spreadsheet with a button-driven procedure that
takes data from a sequence of rows and computes multiple statistics
for each to output in a segregated range of cells might be priced at
$50 or more. Something less than this might be offered for a
collection of links to documentation of the various Excel features
involved, but I'm mindful of your disclaimer that you "know very
little VBA".
regards, mathtalk-ga
|
