UDF ...VBA Formula built ...please help

  • Thread starter Thread starter ytayta555
  • Start date Start date
Y

ytayta555

A good day to you all

I forever was searching for maxim speed in my database ;
I never hear before by User Defined Function/Formula , UDF ;
Now , my extreme important goal is to built in column BD ,
Range from row 91 to 65536 my UDF , but I dont know nothing
about VBA Function/ Formula built ! ...

in Range("BD91") , to explain what my worksheet formula do, I show
you this formula :

=AND(COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R15)<=1;COUNT(S1;S2;S3;S4;S5;S6;S7;S8;S9;S10;S11;S12;S13;S14;S15)<=1;COUNT(T1;T2;T3;T4;T5;T6;T7;T8;T9;T10;T11;T12;T13;T14;T15)<=1;COUNT(U1;U2;U3;U4;U5;U6;U7;U8;U9;U10;U11;U12;U13;U14;U15)<=1;COUNT(V1;V2;V3;V4;V5;V6;V7;V8;V9;V10;V11;V12;V13;V14;V15)<=1;COUNT(W1;W2;W3;W4;W5;W6;W7;W8;W9;W10;W11;W12;W13;W14;W15)<=1;COUNT(X1;X2;X3;X4;X5;X6;X7;X8;X9;X10;X11;X12;X13;X14;X15)<=1;COUNT(Y1;Y2;Y3;Y4;Y5;Y6;Y7;Y8;Y9;Y10;Y11;Y12;Y13;Y14;Y15)<=1;COUNT(Z1;Z2;Z3;Z4;Z5;Z6;Z7;Z8;Z9;Z10;Z11;Z12;Z13;Z14;Z15)<=1;COUNT(AA1;AA2;AA3;AA4;AA5;AA6;AA7;AA8;AA9;AA10;AA11;AA12;AA13;AA14;AA15)<=1;COUNT(AB1;AB2;AB3;AB4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB14;AB15)<=1;COUNT(AC1;AC2;AC3;AC4;AC5;AC6;AC7;AC8;AC9;AC10;AC11;AC12;AC13;AC14;AC15)<=1;COUNT(AD1;AD2;AD3;AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12;AD13;AD14;AD15)<=1;COUNT(AE1;AE2;AE3;AE4;AE5;AE6;AE7;AE8;AE9;AE10;AE11;AE12;AE13;AE14;AE15)<=1;COUNT(AF1;AF2;AF3;AF4;AF5;AF6;AF7;AF8;AF9;AF10;AF11;AF12;AF13;AF14;AF15)<=1;COUNT(AG1;AG2;AG3;AG4;AG5;AG6;AG7;AG8;AG9;AG10;AG11;AG12;AG13;AG14;AG15)<=1;COUNT(AH1;AH2;AH3;AH4;AH5;AH6;AH7;AH8;AH9;AH10;AH11;AH12;AH13;AH14;AH15)<=1;COUNT(AI1;AI2;AI3;AI4;AI5;AI6;AI7;AI8;AI9;AI10;AI11;AI12;AI13;AI14;AI15)<=1;COUNT(AJ1;AJ2;AJ3;AJ4;AJ5;AJ6;AJ7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13;AJ14;AJ15)<=1;COUNT(AK1;AK2;AK3;AK4;AK5;AK6;AK7;AK8;AK9;AK10;AK11;AK12;AK13;AK14;AK15)<=1;COUNT(AL1;AL2;AL3;AL4;AL5;AL6;AL7;AL8;AL9;AL10;AL11;AL12;AL13;AL14;AL15)<=1;COUNT(AM1;AM2;AM3;AM4;AM5;AM6;AM7;AM8;AM9;AM10;AM11;AM12;AM13;AM14;AM15)<=1;COUNT(AN1;AN2;AN3;AN4;AN5;AN6;AN7;AN8;AN9;AN10;AN11;AN12;AN13;AN14;AN15)<=1;COUNT(AO1;AO2;AO3;AO4;AO5;AO6;AO7;AO8;AO9;AO10;AO11;AO12;AO13;AO14;AO15)<=1;COUNT(AP1;AP2;AP3;AP4;AP5;AP6;AP7;AP8;AP9;AP10;AP11;AP12;AP13;AP14;AP15)<=1;COUNT(AQ1;AQ2;AQ3;AQ4;AQ5;AQ6;AQ7;AQ8;AQ9;AQ10;AQ11;AQ12;AQ13;AQ14;AQ15)<=1;COUNT(AR1;AR2;AR3;AR4;AR5;AR6;AR7;AR8;AR9;AR10;AR11;AR12;AR13;AR14;AR15)<=1;COUNT(AS1;AS2;AS3;AS4;AS5;AS6;AS7;AS8;AS9;AS10;AS11;AS12;AS13;AS14;AS15)<=1;COUNT(AT1;AT2;AT3;AT4;AT5;AT6;AT7;AT8;AT9;AT10;AT11;AT12;AT13;AT14;AT15)<=1;COUNT(AU1;AU2;AU3;AU4;AU5;AU6;AU7;AU8;AU9;AU10;AU11;AU12;AU13;AU14;AU15)<=1;COUNT(AV1;AV2;AV3;AV4;AV5;AV6;AV7;AV8;AV9;AV10;AV11;AV12;AV13;AV14;AV15)<=1;COUNT(AW1;AW2;AW3;AW4;AW5;AW6;AW7;AW8;AW9;AW10;AW11;AW12;AW13;AW14;AW15)<=1;COUNT(AX1;AX2;AX3;AX4;AX5;AX6;AX7;AX8;AX9;AX10;AX11;AX12;AX13;AX14;AX15)<=1;COUNT(AY1;AY2;AY3;AY4;AY5;AY6;AY7;AY8;AY9;AY10;AY11;AY12;AY13;AY14;AY15)<=1;COUNT(AZ1;AZ2;AZ3;AZ4;AZ5;AZ6;AZ7;AZ8;AZ9;AZ10;AZ11;AZ12;AZ13;AZ14;AZ15)<=1;COUNT(BA1;BA2;BA3;BA4;BA5;BA6;BA7;BA8;BA9;BA10;BA11;BA12;BA13;BA14;BA15)<=1;COUNT(BB1;BB2;BB3;BB4;BB5;BB6;BB7;BB8;BB9;BB10;BB11;BB12;BB13;BB14;BB15)<=1)

An perfect equivalent for formula above is the next array formula
which I use actually :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)
in my locale formula sintax , and in US formula sintax :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0,ISNUMBER(R1:BB45)*{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})<=1)

in Range("BD92") , all Count functions in formula shall have
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R16) [R16 not
R15 like in BD91!] ;my perfect equivalent array formula
shall look so :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)

in Range("BD93") , all Count functions in formula shall have
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R17) [R16 not
R15 like in BD91!] ;my perfect equivalent array formula
shall look so :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|0|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)

in Range("BD121") = When references from Count functions are
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R45) , then in
next cell of range(Range("BD122") references shall look so :
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R15;R16) , so the
moral of this tale is that references are in combinatoric order , 45
numbers taken 15 times

Please very much to help me to built my array formula in VBA , this
UDF is very very important for me . If I have first 2 or 3 examples
for my array formula from BD91 and BD92 ,
I think I'll be able to understand how I must work for the next
others .I want to built in VBA this UDF , but with an array :( see
here please http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/
to Doug Jenkins comment ) .


[Maybe the array formula above is too complicate ; for an easyer
example , I believe if I have the below easyer example Formulas
write in VBA I'll be able to understand how I must work for the next
more complicate others :
=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C3)<=1;COUNT(D1;D2;D3)<=1;COUNT(E1;E2;E3)<=1;COUNT(F1;F2;F3)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|1|0|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C4)<=1;COUNT(D1;D2;D4)<=1;COUNT(E1;E2;E4)<=1;COUNT(F1;F2;F4)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|1|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C5)<=1;COUNT(D1;D2;D5)<=1;COUNT(E1;E2;E5)<=1;COUNT(F1;F2;F5)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|0|
1})<=1) ]
Thank you all so much .
 
Try calling function below with
=CountUDF(R1:BB15)


Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = WorksheetFunction.Count(Target.Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
'Exit Function
End If
Next RowCount
End Function

ytayta555 said:
A good day to you all

I forever was searching for maxim speed in my database ;
I never hear before by User Defined Function/Formula , UDF ;
Now , my extreme important goal is to built in column BD ,
Range from row 91 to 65536 my UDF , but I dont know nothing
about VBA Function/ Formula built ! ...

in Range("BD91") , to explain what my worksheet formula do, I show
you this formula :

=AND(COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R15)<=1;COUNT(S1;S2;S3;S4;S5;S6;S7;S8;S9;S10;S11;S12;S13;S14;S15)<=1;COUNT(T1;T2;T3;T4;T5;T6;T7;T8;T9;T10;T11;T12;T13;T14;T15)<=1;COUNT(U1;U2;U3;U4;U5;U6;U7;U8;U9;U10;U11;U12;U13;U14;U15)<=1;COUNT(V1;V2;V3;V4;V5;V6;V7;V8;V9;V10;V11;V12;V13;V14;V15)<=1;COUNT(W1;W2;W3;W4;W5;W6;W7;W8;W9;W10;W11;W12;W13;W14;W15)<=1;COUNT(X1;X2;X3;X4;X5;X6;X7;X8;X9;X10;X11;X12;X13;X14;X15)<=1;COUNT(Y1;Y2;Y3;Y4;Y5;Y6;Y7;Y8;Y9;Y10;Y11;Y12;Y13;Y14;Y15)<=1;COUNT(Z1;Z2;Z3;Z4;Z5;Z6;Z7;Z8;Z9;Z10;Z11;Z12;Z13;Z14;Z15)<=1;COUNT(AA1;AA2;AA3;AA4;AA5;AA6;AA7;AA8;AA9;AA10;AA11;AA12;AA13;AA14;AA15)<=1;COUNT(AB1;AB2;AB3;AB4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB14;AB15)<=1;COUNT(AC1;AC2;AC3;AC4;AC5;AC6;AC7;AC8;AC9;AC10;AC11;AC12;AC13;AC14;AC15)<=1;COUNT(AD1;AD2;AD3;AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12;AD13;AD14;AD15)<=1;COUNT(AE1;AE2;AE3;AE4;AE5;AE6;AE7;AE8;AE9;AE10;AE11;AE12;AE13;AE14;AE15)<=1;COUNT(AF1;AF2;AF3;AF4;AF5;AF6;AF7;AF8;AF9;AF10;AF11;AF12;AF13;AF14;AF15)<=1;COUNT(AG1;AG2;AG3;AG4;AG5;AG6;AG7;AG8;AG9;AG10;AG11;AG12;AG13;AG14;AG15)<=1;COUNT(AH1;AH2;AH3;AH4;AH5;AH6;AH7;AH8;AH9;AH10;AH11;AH12;AH13;AH14;AH15)<=1;COUNT(AI1;AI2;AI3;AI4;AI5;AI6;AI7;AI8;AI9;AI10;AI11;AI12;AI13;AI14;AI15)<=1;COUNT(AJ1;AJ2;AJ3;AJ4;AJ5;AJ6;AJ7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13;AJ14;AJ15)<=1;COUNT(AK1;AK2;AK3;AK4;AK5;AK6;AK7;AK8;AK9;AK10;AK11;AK12;AK13;AK14;AK15)<=1;COUNT(AL1;AL2;AL3;AL4;AL5;AL6;AL7;AL8;AL9;AL10;AL11;AL12;AL13;AL14;AL15)<=1;COUNT(AM1;AM2;AM3;AM4;AM5;AM6;AM7;AM8;AM9;AM10;AM11;AM12;AM13;AM14;AM15)<=1;COUNT(AN1;AN2;AN3;AN4;AN5;AN6;AN7;AN8;AN9;AN10;AN11;AN12;AN13;AN14;AN15)<=1;COUNT(AO1;AO2;AO3;AO4;AO5;AO6;AO7;AO8;AO9;AO10;AO11;AO12;AO13;AO14;AO15)<=1;COUNT(AP1;AP2;AP3;AP4;AP5;AP6;AP7;AP8;AP9;AP10;AP11;AP12;AP13;AP14;AP15)<=1;COUNT(AQ1;AQ2;AQ3;AQ4;AQ5;AQ6;AQ7;AQ8;AQ9;AQ10;AQ11;AQ12;AQ13;AQ14;AQ15)<=1;COUNT(AR1;AR2;AR3;AR4;AR5;AR6;AR7;AR8;AR9;AR10;AR11;AR12;AR13;AR14;AR15)<=1;COUNT(AS1;AS2;AS3;AS4;AS5;AS6;AS7;AS8;AS9;AS10;AS11;AS12;AS13;AS14;AS15)<=1;COUNT(AT1;AT2;AT3;AT4;AT5;AT6;AT7;AT8;AT9;AT10;AT11;AT12;AT13;AT14;AT15)<=1;COUNT(AU1;AU2;AU3;AU4;AU5;AU6;AU7;AU8;AU9;AU10;AU11;AU12;AU13;AU14;AU15)<=1;COUNT(AV1;AV2;AV3;AV4;AV5;AV6;AV7;AV8;AV9;AV10;AV11;AV12;AV13;AV14;AV15)<=1;COUNT(AW1;AW2;AW3;AW4;AW5;AW6;AW7;AW8;AW9;AW10;AW11;AW12;AW13;AW14;AW15)<=1;COUNT(AX1;AX2;AX3;AX4;AX5;AX6;AX7;AX8;AX9;AX10;AX11;AX12;AX13;AX14;AX15)<=1;COUNT(AY1;AY2;AY3;AY4;AY5;AY6;AY7;AY8;AY9;AY10;AY11;AY12;AY13;AY14;AY15)<=1;COUNT(AZ1;AZ2;AZ3;AZ4;AZ5;AZ6;AZ7;AZ8;AZ9;AZ10;AZ11;AZ12;AZ13;AZ14;AZ15)<=1;COUNT(BA1;BA2;BA3;BA4;BA5;BA6;BA7;BA8;BA9;BA10;BA11;BA12;BA13;BA14;BA15)<=1;COUNT(BB1;BB2;BB3;BB4;BB5;BB6;BB7;BB8;BB9;BB10;BB11;BB12;BB13;BB14;BB15)<=1)

An perfect equivalent for formula above is the next array formula
which I use actually :

=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)
in my locale formula sintax , and in US formula sintax :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0,ISNUMBER(R1:BB45)*{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})<=1)

in Range("BD92") , all Count functions in formula shall have
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R16) [R16 not
R15 like in BD91!] ;my perfect equivalent array formula
shall look so :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)

in Range("BD93") , all Count functions in formula shall have
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R17) [R16 not
R15 like in BD91!] ;my perfect equivalent array formula
shall look so :
=AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:BB45)*{1|1|1|1|1|1|1|
1|1|1|1|1|1|1|0|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|
0|0|0})<=1)

in Range("BD121") = When references from Count functions are
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R14;R45) , then in
next cell of range(Range("BD122") references shall look so :
COUNT(R1;R2;R3;R4;R5;R6;R7;R8;R9;R10;R11;R12;R13;R15;R16) , so the
moral of this tale is that references are in combinatoric order , 45
numbers taken 15 times

Please very much to help me to built my array formula in VBA , this
UDF is very very important for me . If I have first 2 or 3 examples
for my array formula from BD91 and BD92 ,
I think I'll be able to understand how I must work for the next
others .I want to built in VBA this UDF , but with an array :( see
here please http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/
to Doug Jenkins comment ) .


[Maybe the array formula above is too complicate ; for an easyer
example , I believe if I have the below easyer example Formulas
write in VBA I'll be able to understand how I must work for the next
more complicate others :
=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C3)<=1;COUNT(D1;D2;D3)<=1;COUNT(E1;E2;E3)<=1;COUNT(F1;F2;F3)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|1|0|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C4)<=1;COUNT(D1;D2;D4)<=1;COUNT(E1;E2;E4)<=1;COUNT(F1;F2;F4)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|1|0})<=1)

=AND(COUNT(B1;B2;B3)<=1;COUNT(C1;C2;C5)<=1;COUNT(D1;D2;D5)<=1;COUNT(E1;E2;E5)<=1;COUNT(F1;F2;F5)<=1)
=AND(MMULT(TRANSPOSE(ROW(B1:F5))^0;ISNUMBER(B1:F5)*{1|1|0|0|
1})<=1) ]
Thank you all so much .
Click to expand...
 
Here is another way of doing it without worksheetfunction. I would compare
the two results

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = 0
ForColcount = 1 to Target.Columns.Count
if IsNumeric(Target.Cells(RowCount,ColCount)) then
MyCount = MyCount + 1
If MyCount > 1 Then
CountUDF = False
'Exit Function
end if
End If
Next ColCount
Next RowCount
End Function



Not sure which on the average will work faster.
 
Please very much to provide me and VBA Functions and for BD93 ,
BD93 , BD121
and BD122 , to understand how I must write the others (from BD91 to
BD65536 )
 
You don't change the function, just change the call
=CountUDF(R1:BB15)
Click to expand...

I understand that in BD92 I must call =CountUDF(R1:BB14;R16:BB16),
in BD93 =CountUDF(R1:BB14;R17:BB17)
BD121 =CountUDF(R1:BB14;R45:BB45)
BD122 =CountUDF(R1:BB13;R15:BB16)
BD123 =CountUDF(R1:BB13;R15:BB15;R17:BB17)

Am I right ??
But , how I said I want to built them
without use worksheetfunction ;maybe , if it is posible ,
using array ...excuse me I ask more things , but I need them .
Thanks a lot for your time
 
I have found that worksheet functions are usually very effient unless there
is a less complicated method using a UDF. If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods. It also doesn't use much overhead to call a worksheet
function.

You could use and auxilary column BC and add this formula to row 1 and copy
down the worksheet.
=Count(R1:BB1)<=1

Then at the bottom of the row use Countif to determine if you have any non 1
values
=and(BC1:BC17)
 
I have found that worksheet functions are usually very effient unless there
is a less complicated method using a UDF.  If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods.  It also doesn't use much overhead to call a worksheet
function.
Click to expand...


Indeed , UDF have problem with recalculation , the problem of volatile
formula ,
can occure wrong results ,....; I'll try to see the best method , but
in time ;
maybe you can show me how is to use a macro ...
( If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods. It also doesn't use much overhead to call a worksheet
function. )
Click to expand...
... to understand what is in this ideea .

Thanks so much you initiate me in this new method for me !!

Maybe , another last method and the best is to use , according with
this .....[ Another thing to keep in mind is that a UDF will more
often than
not be less efficient than a deeply nested group of standard
Functions.
This is because Excel's built in Functions are written in the
extremely
fast language, C++. VBA, unfortunately, is a very slow programming
language.]
...according with this , to use C++ for my database .Now , my database
is
in Excel application and VBA programming language , but it isn't
bad ! ...

Many thanks again mr. Joel , any ideea and sugestion here is very
apreciated
and usefull for me
 
UDF only have calculation issues if you are referencing worksheet cells
inside a UDF that are not passed as a parameter. the fix is easy by passing
all the worksheet cells through the calling parameters.

ytayta555 said:
I have found that worksheet functions are usually very effient unless there
is a less complicated method using a UDF. If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods. It also doesn't use much overhead to call a worksheet
function.
Click to expand...


Indeed , UDF have problem with recalculation , the problem of volatile
formula ,
can occure wrong results ,....; I'll try to see the best method , but
in time ;
maybe you can show me how is to use a macro ...
( If you have a UDF it is more
efficient to use macro code rather than call a worksheet function when there
ae equivalent methods. It also doesn't use much overhead to call a worksheet
function. )
Click to expand...
... to understand what is in this ideea .

Thanks so much you initiate me in this new method for me !!

Maybe , another last method and the best is to use , according with
this .....[ Another thing to keep in mind is that a UDF will more
often than
not be less efficient than a deeply nested group of standard
Functions.
This is because Excel's built in Functions are written in the
extremely
fast language, C++. VBA, unfortunately, is a very slow programming
language.]
...according with this , to use C++ for my database .Now , my database
is
in Excel application and VBA programming language , but it isn't
bad ! ...

Many thanks again mr. Joel , any ideea and sugestion here is very
apreciated
and usefull for me
Click to expand...
 
HI EVERYBODY AGAIN !

Last days I worked with this UDF .This UDF with worksheetfunction
don't
work : Try calling function below with
=CountUDF(R1:BB15)

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = WorksheetFunction.Count(Target.Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
'Exit Function
End If
Next RowCount
End Function

It work perfect in the next variant :

Function CountUDF(Target As Range)
Application.Volatile
CountUDF = True
For ColCount = 1 To Target.Columns.Count
MyCount = WorksheetFunction.Count(Target.Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
'Exit Function
End If
Next ColCount
End Function (I changed Row with Column and work perfect)

With my array function , an autofill from BD91 to BD65536 take 65
seconds ;
with this UDF with worksheetfunction inside , take 27 seconds (it's
great!)

The example of UDF without worksheetfunction inside , don't work !
Please
very much everybody to provide me the changes in it to get it work ,
because
work more more fast ( see
http://newtonexcelbach.wordpress.com/2008/05/24/worksheetfunction-vs-...
)

(In a perfect world I'd want so much to be with an array , according
with
the ideas and experiment of here :( see please
http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/
to Doug Jenkins comment ) .

Please everybody to help me , I'm so nearby to get what I need and
want so much !
Thanks very much to all
 
First, you should put back the "Exit function" to speed up the code. Once
you find one row that is greater than 1 you don't need to test all the other
rows.

Second, it doesn't make sence that the columns work and the Rows don't work.
It must mean that you have two items in one Row but not two items in one
column.

Third, there is an equivalent Count function in VBA to the worksheet
function. It will run a little quicker, but probably not noticable.

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
MyCount = Target.Rows(RowCount).Count
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount
End Function
 
First, you should put back the "Exit function" to speed up the code.  Once
you find one row that is greater than 1 you don't need to test all the other
rows.
Click to expand...

Thanks so much for quick reply mr. Joel !
It's amasing : now , for an autofill from BD91
to BD65536 take 6 seconds ! It's the dream that I was looking
for !...Here can see
everybody the power of an UDF ...
Second, it doesn't make sence that the columns work and the Rows don't work.
 It must mean that you have two items in one Row but not two items in one
column.
Click to expand...

Indeed , in one row I can have don't matter how many items , but if in
a column
is more then 1 item , that't what I must find .(My apologise that I
wasn't able to explain
clear , but that's why I posted my worksheet formulas
AND(COUNT(....) , and
AND(MMULT(TR...)) )
Third, there is an equivalent Count function in VBA to the worksheet
function.  It will run a little quicker, but probably not noticable.

Function CountUDF(Target As Range)
CountUDF = True
For RowCount = 1 To Target.Rows.Count
   MyCount = Target.Rows(RowCount).Count
   If MyCount > 1 Then
      CountUDF = False
      Exit Function
   End If
Next RowCount
End Function
Click to expand...

VBA still don't work , but I'm very pleasent for the actual result , 6
seconds
One only problem is now .I have milions of function with references in
combinatoric
order , which , for example, can look so :

=CountUDF(R37:BB37;R39:BB39;R41:BB41;R43:BB43;R45:BB45;R47:BB47;R49:BB49;R51:BB51
;R53:BB53;R55:BB55;R57:BB57;R59:BB59;R61:BB61;R66:BB66;R83:BB83)
with this kind of target UDF function don't work ; it's enough to
have , for example ,
=CountUDF(R37:BB51;R55:BB55) , and it don't work .

Is this an multiple target ? ...is this an array ? ......I don't
know ...
It's last my problem I must fix , the last improvement for speed with
VBA without function inside
doesn't matter so much .
Exist it a way to fix and my last problem ?

[maybe some sugestions for my last problem are here ,
http://www.dailydoseofexcel.com/archives/2007/09/18/performance-monitor/
in the 3-th Doug Jenkins comment ??..]
 
I think you can use a parameter array. when call the array seperate the
calling variable with a comma instead of a semicomma


Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())

For RowCount = 1 To Target(MyRow).Rows.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(1).Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount
Next MyRow
End Function
 
I think you can use a parameter array.  when call the array seperate the
calling variable with a comma instead of a semicomma
Click to expand...

IT WORK PERFECT !

5 seconds for an BD91:BD65536 autofill , I dont think
here can be another improvements ;

YOU PROVIDED ME THE DREAM
THAT I WAS LOOKING FOR TO GET IT , no words to thank
you enough for your patience and knowledge share .

Mr. Joel , what can I say ?
Only thing that I wonder is if I shall can sometimes in my life
to can do what you just done for me ! ... Big question for me ;
I'd like to know that for one day the answer will be positive
 
A good day to everybody

I'm so sorry , but I was just hurry , and I haven't check this
UDF function enough ; it is still not working !!...

Is very easy to check : let's work in range B1:G20 ;
we shall use for this example the next macro :
Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())
For RowCount = 1 To Target(MyRow).Rows.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(1).Rows(RowCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next RowCount
Next MyRow
End Function

in column I row 8 we insert
the UDF =CountUDF(B1:G10;B14:G16;B18:G20) . Without any value in all
cells in
this range (B1:G10) , the UDF return FALSE , what is totaly wrong ,
because this UDF
I need to loop in every column of *ALL* arrays - in all arrays such
as it is one only
column in a only array ;
+++++++++++++++++++++++++++++++++++++++++
in next variant it begin to work , but need some little more
changes :

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For MyRow = 0 To UBound(Target())
For ColCount = 1 To Target(MyRow).Columns.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(1).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount
Next MyRow
End Function

with this line of code :
WorksheetFunction.Count(Target(1).Columns(ColCount)) ,
if we insert in Range("B5:G5") Value = 1 (for example) , the UDF
=CountUDF(B1:G10;B14:G16;B18:G20) will return TRUE , what is right ;
then we
insert in column B14 Value = 5 (for example, doesn't matter if value
is big or small)
, the UDF will return FALSE , what is right ;
BUT , if we delete the content of B14 , and put the value in B19 ,
the UDF will return
TRUE , what is wrong ;
It mean code don't recognise target
2 .....ction.Count(Target(1).Columns(ColCount))

++++++++++++++++++++++++++++++++++++++++
If we have the line of code :
WorksheetFunction.Count(Target(2).Columns(ColCount)),
(Target(2) instead of (Target(1) instead , if we insert in column
B14 Value = 5
, the UDF will return TRUE , what is wrong ;
BUT , if we delete the content of B14 , and put the value in B19 ,
the UDF
will return FALSE , what is wright ;
It mean code don't recognise target 1 in this case ;
What I need is the declaration of this targets , to recognise all
targets I need .

If I have 10 targets , it's enough for me ; to recognise something
like :
WorksheetFunction.Count(Target(1;2;3;4;5;6;7;8;9;10).Columns(ColCount))

Thanks very much for your incredible help
 
Please very much to provide me here any kind of ideas , I have
need very strong this UDF to begin built my database .
 
If I may explain myself a little more what
I need to perform this UDF , is to count each
Column separately in a Range of Columns , and in
a Array of Rows . Thank you very much
 
OK, I think I understand what you want better now.
This should work:
Click to expand...

IT WORK PERFECT ! IT WORK !

Thank you very much , mr. Doug Jenkins , you're amasing !
Thank you both , and to Joel ; if you believe me or not ,
you made me a MAN , this UDF was my biggest problem .


Now , this UDF for an autofill BD91:BD65536 take
10 seconds , it's a dream .
You could add some code to check the number of
columns in each range, and give a warning if they are not
all the same.
Click to expand...

This is not a problem ; this can be maybe a last
improvement : for example 0,3 seconds speed improvement
per workbook for me is very much , it provide me a database
bigger with some few millions functions .
I tried to find the right code for VBA only , to can see the
diference between speed , but without any succes :
Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
range_array = Target(myrange).Value2
For ColCount = 1 To Target(0).Columns.Count
MyCount = 0
For myranges = 0 To UBound(Target())
MyCount = Target(myranges).Columns(ColCount).Count
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next myranges
Next ColCount
End Function
 
Back
Top