|
UDF ...VBA Formula built ...please help
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;S 10;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;C OUNT(V1;V2;V3;V4;V5;V6;V7;V8;V9;V10;V11;V12;V13;V1 4;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(Z 1;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;AB 4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB14;AB1 5)<=1;COUNT(AC1;AC2;AC3;AC4;AC5;AC6;AC7;AC8;AC9;AC 10;AC11;AC12;AC13;AC14;AC15)<=1;COUNT(AD1;AD2;AD3; AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12;AD13;AD14;A D15)<=1;COUNT(AE1;AE2;AE3;AE4;AE5;AE6;AE7;AE8;AE9; AE10;AE11;AE12;AE13;AE14;AE15)<=1;COUNT(AF1;AF2;AF 3;AF4;AF5;AF6;AF7;AF8;AF9;AF10;AF11;AF12;AF13;AF14 ;AF15)<=1;COUNT(AG1;AG2;AG3;AG4;AG5;AG6;AG7;AG8;AG 9;AG10;AG11;AG12;AG13;AG14;AG15)<=1;COUNT(AH1;AH2; AH3;AH4;AH5;AH6;AH7;AH8;AH9;AH10;AH11;AH12;AH13;AH 14;AH15)<=1;COUNT(AI1;AI2;AI3;AI4;AI5;AI6;AI7;AI8; AI9;AI10;AI11;AI12;AI13;AI14;AI15)<=1;COUNT(AJ1;AJ 2;AJ3;AJ4;AJ5;AJ6;AJ7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13; AJ14;AJ15)<=1;COUNT(AK1;AK2;AK3;AK4;AK5;AK6;AK7;AK 8;AK9;AK10;AK11;AK12;AK13;AK14;AK15)<=1;COUNT(AL1; AL2;AL3;AL4;AL5;AL6;AL7;AL8;AL9;AL10;AL11;AL12;AL1 3;AL14;AL15)<=1;COUNT(AM1;AM2;AM3;AM4;AM5;AM6;AM7; AM8;AM9;AM10;AM11;AM12;AM13;AM14;AM15)<=1;COUNT(AN 1;AN2;AN3;AN4;AN5;AN6;AN7;AN8;AN9;AN10;AN11;AN12;A N13;AN14;AN15)<=1;COUNT(AO1;AO2;AO3;AO4;AO5;AO6;AO 7;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;COUN T(AR1;AR2;AR3;AR4;AR5;AR6;AR7;AR8;AR9;AR10;AR11;AR 12;AR13;AR14;AR15)<=1;COUNT(AS1;AS2;AS3;AS4;AS5;AS 6;AS7;AS8;AS9;AS10;AS11;AS12;AS13;AS14;AS15)<=1;CO UNT(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;AV1 1;AV12;AV13;AV14;AV15)<=1;COUNT(AW1;AW2;AW3;AW4;AW 5;AW6;AW7;AW8;AW9;AW10;AW11;AW12;AW13;AW14;AW15)<= 1;COUNT(AX1;AX2;AX3;AX4;AX5;AX6;AX7;AX8;AX9;AX10;A X11;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;BA 4;BA5;BA6;BA7;BA8;BA9;BA10;BA11;BA12;BA13;BA14;BA1 5)<=1;COUNT(BB1;BB2;BB3;BB4;BB5;BB6;BB7;BB8;BB9;BB 10;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:B B45)*{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:B B45)*{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;R 14;R16) [R16 not R15 like in BD91!] ;my perfect equivalent array formula shall look so : =AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:B B45)*{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;R 14;R17) [R16 not R15 like in BD91!] ;my perfect equivalent array formula shall look so : =AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:B B45)*{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;R 14;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;R 15;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/arch...mance-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(D 1;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(D 1;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(D 1;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 . |
UDF ...VBA Formula built ...please help
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" wrote: 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;S 10;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;C OUNT(V1;V2;V3;V4;V5;V6;V7;V8;V9;V10;V11;V12;V13;V1 4;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(Z 1;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;AB 4;AB5;AB6;AB7;AB8;AB9;AB10;AB11;AB12;AB13;AB14;AB1 5)<=1;COUNT(AC1;AC2;AC3;AC4;AC5;AC6;AC7;AC8;AC9;AC 10;AC11;AC12;AC13;AC14;AC15)<=1;COUNT(AD1;AD2;AD3; AD4;AD5;AD6;AD7;AD8;AD9;AD10;AD11;AD12;AD13;AD14;A D15)<=1;COUNT(AE1;AE2;AE3;AE4;AE5;AE6;AE7;AE8;AE9; AE10;AE11;AE12;AE13;AE14;AE15)<=1;COUNT(AF1;AF2;AF 3;AF4;AF5;AF6;AF7;AF8;AF9;AF10;AF11;AF12;AF13;AF14 ;AF15)<=1;COUNT(AG1;AG2;AG3;AG4;AG5;AG6;AG7;AG8;AG 9;AG10;AG11;AG12;AG13;AG14;AG15)<=1;COUNT(AH1;AH2; AH3;AH4;AH5;AH6;AH7;AH8;AH9;AH10;AH11;AH12;AH13;AH 14;AH15)<=1;COUNT(AI1;AI2;AI3;AI4;AI5;AI6;AI7;AI8; AI9;AI10;AI11;AI12;AI13;AI14;AI15)<=1;COUNT(AJ1;AJ 2;AJ3;AJ4;AJ5;AJ6;AJ7;AJ8;AJ9;AJ10;AJ11;AJ12;AJ13; AJ14;AJ15)<=1;COUNT(AK1;AK2;AK3;AK4;AK5;AK6;AK7;AK 8;AK9;AK10;AK11;AK12;AK13;AK14;AK15)<=1;COUNT(AL1; AL2;AL3;AL4;AL5;AL6;AL7;AL8;AL9;AL10;AL11;AL12;AL1 3;AL14;AL15)<=1;COUNT(AM1;AM2;AM3;AM4;AM5;AM6;AM7; AM8;AM9;AM10;AM11;AM12;AM13;AM14;AM15)<=1;COUNT(AN 1;AN2;AN3;AN4;AN5;AN6;AN7;AN8;AN9;AN10;AN11;AN12;A N13;AN14;AN15)<=1;COUNT(AO1;AO2;AO3;AO4;AO5;AO6;AO 7;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;COUN T(AR1;AR2;AR3;AR4;AR5;AR6;AR7;AR8;AR9;AR10;AR11;AR 12;AR13;AR14;AR15)<=1;COUNT(AS1;AS2;AS3;AS4;AS5;AS 6;AS7;AS8;AS9;AS10;AS11;AS12;AS13;AS14;AS15)<=1;CO UNT(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;AV1 1;AV12;AV13;AV14;AV15)<=1;COUNT(AW1;AW2;AW3;AW4;AW 5;AW6;AW7;AW8;AW9;AW10;AW11;AW12;AW13;AW14;AW15)<= 1;COUNT(AX1;AX2;AX3;AX4;AX5;AX6;AX7;AX8;AX9;AX10;A X11;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;BA 4;BA5;BA6;BA7;BA8;BA9;BA10;BA11;BA12;BA13;BA14;BA1 5)<=1;COUNT(BB1;BB2;BB3;BB4;BB5;BB6;BB7;BB8;BB9;BB 10;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:B B45)*{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:B B45)*{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;R 14;R16) [R16 not R15 like in BD91!] ;my perfect equivalent array formula shall look so : =AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:B B45)*{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;R 14;R17) [R16 not R15 like in BD91!] ;my perfect equivalent array formula shall look so : =AND(MMULT(TRANSPOSE(ROW(R1:BB45))^0;ISNUMBER(R1:B B45)*{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;R 14;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;R 15;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/arch...mance-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(D 1;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(D 1;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(D 1;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 . |
UDF ...VBA Formula built ...please help
On 7 Aug, 04:58, Joel wrote:
Try calling function below with Thanks so much for help , but please take a look here : http://newtonexcelbach.wordpress.com...tion-vs-udf-2/ How I said , I look for the maxim speed ! Thanks again |
UDF ...VBA Formula built ...please help
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. "ytayta555" wrote: On 7 Aug, 04:58, Joel wrote: Try calling function below with Thanks so much for help , but please take a look here : http://newtonexcelbach.wordpress.com...tion-vs-udf-2/ How I said , I look for the maxim speed ! Thanks again |
UDF ...VBA Formula built ...please help
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 ) |
UDF ...VBA Formula built ...please help
You don't change the function, just change the call
=CountUDF(R1:BB15) "ytayta555" wrote: 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 ) |
UDF ...VBA Formula built ...please help
On 7 Aug, 05:57, Joel wrote:
You don't change the function, just change the call =CountUDF(R1:BB15) 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 |
UDF ...VBA Formula built ...please help
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) "ytayta555" wrote: On 7 Aug, 05:57, Joel wrote: You don't change the function, just change the call =CountUDF(R1:BB15) 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 |
UDF ...VBA Formula built ...please help
On 7 Aug, 13:45, Joel wrote:
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. 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. ) ... 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 ...VBA Formula built ...please help
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" wrote: On 7 Aug, 13:45, Joel wrote: 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. 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. ) ... 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 ...VBA Formula built ...please help
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...etfunction-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/arch...mance-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 |
UDF ...VBA Formula built ...please help
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 "ytayta555" wrote: 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...etfunction-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/arch...mance-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 |
UDF ...VBA Formula built ...please help
On 11 Aug, 03:28, Joel wrote:
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. 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. 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 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/arch...mance-monitor/ in the 3-th Doug Jenkins comment ??..] |
UDF ...VBA Formula built ...please help
Any kind of sugestion here is more then wellcome for me . Many thanks
|
UDF ...VBA Formula built ...please help
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 "ytayta555" wrote: Any kind of sugestion here is more then wellcome for me . Many thanks |
UDF ...VBA Formula built ...please help
On 12 Aug, 08:06, Joel wrote:
I think you can use a parameter array. *when call the array seperate the calling variable with a comma instead of a semicomma 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 |
UDF ...VBA Formula built ...please help
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;1 0).Columns(ColCount)) Thanks very much for your incredible help |
UDF ...VBA Formula built ...please help
Please very much to provide me here any kind of ideas , I have
need very strong this UDF to begin built my database . |
UDF ...VBA Formula built ...please help
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 |
UDF ...VBA Formula built ...please help
I have found myself here a kind of solution , which resolve only
one problem , the problem of recognise of (Target(1) and (Target(2) : 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 )) + _ WorksheetFunction.Count(Target(2).Columns(ColCount )) If MyCount 1 Then CountUDF = False Exit Function End If Next ColCount Next MyRow End Function But still is don't resolved the problem of counting separately each column , to find in every separately column from array if it have more that 1 item (number) in it . Oh , programmers , I'm just crazy here , please to advice me . |
UDF ...VBA Formula built ...please help
I believe that the answer for me is somewhere
around here : http://www.cpearson.com/Excel/Passin...ningArrays.htm http://www.cpearson.com/Excel/VBAArr...erOfDimensions but I am totally lost here . Only if a profesional person work with dates of this pages , I can get what I need so strong . If I am not impolite , exist another groups or forums where I can post this thread ? I ask , because I don't know another better then this . |
UDF ...VBA Formula built ...please help
On 21 Aug, 08:10, wrote:
OK, I think I understand what you want better now. This should work: 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. 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 |
UDF ...VBA Formula built ...please help
I 'll can not to reply/comunicate here before
monday . A good week-end , gentlemans . |
UDF ...VBA Formula built ...please help
Here is a version of the UDF that converts the ranges to arrays:
Function CountUDFA(ParamArray Target() As Variant) As Boolean Dim NumRanges As Long, NumRows() As Long, NumCols As Long, ColCount As Long Dim MyCount As Long, MyRanges As Long, Target2 As Variant, RowNum As Long CountUDFA = True NumRanges = UBound(Target()) - LBound(Target()) + 1 ReDim NumRows(0 To NumRanges - 1) For MyRanges = LBound(Target()) To UBound(Target()) Target(MyRanges) = Target(MyRanges).Value2 NumRows(MyRanges) = UBound(Target(MyRanges)) - LBound(Target(MyRanges)) + 1 Next MyRanges NumCols = UBound(Target(0), 2) - LBound(Target(0), 2) + 1 For ColCount = 1 To NumCols MyCount = 0 For MyRanges = LBound(Target()) To UBound(Target()) Target2 = Target(MyRanges) For RowNum = 1 To NumRows(MyRanges) If Not IsEmpty((Target2(RowNum, ColCount))) Then MyCount = MyCount + 1 If MyCount 1 Then CountUDFA = False Exit Function End If End If Next RowNum Next MyRanges Next ColCount End Function With 3 very big ranges (in Excel 2007) it is much slower than the previous version, but with a lot of small ranges it might be quicker. I will leave it to you to check that. On Aug 22, 9:01*am, ytayta555 wrote: On 21 Aug, 08:10, wrote: OK, I think I understand what you want better now. This should work: * * * * 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. 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 |
UDF ...VBA Formula built ...please help
On 23 Aug, 15:25, wrote:
Here is a version of the UDF that converts the ranges to arrays: With 3 very big ranges (in Excel 2007) it is much slower than the previous version, but with a lot of small ranges it might be quicker. I will leave it to you to check that. I'm very pleasantly surprised for this high level VBA knowledge demonstration , used in interest of my help and needs . My results are the next : 1) =CountUDF(R1:BB12;R15:BB17) = 9,31 seconds =CountUDFA(R1:BB12;R15:BB17) = 16,15 seconds 2) =CountUDF(R1:BB8;R15:BB17;R20:BB24;R26:BB26) = 12,92 sec The same Target UDFA = 17,67 sec 3) =CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15 :W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15 :AC17;AD1:AE12;AD15:AD17;AE1:BB12;AE15:AE17) = 7,22 UDFA = 14,65 4) =CountUDFA(R1:BB2;R4:BB4;R6:BB6;R8:BB8;R10:BB10;R1 2:BB12;R14:BB14;R16:BB16;R18:BB18;R20:BB21;R23:BB2 5) = 30,78 UDFA = 40,85 5) =CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15 :W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15 :AC17;AD1:AE12;AD15:AE17;AF1:BB12;AF15:BB17) = 6,65 UDFA = 14,63 6) =CountUDF(R1:S6;R15:S17;T1:U6;T15:U17;V1:W6;V15:W1 7;X1:Y6;X15:Y17;Z1:AA6;Z15:AA17;AB1:AC6;AB15:AC17; AD1:AE6;AD15:AE17;AF1:BB6;AF15:BB17;R8:S13;T8:U13; V8:W13;X8:Y13;Z8:AA13;AB8:AC13;AD8:AE13;AF8:BB13) = 10,72 UDFA = 19,00 7) =CountUDF(R1:S12;R15:S17;T1:U12;T15:U17;V1:W12;V15 :W17;X1:Y12;X15:Y17;Z1:AA12;Z15:AA17;AB1:AC12;AB15 :AC17;AD1:AE12;AD15:AD17;AE1:AF12;AE15:AF17;AG1:AH 12;AG15:AH17;AI1:AJ12;AI15:AJ17;AK1:AL12;AK15:AL17 ;AM1:AN12;AM15:AN17;AO1:BB12;AO15:BB17) = 7,98 UDFA = 20,14 8) =CountUDF(R1:BB1;R3:BB3;R5:BB5;R7:BB7;R9:BB9;R11:B B11;R13:BB13;R15:BB15;R17:BB17;R19:BB19;R21:BB21;R 23:BB23;R15:BB15;R27:BB27;R29:BB29) = 55,1 UDFA = 30,78 [I have to work in columns from R to BB , and to have in function 15 rows , doesn't matter in how many ranges , but if them are divided in two ranges , like in result 5 , the results seems to be faster ] For a first look , the results seems to be faster with worksheet function inside ; (can be maybe a combination of arrays working with worksheet function , with a better speed ? it was just only an idea , I'm totally lost for this level .. ) THANKS SO MUCH FOR YOUR EFFORT , mr. DOUG JENKINS |
| All times are GMT +1. The time now is 09:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com