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

  • Thread starter Thread starter ytayta555
  • Start date Start date
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.
 
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.
Click to expand...

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;R12:BB12;R14:BB14;R16:BB16;R18:BB18;R20:BB21;R23:BB25)
= 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:W17;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:AH12;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:BB11;R13:BB13;R15:BB15;R17:BB17;R19:BB19;R21:BB21;R23: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
 
Back
Top