Problems with passing an array into a user-defined function

[cuddling101]

I am having problems with passing an array into a UDF.

I have essentially copied most of the code from an Excel discussion board, but I am getting the #VALUE error when the code runs. I have tried some debugging and received some information like Type mismatch with regard to trying to 'examine', via the Immediate window, the range passed, when I inserted a breakpoint just before the For loop. I also got a 'method does not support object' error when I tried to insert a Print K statement, and mark that as a breakpoint for my debugging.

The function is seeking to count the number of cells, in a range, that have a certain colour background. The range is in another open workbook.

The function's code is -

Function Families_in_Generation(Gen_Colour As Long, Families_List As Range) As Long

Dim fGen_Colour As Long
Dim fFamilies_List As Range
Dim fCell As Range
       
Dim K As Long

K = 0
       
fGen_Colour = Gen_Colour
fFamilies_List = Families_List
       
For Each fCell In fFamilies_List
              
    If fCell.Interior.ColorIndex = fGen_Colour Then
       
       K = K + 1
              
    End If
       
Next fCell
       
Families_in_Generation = K

End Function

It is called with the following call -

=Families_in_Generation((Get_Cell_Colour((INDIRECT("'[Samuel_Johnson_and_Anne_Lawler_Descendants_Book_Control.xlsx]Samuel Johnson'!$" _ 
&(VLOOKUP((SHEET()), [Samuel_Johnson_and_Anne_Lawler_Descendants_Book_Control.xlsx]Gens_to_Columns!$A$2:$B$15,2,FALSE))&"$1",TRUE)))), _
(Genealogical_Service_Users_Tracing_Project_Families.xlsm!Statistics_1:Genealogical_Service_Users_Tracing_Project_Families.xlsm!Last_Family))

I split the call into three lines, just for the purpose of display in this forum. The actual call all runs together, in the calling cell, of course.

The call, after evaluation, enters the 'subject function', just as I expect, as

= Families_in_Generation(255,  [Genealogical_Service_Users_Tracing_Project_Families.xlsm]Coverage_Statistics!$A$2:$A$94))

This whole area of passing a range into a UDF is an area I have never been that comfortable with, and I am obviously getting the syntax wrong, somewhere along the way. I wondered whether I need to pass the worksheet name, column, and rows, as separate parameters, and then build the range in the UDF, but that was just one thought.

Suggested code corrections / amendments please; with my variable names if possible, please.

With thanks in anticipation.

Best regards

Philip
Bendigo, Victoria
Australia

 

[cuddling101]

I have tried to restructure the function so it receives the workbook and worksheet names as Strings, plus the column, and rows, as Longs, and then build the name, to refer to the range, inside the function, as follows

Function Families_in_Generation(Gen_Colour As Long, Families_List_WB_Name As String, Families_List_WS_Name As String, _
                                Families_List_Col As Long, Families_List_Start_Row As Long, _
                                Families_List_End_Row As Long) As Long

Dim fGen_Colour As Long
Dim fFamilies_List_WB_Name As String
Dim fFamilies_List_WS_Name As String
Dim fFamilies_List_Col As Long
Dim fFamilies_List_Start_Row As Long
Dim fFamilies_List_End_Row As Long

fGen_Colour = Gen_Colour
fFamilies_List_WB_Name = Families_List_WB_Name
fFamilies_List_WS_Name = Families_List_WS_Name
fFamilies_List_Col = Families_List_Col
fFamilies_List_Start_Row = Families_List_Start_Row
fFamilies_List_End_Row = Families_List_End_Row

Dim fFamilies_List_Text As Variant

Dim fFamilies_List As Range
Dim fCell As Range

Dim K As Long

K = 0

fFamilies_List_Text  = "=" & Families_List_WS_Name & "!R" &  fFamilies_List_Start_Row & "C" & fFamilies_List_Col & _
                                                           ":R" & fFamilies_List_End_Row & "C" & fFamilies_List_Col

K = K

Workbooks(Families_List_WB_Name).Names.Add Name:="fFamilies_List", RefersToR1C1:=fFamilies_List_Text
       
K = K

For Each fCell In fFamilies_List
              
    If fCell.Interior.ColorIndex = fGen_Colour Then
       
       K = K + 1
              
    End If
       
Next fCell
       
Families_in_Generation = K

End Function

but I still get a #VALUE error.

Can somebody PLEASE help me here; this whole thing of referring to a range, in a worksheet in another workbook, within a UDF, is clearly something I am not understanding well enough, but it is important to my family history project that I get this worked out. PLEASE HELP.

NB: K = K statements are only there so I can create debugging breakpoints.

Philip
Bendigo, Victoria
Australia

 

[Norie]

Philip

Have you tried stepping through the code by setting a breakpoint and then using F8?

 

[cuddling101]

I have tried stepping through the code. I am not sure about your reference to F8; what does that do? I have used the immediate window to examine things along the way and I have got some type mismatch errors and I suspect that is where my problem is BUT the bottom line is that I do not properly understand the correct syntax, AND method, to have a UDF 'look across', so to say, when having been called from a cell in a worksheet in one workbook to a range of cells in another worksheet in another workbook, both of which are open.

 

[cuddling101]

I tried the F8, which is step-through - I did not know that.

The code where the stepping through abends is -

Workbooks(Families_List_WB_Name).Names.Add Name:="fFamilies_List", RefersToR1C1:=fFamilies_List_Text

when I have the DIM statement DIM fFamilies_List as Variant before it, in the code.

When I omit the DIM statement completely, the above statement appears to execute BUT then the statement

For Each fCell In fFamilies_List

abends with the message that the variable fFamilies_List is undefined.

 

[Norie]

Does this work?

Function Families_in_Generation(Gen_Colour As Long, Families_List As Range) As Long

Dim fGen_Colour As Long
Dim fFamilies_List As Range
Dim fCell As Range
       
Dim K As Long
       
fGen_Colour = Gen_Colour
Set fFamilies_List = Families_List
       
For Each fCell In fFamilies_List.Cells
              
    If fCell.Interior.ColorIndex = fGen_Colour Then
       
       K = K + 1
              
    End If
       
Next fCell
       
Families_in_Generation = K

End Function

 

[cuddling101]

Just to close this one off, and in case anybody looks at this for ideas in future, the working code ended up as -

Function Families_in_Generation(Gen_Colour As Long, Families_List As Range) As Long

Dim fGen_Colour As Long

Dim rCell As Range
              
Dim K As Long

Dim fTest_Colour As Long

K = 0

fGen_Colour = Gen_Colour

K = K
       
For Each rCell In Families_List
              
    fTest_Colour = rCell.Interior.Color
    
    If fTest_Colour = fGen_Colour Then
       
       K = K + 1
              
    End If
       
Next rCell
       
Families_in_Generation = K

One point of silliness, which I discovered near the end of finalisation, was that I was passing in Color and then testing Color Index BUT that was not the core of the issue - the core was working out how to properly pass in and address a range. One of the things I had to do in the end was directly address the passed in range, rather than adopting my usual style of assigning each passed in parameter to an internal UDF variable and then code against those internal variables only. I was not able to achieve that 'standard' on this occasion.

One other thing, which I did not appreciate from the code elements that I drew from elsewhere was that, the word rCell, in the code line

For each rCell in Families_List

was not just another variable, which might therefore be called anything, which is what I thought in the first place BUT is, rather, a VBA language term - not one I had come across before, so that is why I went astray for a long time.

I know a lot of the above might be obvious to many but, as a bottom end Intermediate skills VBA programmer, and a former Help Desk coordinator who dealt with very non-savvy users, I have always preferred a style of computer help information that spells out everything in extremely fine detail and I would therefore have preferred if some of the posts on this board, and elsewhere, had made that last point explicitly clear, in the way that I have just done. KISS principle, eh.

Best regards to everybody

Philip
Bendigo, Victoria
Australia

 

[Norie]

Philip

rCell is a variable.

It could be named anything, within reason.

PS Did the code I posted work at all?

 

[NdNoviceHlp]

I'm glad U figured this out Philip but the word "rCell" is not a vba language term... U declared it yourself as a range It also seems like a lot of unnecessary bother to be re-assigning variable names... that's the beauty of functions/sub input parameters… they are used within the function/sub code. Anyways, thanks for the interesting read of your trials and triumph. Dave