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

[ytayta555]

I believe that the answer for me is somewhere
around here :

http://www.cpearson.com/Excel/PassingAndReturningArrays.htm

http://www.cpearson.com/Excel/VBAArrays.htm#NumberOfDimensions

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 .

 

[dougaj4]

ytayta555 - you left a comment on my blog, NewtonExcelBach (http://
newtonexcelbach.wordpress.com/), I'll reply here.

A few small changes may do what you want

First I'm assuming that ; is the correct array separator for your
region. For here (Australia) it's a comma.

Second it might be better to rename the variable "myrow" to "myrange",
since the array Target contains a number of ranges.

Then change: For myrow = 0 To UBound(Target())
to: For myrange = 0 To UBound(Target())

and change: WorksheetFunction.Count(Target(1).Rows(RowCount))
to: WorksheetFunction.Count(Target(myrange).Rows(RowCount))

and change: Next myrow to Next myrange

This will then:
loop through each range
in each range loop through each row
for each row count the number of cells that are not empty
When it finds more than one non-empty cell it will exit indicating
false
If there are 0 or 1 non-empty cells it will exit indicating true

Is that what you want?

For this application it may well be quicker to use
worksheetfunction.count than to try and do it entirely in VBA. To do
it in VBA you could:

Convert each range to a variant array:

range_array = Target(myrange).value2

Then loop through each row and each column of the array, and count non-
empty cells. But it may not be any quicker.

Finally with the code as it is, I don't see any purpose in looping
through each row. Why not simply do the count on each range?

 

[ytayta555]

ytayta555 - you left a comment on my blog, NewtonExcelBach (http://
newtonexcelbach.wordpress.com/), I'll reply here.
A few small changes may do what you want

I didn't expect to get the answer from you youself sir Doug
Jenkins , I am very pleasantly surprised

First I'm assuming that ; is the correct array separator for your
region.  For here (Australia) it's a comma.

I am not wrong here , in european sintax array separator
is ; and for array formula is |

Second it might be better to rename the variable "myrow" to "myrange",
since the array Target contains a number of ranges.
Then change: For myrow = 0 To UBound(Target())
to:  For myrange = 0 To UBound(Target())
and change:   WorksheetFunction.Count(Target(1).Rows(RowCount))
to: WorksheetFunction.Count(Target(myrange).Rows(RowCount))
and change: Next myrow to Next myrange

I made this changes , and it is a big improvement ! Now , UDF
recognise all targets .

In some few minutes we can work in range B1:G20 ,
to see and be shure on results ;
we shall use for this example the next macro :
Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For myranges = 0 To UBound(Target())
For ColCount = 1 To Target(myranges).Columns.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(myranges).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount
Next myranges
End Function

In cell I8 we put the UDF : =CountUDF(B1:G10;B14:G16;B18:G20)
If we put a number in cell B1 and then in B7 , will return False =
good result !
The same thing if we put the number in B15 or B 19 instead of B7 .
BUT , if we have a number in cell B1 and we put a number in cell C19 ,
or
C15 , the UDF will return False , what is wrong .

If we have in all range we work (B1:G20) in cell B1 a number and in
cell D19 another number, the right
result for me is TRUE , because there are not 2 numbers in column
B !!
Actually , UDF don't recognise to count every column separately (to
count the same column in this
three ranges like is a one range ( to recognise column
B1:B10;B14:B16;B18:B20 like a *single* range ).
- - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
[ An example what I need : The worksheet formula look so in this
example : |
=AND(COUNT(B1:B10;B14:B16;B18:B20)<=1;COUNT(C1:C10;C14:C16;C18:C20)<=1;COUNT
|
(D110;D1416;D1820)<=1;.....E...........F.............COUNT(G1:G10;G14:G16;G18:G20)<=1)
|
Here we can see that the worksheet formula count in every column
separate ] |
- - - - - - - - - - -- - - - - - - - - - - - -- -- - - - - - - - -
- - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -

This will then:
 loop through each range

I need to loop through each column in each range , such as
it is a only column in range B1:B10;B14:B16;B18:B20
but , actually , it count like in three columns (ranges) separate !...
The problem is that this UDF actually count in 3 ranges , not
like in one range , each Column must be recognised like an
only range .

 in each range loop through each row
 for each row count the number of cells that are not empty

I need to loop and count in every column , not row

Is that what you want?

My last problem to fix is what I just tried to explain

For this application it may well be quicker to use
worksheetfunction.count than to try and do it entirely in VBA.  To do
it in VBA you could:
Convert each range to a variant array:
range_array = Target(myrange).value2
Then loop through each row and each column of the array, and count non-
empty cells.  But it may not be any quicker.

Here , I'm totally lose , I need like a beginner all code , but it
doesn't matter
so much , I can not to ask you more , I am very pleased to fix this my
last
problem ( to count in every column separate but not how they are 3
ranges
separate ).

Finally with the code as it is, I don't see any purpose in looping
through each row.  Why not simply do the count on each range?

I'll have not just one UDF function , I'll have millions of this kind
of UDF ,
that's why I am looking for fastest speed , an improvement of 1
second
per workbook is very much for me ; with array worksheet formula it
take
60 seconds per workbook , with this UDF can take and 10 seconds
or more slightly , because worksheet formula doesn't have :
,, If MyCount > 1 Then
CountUDF = False
Exit Function ,, ............

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.

I tried in many ways , it is the best and fastest kind of querry in my
kind of database .

 

[ytayta555]

A few small changes may do what you want
Is that what you want?

I worked , and I have resolved the last but one problem .

With the next code the UDF recognise to count each column
separate in every range, but do not recognise to count in all
ranges of the array the same column as like it is one
range :

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

For ColCount = 1 To Target(myrange).Columns.Count
MyCount =
WorksheetFunction.Count(Target(myrange).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount
Next myrange
End Function

With the our previous code , UDF recognise to count in
all ranges of the array , but not in the each separate column
of every range as like it is one range :

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

For ColCount = 1 To Target(myrange).Columns.Count
MyCount = MyCount + _
WorksheetFunction.Count(Target(myrange).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next ColCount
Next myrange
End Function

 

[dougaj4]

OK, I think I understand what you want better now.

You need to put the For to loop through the columns first, then loop
through each range, and you also need to reset MyCount to zero for
each column.

This should work:

Function CountUDF(ParamArray Target() As Variant)
CountUDF = True

For ColCount = 1 To Target(0).Columns.Count
MyCount = 0
For myranges = 0 To UBound(Target())
MyCount = MyCount +
WorksheetFunction.Count(Target(myranges).Columns(ColCount))
If MyCount > 1 Then
CountUDF = False
Exit Function
End If
Next myranges
Next ColCount
End Function

This will take the number of columns in the first range as being the
same as all the other ranges. 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.

ytayta555- you left a comment on my blog, NewtonExcelBach (http://
newtonexcelbach.wordpress.com/), I'll reply here.
A few small changes may do what you want

          I didn't expect to get the answer from you youself sir Doug
Jenkins , I am very pleasantly surprised

First I'm assuming that ; is the correct array separator for your
region.  For here (Australia) it's a comma.

          I am not wrong here , in european sintax array separator
is  ;  and for array formula is  |

Second it might be better to rename the variable "myrow" to "myrange",
since the array Target contains a number of ranges.
Then change: For myrow = 0 To UBound(Target())
to:  For myrange = 0 To UBound(Target())
and change:   WorksheetFunction.Count(Target(1).Rows(RowCount))
to: WorksheetFunction.Count(Target(myrange).Rows(RowCount))
and change: Next myrow to Next myrange

       I made this changes , and it is a big improvement ! Now , UDF
recognise all targets .

In some few minutes we can work  in range B1:G20 ,
to see and be shure on results  ;
we shall use for this example the next macro :
Function CountUDF(ParamArray Target() As Variant)
CountUDF = True
MyCount = 0
For myranges = 0 To UBound(Target())
For ColCount = 1 To Target(myranges).Columns.Count
      MyCount = MyCount + _
         WorksheetFunction.Count(Target(myranges).Columns(ColCount))
      If MyCount > 1 Then
         CountUDF = False
         Exit Function
      End If
   Next ColCount
Next myranges
End Function

In cell I8 we put the UDF :  =CountUDF(B1:G10;B14:G16;B18:G20)
If we put a number in cell B1 and then in B7 , will return False =
good result !
The same thing if we put the number in B15 or B 19 instead of B7 .
BUT , if we have a number in cell B1 and we put a number in cell C19 ,
or
C15 , the UDF will return False , what is wrong .

If we have in all range we work  (B1:G20) in cell B1 a number and in
cell D19 another number, the right
result for me is TRUE , because there are not 2 numbers in column
B !!
Actually , UDF don't recognise to count every column separately (to
count the same column in this
three ranges like is a one range ( to recognise column
B1:B10;B14:B16;B18:B20 like a *single* range ).
- - - - - - - -  -- - - -  - - - - - - - - - - - -  - - - - - - - --
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  -
- -
[  An example what I need :    The worksheet formula look so in this
example  :                              |
=AND(COUNT(B1:B10;B14:B16;B18:B20)<=1;COUNT(C1:C10;C14:C16;C18:C20)<=1;COUN­T
|
(D110;D1416;D1820)<=1;.....E...........F.............COUNT(G1:G10;G14­:G16;G18:G20)<=1)
|
Here we can see that the worksheet formula count in every column
separate  ]                              |
- - - - - - - - - - --  - - - - - - - - - - - --  --  - - - - - - --
- - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-  -

This will then:
 loop through each range

I need to loop through each column in each range , such as
it is a only column in range B1:B10;B14:B16;B18:B20
but , actually , it count like in three columns (ranges) separate !...
The problem is that this UDF actually count in 3 ranges , not
like in one range , each  Column must be recognised like an
only range .

 in each range loop through each row
 for each row count the number of cells that are not empty

I need to loop and count in every column , not row

Is that what you want?

My last problem to fix is what I just tried to explain

For this application it may well be quicker to use
worksheetfunction.count than to try and do it entirely in VBA.  To do
it in VBA you could:
Convert each range to a variant array:
range_array = Target(myrange).value2
Then loop through each row and each column of the array, and count non-
empty cells.  But it may not be any quicker.

Here , I'm totally lose , I need like a beginner all code , but it
doesn't matter
so much , I can not to ask you more , I am very pleased to fix this my
last
problem ( to count in every column separate but not how they are 3
ranges
separate ).

Finally with the code as it is, I don't see any purpose in looping
through each row.  Why not simply do the count on each range?

I'll have not just one UDF function , I'll have millions of this kind
of UDF ,
that's why I am looking for fastest speed , an improvement of 1
second
per workbook is very much for me ; with array worksheet formula it
take
60 seconds per  workbook , with this UDF can take and 10 seconds
or more slightly , because  worksheet formula doesn't have :
  ,, If MyCount > 1 Then
         CountUDF = False
         Exit Function ,,          ............> Joelwrote ...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.

I tried in many ways , it is the best and fastest kind of querry in my
kind of database .

 

[ytayta555]

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

 

[ytayta555]

I 'll can not to reply/comunicate here before
monday . A good week-end , gentlemans .

 

[dougaj4]

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.

 

[ytayta555]

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;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