CODEWITHSUNDEEP
vbaexcel
user2997767
user2997767

Reputation: 25

Running a Vba function on button press not working in excell

this is a simple stock price change code my code is function(with parameters)

    Function VanillaCall(S0 As Single, Exercise As Single, Mean As Single, sigma As Single, _
Interest As Single, Time As Single, Divisions As Integer, Runs As Integer) As Single

deltat = Time / Divisions
interestdelta = Exp(Interest * deltat)
up = Exp(Mean * deltat + sigma * Sqr(deltat))
down = Exp(Mean * deltat - sigma * Sqr(deltat))
pathlength = Int(Time / deltat)

piup = (interestdelta - down) / (up - down)
pidown = 1 - piup
Temp = 0

For Index = 1 To Runs
    upcounter = 0
    For j = 1 To pathlength
        If Rnd > pidown Then upcounter = upcounter + 1
    Next j
        callvalue = Application.Max(S0 * (up ^ upcounter) * (down ^ (pathlength - upcounter)) - Exercise, 0) / (interestdelta ^ pathlength)
        Temp = Temp + callvalue
Next Index

VanillaCall = Temp / Runs

End Function

parameters are passed from cells in excel. i want to execute this function from button click and display return value in a cell say b12. i have tried putting the code inside a button sub but its not working ,a call vanillacall inside sub too isnt working. like..

private sub button1_click()
call vanillacall
end sub

Upvotes: 1

Views: 346

Answers (3)

Harley B
Harley B

Reputation: 569

I'd do something like the below which would allow me to pick the range containing the data I want to pass to the function (as long as the range is contiguous and contains 8 cells) and pick the cell I want to output the result to.

Private Sub button1_click()
Dim inRng As Range, outRng As Range

inSelect:
Set inRng = Application.InputBox("Select Range to Calculate", Type:=8)
    If inRng.Cells.Count <> 8 Then
        MsgBox "Select a range with 8 cells!", vbCritical
        GoTo inSelect
    End If
outSelect:
Set outRng = Application.InputBox("Select Cell to Output To", Type:=8)
    If outRng.Cells.Count > 1 Then
        MsgBox "Select only one cell!", vbCritical
        GoTo outSelect
    End If

outRng.Value = VanillaCall(inRng.Cells(1), inRng.Cells(2), inRng.Cells(3), inRng.Cells(4), inRng.Cells(5), inRng.Cells(6), inRng.Cells(7), inRng.Cells(8))

End Sub

Upvotes: 1

D_Bester
D_Bester

Reputation: 5931

You need to get the values from the sheet and save in variables. Then pass the variables to the function. Then output the result to the sheet somewhere. You will need to adjust the range addresses and worksheet name as appropriate.

Private sub button1_click()

    dim ws as worksheet
    Set ws = worksheets("Sheet1") ' < change the sheet name as appropriate

    dim S0 As Single
    dim Exercise As Single
    dim Mean As Single 
    dim sigma As Single
    dim Interest As Single
    dim Time As Single
    dim Divisions As Integer
    dim Runs As Integer As Single

    S0 = ws.Range("B1") '< specify the cell that has this data
    Exercise = ws.Range("B2") '< specify the cell that has this data
    Mean   = ws.Range("B3") '< specify the cell that has this data
    sigma  = ws.Range("B4") '< specify the cell that has this data
    Interest  = ws.Range("B5") '< specify the cell that has this data
    Time  = ws.Range("B6") '< specify the cell that has this data
    Divisions  = ws.Range("B7") '< specify the cell that has this data
    Runs = ws.Range("B8") '< specify the cell that has this data

    dim Result as Single
    Result = vanillacall(S0, Exercise , Mean, sigma, Interest, Time, Divisions, Runs)
    ws.Range("B10") = Result '<specify the cell where you want the result

end sub

Upvotes: 0

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Private Sub button1_click()
    Range("B12").Value = vanillacall(....)
End Sub

As per your request, Pass arguments in Range like below. Below code is just for example (due to the changes in excel data)

Sub testing33()
    Range("B12") = sample(Range("A5"), Range("B5"))
End Sub

Function sample(a As Range, b As Range)
     sample = a.Cells.Value & ", " & b.Cells.Value
End Function

Upvotes: 1

Related Questions