PDA

View Full Version : [SOLVED] Running multiple VBA functions within one sub



jroberts1
04-15-2015, 12:19 PM
I have a few functions that use the same variables, but would like to consolidate them into one function or sub. Here's an example of a few that I'm using...

Function dONE(stock, exercise, time, interest, sigma)
dONE = (Log(stock / exercise) + interest * time) / (sigma * Sqr(time)) + 0.5 * sigma * Sqr(time)
End Function

Function dTWO(stock, exercise, time, interest, sigma)
dTWO = dONE(stock, exercise, time, interest, sigma) - sigma * Sqr(time)
End Function

Function BSCall(stock, exercise, time, interest, sigma)
BSCall = stock * Application.NormSDist(dONE(stock, exercise, time, interest, sigma)) - exercise * Exp(-time * interest) * Application.NormSDist(dTWO(stock, exercise, time, interest, sigma))
End Function

Function bsput(stock, exercise, time, interest, sigma)
bsput = BSCall(stock, exercise, time, interest, sigma) + exercise * Exp(-interest * time) - stock
End Function

Function deltaput(stock, exercise, time, interest, sigma)
deltaput2 = Application.NormSDist(dONE(stock, exercise, time, interest, sigma)) - 1
End Function


So I was wondering if I could do something like...

"Sub FIN(stock, exercise, time, interest, sigma, keyword)"

and then having the keyword call on the appropriate function, or perhaps reference the function within the coding below it. Any suggestions?

Paul_Hossler
04-15-2015, 01:02 PM
don't have any live data to really test, but you should be able to combine functionality





Option Explicit
Sub drv()
MsgBox FIN(100, 50, 4, 0.1, 2, "dONE")
MsgBox FIN(100, 50, 4, 0.1, 2, "dTwo")
MsgBox FIN(100, 50, 4, 0.1, 2, "BSCall")
MsgBox FIN(100, 50, 4, 0.1, 2, "BSPut")
MsgBox FIN(100, 50, 4, 0.1, 2, "Deltaput")
MsgBox FIN(100, 50, 4, 0.1, 2, "BadInput")
End Sub


Function FIN(stock, exercise, time, interest, sigma, keyword) As Variant
Dim tempOne As Double, tempTwo As Double, tempBS As Double

Select Case LCase(keyword)

Case "done"
FIN = (Log(stock / exercise) + interest * time) / (sigma * Sqr(time)) + 0.5 * sigma * Sqr(time)

Case "dtwo"
tempOne = FIN(stock, exercise, time, interest, sigma, "dONE")
FIN = tempOne - sigma * Sqr(time)

Case "bscall"
tempOne = FIN(stock, exercise, time, interest, sigma, "dONE")
tempTwo = FIN(stock, exercise, time, interest, sigma, "dTWO")
FIN = stock * Application.NormSDist(tempOne) - exercise * Exp(-time * interest) * Application.NormSDist(tempTwo)

Case "bsput"
tempBS = FIN(stock, exercise, time, interest, sigma, "bscall")

FIN = tempBS + exercise * Exp(-interest * time) - stock


Case "deltaput"
tempOne = FIN(stock, exercise, time, interest, sigma, "dONE")
FIN = Application.NormSDist(tempOne) - 1

Case Else
FIN = CVErr(xlErrNA)
End Select
End Function

jroberts1
04-18-2015, 06:30 PM
Works great, thanks Paul!