Call a Public Function in another function

[kamelkid2]

i have this function i found/adapted on the internet:

Please Login or Register  to view this content.

As a user function, this works perfectly as needed (by way of helper columns). I need to take this into another user function (to eliminate helper columns) and loop it through 50 sets of values. I keep returning #value errors when attempting to run my function as it stands. Here is my userfunction:

Please Login or Register  to view this content.

i chose cx490, cy490, cx500, cy500 as the input variables as they return a value in my worksheet, but as i said, only get #value errors when testing this DominantWavelength function. Can anybody see what error I am making? the only functions/subs i've ever been able to successfully call within another function/sub ive adapted from existing code so I don't fully have the fundamental understanding yet.

thank you for your time

[MrShorty]

Since you have hardcoded references to a range of values, it would be useful if you would upload the entire workbook, since it will be a bit of work to recreate or adapt your code to one of our workbooks.

1st step I almost always undertake in debugging something like this is to set a breakpoint early in the function so I can step through the function one line at a time. This will allow me to identify where in the function code the error is occurring, verify the values of any variables, and see if that helps me see what is going wrong.

Are you reasonably certain the error is caused by the function call to intersectcomplex()? VALUE error like that is a kind of generic "something went wrong" error, and I don't see anything in your code or description that clearly points to a fault in the function call. Another part of the value of stepping through the function to to make sure the error occurs where I think it occurs.

[kamelkid2]

Thank you for your reply - I will upload a sample book tonight as there are data protection measures in place preventing me from uploading an excel book where I work. I am confident there is an error in the sense I have not setup the modules in the correct way / declaration of functions.

within my worksheet, if i enter IntersectComplex(blah blah blah) it will return the correct value and if i enter DominantWavelength(blah blah blah) in the worksheet i am able to calculate and display cx490, cy490, cx500, and cy500.

it is when i try to bring these cx variables (to be used as x1, x2, y1, y2) in the IntersectComplex function that i get stuck.


as it resides now, IntersectComplex is located in ic.jpg the 'misc' module whereas the dominantwavelength function is located in module 'dominant'

could this be a cause of this? honestly i've been unsuccessful at even the most basic of calling functions and everything i find in google is over my head. do you know of a very basic calling procedure? something like:

Please Login or Register  to view this content.

that i could work through and understand how these work with each other?

thank you again

[MrShorty]

If I take these two simple procedures, put them each in their own module within a project, I can call add from addn just about as is. (add needs a comma between arguments, and I needed to dim x1 and x2 in addn as integers). I think your problem is probably not as simple as, "they are not in the same module" since I don't see any naming conflicts between modules/function names (unless there is another name conflict elsewhere in the project).

I still think you may find something useful by setting a breakpoint and stepping through the functions to see which statement is causing the error.

[kamelkid2]

How do I set a breakpoint?

[shg]

See http://www.cpearson.com/excel/DebuggingVBA.aspx

[kamelkid2]

can i step into a userfunction? i am not getting any interaction when i follow the link posted above.

anyway i have come across something interesting that may be the cause of this:

Please Login or Register  to view this content.

This returns 1 in a cell using this function, however:

Please Login or Register  to view this content.

returns a #Value! error in a cell using this function. How am i declaring the variable LineB as a range incorrectly?

[Hawkeye16]

Put a breakpoint on the LineB = line and you may be able to tell what is going wrong.

I would think your first one would error out and your second one would not due to the Option Explicit you used in your original post.

[kamelkid2]

i created a new sub that passed these variables into the function so i could step into it (wouldn't allow me any other way)

received a "run time error 91 - object variable or with block not set" error on the line:

Please Login or Register  to view this content.

I've tried:

Please Login or Register  to view this content.

any ideas on this one? i feel like im close on this one but haven't been able to figure it out. thank you all for teaching me how to debug more properly though!

[Hawkeye16]

Stupid. Haha, I completely missed it.

Use

Please Login or Register  to view this content.

You need to set the reference to a range like you would with a worksheet or workbook variable.

[kamelkid2]

thank you so much!

i felt like i was taking crazy pills not being able to define this variable. my code up to this point is working properly now - thanks again