MrExcel Publishing
Your One Stop for Excel Tips & Solutions

functions breaking up/stopping VBA code

Posted by Luke on November 10, 2001 3:29 PM

: Hi,

Post this one again since it really bothers me and the program I am working on and as a question it is now far down on the list;

Posted by Juan Pablo on November 10, 2001 6:29 PM

If i'm right, UDF just stop if they encounter an error, that is, unlike subs, they don't crash and show the debug-finish dialog box. I think the probem is in line 4 of your UDF

The "&" is used for concatenate strings (As is in Excel), i.e., if you have:

a = 1 & " is my number"
MsgBox a

You'll get "1 is my number"

I think you're trying to do is:

If fvalue Like "*" AND dcode(series, 1) Like "*" Then totalen = totalen + 1

Are you testing fvalue for anything ? you could use:

fvalue <> ""

See if that works.

Juan Pablo

Posted by Luke on November 11, 2001 2:22 PM

Hi, Thanks for the reply. In your response, I would seem to be checking for a "*" and compare both dcode and fvalue the the asterisk which is not what I want. In the function I use the "*" as a wildcard. In the function I test each cell in a range to see of it contains the code in dcode. ie
a1 = 11,4
b1 = 12
c1 = x
d1 = (11)*
dcode(1,1) = 11
dcode(1,2) = 12
I want to know how many 11's there are and how many 12's, nomatter what is before or after the 11 or 12. I then check each cell using a wildcard, the code (dcode) and another wildcard and use the & to put them together. Like I said, it works fine when I just change a cell in the range, or activate a worksheet it calculates all the cells, but when I insert an entire row it recalculates all the cells the function is called in (140 in all) and then does not return to the old code.


Posted by Ivan F Moala on November 11, 2001 11:37 PM

In your VBA routine that inserts the row
try turning Calculation OFF
then turn it back ON
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic