• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working with arrays in vba

B

bf123

New Member
Hi there,

my code is not awesome at the moment:) I wrote a code to calculate the black scholes formula in an array with all the inputs in different ranges. However, its not working right now and I am stuck at my debugging process. Can anyone please help? Please note that there are two UDFs below. The second UDF called "dOne" is used in the first one.

Code: 
Public Function CallOptArray(spot As Range, strk As Range, time As Range, rate As Range, Vol As Range, Div As Range) As Variant

Dim sArr As Variant
Dim kArr As Variant
Dim tArr As Variant
Dim rArr As Variant
Dim vArr As Variant
Dim dArr As Variant
Dim n As Long
Dim CallPxArr() As Variant

sArr = spot.Value2
kArr = strk.Value2
tArr = time.Value2
rArr = rate.Value2
vArr = Vol.Value2
dArr = Div.Value2

ReDim CallPxArr(1 To UBound(sArr, 1))
On Error GoTo FuncFail

For n = 1 To UBound(sArr, 1)

CallPxArr(n) = Exp(-1 * dArr(n) * tArr(n)) * sArr(n) * Application.NormSDist(dOne(sArr(n), kArr(n), tArr(n), rArr(n), vArr(n), dArr(n))) - kArr(n) * Exp(-1 * rArr(n) * tArr(n)) * Application.NormSDist(dOne(sArr(n), kArr(n), tArr(n), rArr(n), vArr(n), dArr(n)) - vArr(n) * Sqr(tArr(n)))

Next n

CallOptArray = CallPxArr

FuncFail:
    MsgBox ("Dude..")
  
End Function

Function dOne(spot, strk, time, rate, Vol, Div)

dOne = (Log(spot / strk) + (rate - Div + 0.5 * Vol ^ 2) * time) / (Vol * (Sqr(time)))

End Function
 
Last edited by a moderator:
BF123

Firstly, Welcome to the Chandoo.org Forums

Can you please upload a file with the macro's and some data available
it may well be the data that is upsetting things

Also tell us what specifically is wrong ?
 
Hi Hui, thanks for the quick response. I have uploaded a file. I can't really pinpoint the problem apart from the numbers not coming out. I have replaced my long formula in the For.. next loop with something simple and that worked so perhaps the problem might lie in my formula. I will keep going at it while waiting for someone to point out problems in my code. Thank you.
 

Attachments

  • BFs connundrum.xlsm
    16.7 KB · Views: 10
BF123
Your variable dArr(n) isn't defined
You have declared variable dArr as a variant, not dArr()
etc for the other variables
 
Hui said:
BF123
Your variable dArr(n) isn't defined
You have declared variable dArr as a variant, not dArr()
etc for the other variables
Click to expand...

Thank you once again Hui. If I were to declare dArr and others to be a dynamic array, how do I input the values of my ranges namely spot, strk, time, rate, vol and div, I have entered in the arguments of my CalloptArray function? I have seen other workable codes that declared the variant followed by equating this variant to the range entered in the arguments of their UDF which is what I am doing here:)
 
No you have assigned the variable as an Array and then by default set another variable that looks like the first variable but is different. As you didn't declare it dArr is a variant but isn't an Array

dArr() <> dArr
 
Hi ,

Replace your current statement with the following :

CallPxArr(n) = Exp(-1 * dArr(n, 1) * tArr(n, 1)) * sArr(n, 1) * Application.NormSDist(dOne(sArr(n, 1), kArr(n, 1), tArr(n, 1), rArr(n, 1), vArr(n, 1), dArr(n, 1))) - kArr(n, 1) * Exp(-1 * rArr(n, 1) * tArr(n, 1)) * Application.NormSDist(dOne(sArr(n, 1), kArr(n, 1), tArr(n, 1), rArr(n, 1), vArr(n, 1), dArr(n, 1)) - vArr(n, 1) * Sqr(tArr(n, 1)))

Note that when you wish to assign the value of a range to an array variable , you must declare the variable as a variant ; there is nothing wrong in your declarations.

What was wrong was the way you were accessing the array values ; when you declare a variable as a variant , and assign a range to it , Excel automatically creates a two dimensional array even though your range is a single column ( or single row ) range.

Narayan
 
Thank you Hui and Narayank991.

Narayank991, I understand what you are saying and I have replaced the formula aforementioned. The code now works but is showing the same value for all the cells. I replaced my formula with a simple formula as well and it is also showing the same problem i.e resulting cells have all the same value (value of the first row). I also found it more difficult to debug a function. Any help and advice is greatly appreciated! Thank you for the super clear explanation Narayank991!
 
Hi both, i think i finally worked it out all thanks to your guys.

I replaced my Redim statement as :
ReDim CallPxArr(1 To UBound(sArr, 1), 1 To 1)

after realising that the answer to my function might be defined as a two dimensional array as well! - Thanks Narayank991 for pointing that out!

Back to debugging a function: It seems to me that it doesnt matter where I put "on error goto.." code. It will simply fail if something in the code is not right. Or maybe i am not doing this right?

Is there a 'bible' that all vba users should get, with clear and concise explanations? Books aside, chandoo.org is obviously the place to be;)
 
Hi ,

The On Error Goto .... statement has two variants :

1. On Error Goto 0 , which resets an earlier programmed On Error route to the default of halting on any error

2. On Error GoTo errorhandler routine , which directs the program flow to execute a programmed error routine when ever a trappable error is encountered. Untrappable errors will still cause program execution to halt with a system defined error message.

Check these links for detailed information :

http://support.microsoft.com/kb/141571

http://www.cpearson.com/excel/ErrorHandling.htm

http://www.functionx.com/vbaexcel/Lesson26.htm

http://www.excel-spreadsheet.com/vba/errorhandling.htm

Narayan
 
You must log in or register to reply here.
Back
Top