How to have many arguments in a module without it breaking

Hi everyone,
Hope you're having a lovely day. I was hoping if you could point me into the right direction regarding this code. It works in single module parts but not as a whole in one three part module. I would love to be able to make a continuous module without breaking it up.
Input:
Returns: [ 0.25 , 0.10 , -0.25 ]
Probability: [ 0.5 , 0.3 , 0.2 ]
Result: 0.357

I just get a " 0,0 " result from this function so it's breaking somewhere but I just can't figure out where.

Please Login or Register  to view this content.

I didn't fully debug it, but I will make a couple of comments and leave some debugging to you. Remember that only half of coding is writing the procedure. Debugging is the other half.

1) As I explain in my little primer on UDFs (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I do with a UDF is add at least one stop statement or breakpoint to bring up debug mode. Somewhere at the top of the procedure I would add a stop statement.

2) What is the purpose of the On Error Resume Next statements? What error(s) are you expecting and why is "ignore error and continue" the best way to trap and handle those errors? I think sometimes we get into a habit of putting "On Error Resume Next" into our code rather than really thinking through the error trapping and handling. In your procedure, there appears to be at least one fatal error that ignoring and continuing is definitely the wrong approach. If you have included "On Error Resume Next" out of habit rather than real thought about the expected errors, then I would remove all "On Error Resume Next" statements and let VBA raise the errors or fail or whatever it is going to do or not do so you know where the errors are.

3) As an example of an error that I see, in part 2, you have a "Returns - ExpectedReturn" operation. Returns is a Range, and ExpectedReturn is also a Range (that has never been assigned anything). I could be wrong, but I don't think VBA knows what "Range-Range" or "Range-Nothing" means. With On Error Resume Next active, VBA just ignores and continues, so nothing gets assigned to Arg2(). When you get to part 3 where you want to do something with the results in Arg2(), there is nothing there, so nothing further happens. Again, any errors are just ignored, so you get to the end without really doing anything and no errors are raised.

To recap -- I would add Stop statements for debugging. Then I would suggest you think through your error trapping and handling more carefully, because "ignore and continue" doesn't seem like the correct error handling strategy for the errors that are present. As I said, I didn't fully debug, because I didn't want to fully reverse engineer what you are wanting to do and figure out the corrections. I'm hopeful that, by not ignoring errors and entering debug mode, you will be able to figure out what needs to be done differently.