Dear friends
I am quite new to VBA and I am hoping for some help.
I the code below I notice that the variables are not initialised with the values specified? Could anyone tell my what that is?
Many thanks
Please Login or Register to view this content.
Last edited by Leith Ross; 06-25-2013 at 12:16 PM. Reason: Added Code Tags
nikolaygeorgiev,
Welcome to the forum!
In the future, please wrap your code in code tags. See link in my sig for how.
As for your question, you have mu dimmed as an Integer, and then you try to assign a decimal value to it. Because mu is an Integer, it converts the decimal value to the closest integer rounded down, which is 0.
So S(i) * 0 * 1 is always 0.
Then you declare Sigma as an integer but never assign a value to it, so by default Sigma will have a value of 0.
So Application.NormSDist(Rnd) * Sqr(t) * 0 is always 0
Which means your equation is always 0 + 0 = 0.
To correct those issues:
Dim mu as Double
Dim S(10000) as Double
Assign a value to Sigma
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
~tigeravatar
Many thanks for your help! In the future I will do as you suggested! Many thanks
Hi, tigeravatar,
I wonder what the Sqrt of 1 (aka t) may look like?Okay, I´m off by now
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
HaHoBe,
haha that is not the point here. I was just trying to follow up on an error. I am learning new things. Don't have a clue about VBA
Thanks all
Hey nikolaygeorgiev,
I'm not a moderator or anything important, but after someone solves your problem, you're supposed to go to the top of the thread, find "Thread Tools", click it, and then click "Mark this thread as solved" so that other people dont come here and find out that you've already been helped. Just letting you know!
Thank you! I will do that. I just registered and don't know the rules as yet. Thank you
Hi guys
I am struggling with this VBA. The code I am running doesn't seem to work properly and is giving me the following error message "the value used in the formula is of the wrong data type"
I will never learn this VBA lolPlease Login or Register to view this content.
Many thanks fellas
Last edited by nikolaygeorgiev; 06-27-2013 at 04:37 AM.
Are you calling this formula from the worksheet, or from elsewhere in your code?
What are the values you are providing to the formula for Stock, mu, time, and Sigma?
Thanks tigeravatar
Actually I am calling this function from the Worksheet as =MCS(100, 0.10, 10, 0.20)
I want to see if the function is working so I decided to popule the array after executing the function
THank you
alright, you can't call a function from the worksheet and have it output the result to different cells. It has to output its result to the cell that called the function, just like any other worksheet function would. What are you trying to accomplish?
Well I am trying to learn VBA and trying different things. The function I tried to model I wanted to see its output in the worksheet. Perhaps I should use a sub instead and give it the parameters
thank you
Slight alterations to your function:
Please Login or Register to view this content.
And then you can call it with this sub which would output it column B:
Please Login or Register to view this content.
The reason I changed S to be dimmed as S(795) is because you would get an overflow error when i = 795: at that point the calculation of S(i) will exceed the largest number that Excel is capable of handling.
So I changed S to be dimmed as S(795) and adjusted the For Loop to end at 794 and it ran successfully and output the results to column B as expected
Thank ~tigeravatar
I will try it now. Much appreciated!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks