Sub not executing when called from function!!

[TIGREBLANCO]

Hi Guys

Short story:
Why when converting a Sub into a Function, execution of statements will no longer ocurr, even though vb runs through them?

Long story:
Is there a way to convert a Sub into a Function?. This Sub manipulates (insert, delete, etc) columns and its data, not only variables.

I have a macro that counts single instances of values (ignores cells with duplicate values).
I wanted to make this available as a function to users but replacing "Sub" with "Function" and doing proper modifications wouldn't work, it runs but won't do what Sub used to do, it actually does...nothing!!
I can see line by line executing and despite this my debug screen reports all my variables and references to cells are empty.
I hope this is enough info and that someone knows the reason/solution for this behavior.

Regards
Sal

 

[GlennUK]

Why do want to change a Sub to a Function? Why can't you make the Sub available to the users? The reason for this behaviour is that Functions can't alter Excel objects, whereas Subs can.


Glenn.

 

[TIGREBLANCO]

The only reason I want to make it work as a function (users are using it as a Sub already) is because it's easier for them, value would calculate automatically and passing parameters is more interactive; as a Sub of course, they have to run the macro every time they want the result updated and give the parameters manually.
You know users.
Now, is there a way to call the sub from the function and get the right result?
I already tried but the way I did it it seems to me that the Sub is performing within the scope of the function and therefore it does not handle the objects either, just as if I was doing it with a function alone.
Any input will be greatly appreciated.

 

[Zathras]

A user-defined function can access data from just about anywhere but the result is only returned as the value for the cell that contains the function.

I don't understand the concept of using a function to insert a column (for example). Do you really want to insert a column every time the worksheet recalcs? It makes no sense to me. What is it that you are really trying to do?

 

[TIGREBLANCO]

What I'm really trying to do is to count values from a column (for now, later on I would make it a range if necessary). Now, this values have duplicates, those I don't want to count, just single instances of a value without any duplicates.
For example:
If we have a column with values: 97377, 10345, 10500, 97377, 10600. The result of the function would be 4 since 97377 appears twice in the range, we of course count it as one.
Now, I searched in excel's functions to find one that would do this and couldn't find it, perhaps I didn't look hard enough but I don't think that's the case.
I can think of using arrays to store and sort values provisionaly and get the result from it, but I became very courious about getting this to work this way.
Any thoughts?

 

[xlbo]

You simply can't AFFECT a worksheet with a function and any subs run from a function are limited to the same restriction. Therefore, if any insertion of columns / rows takes place, you can't do it in a function or a sub called from a function
There are at least 3 solutions in this thread:
thread68-552708
the easiest of which to use is probably:
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
where your list is in A1:A100


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TIGREBLANCO]

Well, there it is!!!
Nice solution. Too obvious for me to see it.
Thanks Geoff, Sorry I didn't have the patience to keep looking till May's threads came up.

Regards
Sal.