Need help with VBA macro/function

[DrCL]

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.

 

[Richie(UK)]

Hi Dr,

Welcome to the board

Functions cannot change cells other than the ones in which they are contained. The basic structure of the current approach is never going to work. The best (and its not really very good!) that you could do would be to try and use the Calculate event to test whether any cells contain your UDF and then act accordingly.

For example, if you are entering the numberfun UDF in Sheet1 then right-click on the sheet tab and select View Code. Paste in the following code:

Private Sub Worksheet_Calculate()
    Dim rngFormulas As Range, rngCell As Range

    Set rngFormulas = Me.Cells.SpecialCells(xlCellTypeFormulas)
    For Each rngCell In rngFormulas
        If rngCell.Formula Like "=numberfun(*" Then
            MsgBox "Found our UDF"
        End If
    Next rngCell
    
End Sub

This event fires each time that the sheet calculates. It looks through all of the cells containing formulas (using the SpecialCells method to restrict the range searched) and tries to find our UDF.

This is far from ideal. It will fire on all calculations, not just ones involving our UDF. It loops through all formulas. You will also need to add some sort of test to see whether the formula has already been caught by a previous event call to avoid calling the messagebox (or whatever) everytime there is a calculation. This could be unworkable on a large sheet with lots of calculations.

All in all, I'd look for a different approach - perhaps a sub (rather than a function) that collects input for all of the cells concerned and then outputs them to your chosen cells.

 

[tusharm]

Dunno why you have a InputBox in a function? Wouldn't it be easier to have the person enter in the information into the cell of interest?

As has already been pointed out, a UDF can only 'modify' the result shown in the range in which it is entered. However, a range is not a single cell. A UDF can be array entered into multiple cells, just like an XL native function.

For example, the function below returns an array:

Option Explicit

Function ArrayUDF(x As Range)
    Dim Rslt(), i As Integer, aCell As Range
    ReDim Rslt(1 To x.Cells.Count)
    i = 1
    For Each aCell In x
        If IsNumeric(aCell.Value) Then
            Rslt(i) = aCell.Value * 2
        Else
            Rslt(i) = aCell.Value
            End If
        i = i + 1
        Next aCell
    ArrayUDF = Rslt
    End Function

Use it as follows: enter some combination of text and numbers in a row (say A1:D1).
Then, in another row, select the same number of cells (say B2:E2). Array enter the =ArrayUDF(A1:D1)

To array-enter a formula, complete formula entry with CTRL+SHIFT+ENTER rather than just the ENTER key. If done correctly, *XL* will show the formula with braces { and } around it.

{snip}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

{snip}