Problem with user defined function

You are defining and setting the value of "MyRange" inside of the function rather than passing this value/reference through the argument list. The reference for MyRange [Range("Table4")] is also unqualified, so it will refer to the activesheet/workbook (even if that is not the sheet/workbook calling the UDF). Perhaps the 2nd workbook does not have a range named "Table4" -- which will cause an error.

Correcting this will require you to define MyRange more carefully. IMO, you should strongly consider passing MyRange to the function through the argument list, just like you are doing with the other three ranges.

Hi MrShorty!
Thank you very much for the reply!
I think your suggestion will help me solve this problem!

Passing the MyRange through the arguments is impractical, because since this range will always be the same range, there is no point in making the user select this range.
So I prefer to qualify the reference for Range("Table4").
I tried to do it in several different ways, but I couldn't make it work.

A)

Please Login or Register  to view this content.

B)

Please Login or Register  to view this content.

C)

Please Login or Register  to view this content.

D)

Please Login or Register  to view this content.

E)

Please Login or Register  to view this content.

F)

Please Login or Register  to view this content.

G)

Please Login or Register  to view this content.

H)

Please Login or Register  to view this content.

I)

Please Login or Register  to view this content.

None of these work :S

By the way, I would prefer a solution where I would use the codename of the workbooks and worksheets if that is possible.

Note That "Otomatik Portföy" is the name of the workbook,
"wOtomatikPortföy" is the codename of the workbook,
"Liste" is the name of the sheet where the Table4 is located,
"ShtListe" is the codename of that sheet.


Thank you very much for your help!

there is no point in making the user select this range.

Unless you'd like the UDF to recalculate automatically when that range is changed.

Originally Posted by shg

Unless you'd like the UDF to recalculate automatically when that range is changed.

Well, that range is never going to change

Well, that range is never going to change

Maybe not; it's still atrocious coding practice.

A range is an object variable, and object variables require Set:

Please Login or Register  to view this content.

MyRange is not a range, it is an array.
You can see this, since it is declared as variant, not an array.
And you can't use "set" on arrays.

Please note that I have to keep it as an array.

If range values will not change why do you use worksheetfunction.Count instead of constant value?

The range is a table.
So the table can grow, so that's why the number of elements in it can change.
However, since I'm referring to the table with it's name, I will never need to update this reference.
Why are you questioning me instead of trying to help me?

I think you'll find that they are trying to help you. You might want to be a little more appreciative.

For a UDF you should pass all ranges as arguments. If you don't, you have to make your function volatile otherwise when your referenced table expands, your functions won't recalculate automatically and properly qualify the ranges, as mentioned. In this case you should be able to use:

Please Login or Register  to view this content.

but it is still better practice to pass the range as a parameter.

Hi romperstomper,

Thank you for your suggestion!
I've tried this code:

Please Login or Register  to view this content.

But it doesn't solve my problem.
When I open a new workbook, and return to the original workbook, I still get #value erros on the original workbook.
I believe I need to refer to the original workbook, but I couldn't find a way to successfully do it.

Originally Posted by romperstomper

For a UDF you should pass all ranges as arguments. If you don't, you have to make your function volatile otherwise when your referenced table expands, your functions won't recalculate automatically and properly qualify the ranges, as mentioned.

Thanks for the explanation about why everyone insists on passing this range as an argument.
I tried to expand my table, and checked whether the function automatically recalculates the values without me doing anything, and apparently it does automatically recalculate. So it looks like there is no need to pass this range as an argument in this case .

Did you properly qualify all the ranges in your code?

Please Login or Register  to view this content.

is workbook-specific. It can only refer to the sheet codenamed ShtListe in the workbook containing the code.

Your function should not automatically recalculate unless you also changed one or more of the input values. It seems to be pointless to continue that discussion though.

In response to PM.

In many ways, I am inclined to agree with shg -- the easiest and, IMO, best solution is to pass the "Table4" range through the argument list (I also noticed that you also have an unqualified reference to "Table3..." down in the sumifs() statement). Converting a converting a range passed to the argument to a variant/array should be essentially the same as your Myrange=... statement is now -- but you get better definition of what the range argument refers to.

It can be unhelpful to state that "none of these statements worked" like in post #3. Exactly what didn't work? Did you get a compiler error? a runtime error? it ran but returned the wrong value? Syntactically, many of those statements look correct to me, so it may be important to know exactly what you mean by "didn't work".

One thing I would probably suggest: Put a breakpoint near the beginning of the function, so you can debug this from break mode where you will have access to VBA's array of debugging tools http://office.microsoft.com/en-us/su...001042819.aspx . This will allow you to explore different variables and constants using the immediate window, watch window, and locals window to see what each contains. When a variable contains something unexpected, you can investigate when and eventually why the variable is assigned an incorrect value.

MrShorty,
Thank you very very much!
As you have discovered, the other unqualifed ranges were also causing the problem.
I solved the problem by qualifying all of the ranges properly, but I also had to change this code:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

I thought that arrays should also be declered by using the parantheses after the array name, but I get errors if I do use the paranthesis, but the code works well if I omit the paranthesis.

For example if I use

Please Login or Register  to view this content.

I get the error "type mismatch".
If I put the array lengths in the paranthesis, and write this:

Please Login or Register  to view this content.

I get the error "Can't assign to array"

Anyway, so deleting the paranthesis after the "MyRange" array and qualifying all the ranges properly solves my problem.

So here is the final code:

Please Login or Register  to view this content.

I found out that after removing the paranthesis, the methods to qualify the range described in A), B), C), D), G) and H) all work!


Again, Thank You very Much!

Always specify the .Value - never leave it implicit.

I found out that after removing the paranthesis, the methods to qualify the range described in A), B), C), D), G) and H) all work!

you have just entered that part of "The Twilight Zone" where the difference between "An array of Variants" and "A Variant containing an array" becomes important.