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.
I suspect that I might need a subroutine witin the function, but I tried the following and it did not work (perhaps a little change would make it work but I don't know too much VBA).
Public sub putnextnum(numberx)
. . .
Activecell.offset(0,1).Range(“A1”).select
Activecell.formular1c1=numberx
. . .
End Sub
$$$$$$$ $$$$$$$ $$$$$$$
$$$$ READ BELOW ONLY IF YOU NEED CLARIFICATION $$$$$
$$$$$$$ $$$$$$$ $$$$$$$
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.
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.
I suspect that I might need a subroutine witin the function, but I tried the following and it did not work (perhaps a little change would make it work but I don't know too much VBA).
Public sub putnextnum(numberx)
. . .
Activecell.offset(0,1).Range(“A1”).select
Activecell.formular1c1=numberx
. . .
End Sub
$$$$$$$ $$$$$$$ $$$$$$$
$$$$ READ BELOW ONLY IF YOU NEED CLARIFICATION $$$$$
$$$$$$$ $$$$$$$ $$$$$$$
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.