Nested functions and counting the amount of non-empty rows in a range

[Restricted]

I have a function that concatenates a string and stores it into a single variable, and this is what the function returns.
The other function is used to count how many non-empty rows is in a particular range, and I need to call this function in the Concatenation function above. How can I do so?

Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & RowCount(B2) ))
The 3rd argument basically specifies the range used in the concatenation...

returns a -> 'Object Required' error on the same line (so the function hasn't even been evaluated yet).
Is this how you call the function in VBA?

Generally with VBA, I have used variables to store the result of the return value from functions, however I've never had to call one within another function and was wondering whether I should call the function and store it in a variable, and THEN, use that variable instead? However... this seemingly creates unnecessary variables and there is no need for that when I just require the need to call the function, in order for it to return a value.

I have also attempted:
Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & RowCount(MyNamedWorksheet.Range("B2") ))
However, I believe this passes the value of the range... which is not what I want, in addition it returned an error as well.


In addition, the need for the RowCount function is because the the table in MyNamedWorksheet is dynamic, and the amount of data in that table varies from time to time and rather than specifying the entire range via an arbitrary number i.e. B2:B9999, I'd like the '9999' to be replaced with a SPECIFIC row count.
I've used a Do Until IsEmpty(ActiveCell) loop and incrementing a counter to determine the amount of rows.

Any suggestions?

Thank you

 

[Andrew Poulsom]

You can't specify a function's argument in its declaration, if that's what you mean. You have to pass what you have as the third argument when you call the function. If you need more help please post the Function and the Sub that calls it.

 

[Restricted]

You can't specify a function's argument in its declaration, if that's what you mean. You have to pass what you have as the third argument when you call the function. If you need more help please post the Function and the Sub that calls it.

Sorry for not being clear, the code above resides within a sub procedure, I am not declaring a function within another function's argument.

Function Concatenate(s As String, table_range As Range, concat_range As Range)

Dim j As Integer
Dim result As String

For j = 1 To table_range.Rows.Count
If table_range.Cells(j, 1).Value = s Then
result = result & " " & concat_range.Cells(j, 1).Offset(0, 1).Value & " " & concat_range.Cells(j, 1).Value
End If
Next i
Concatenate = Trim(result)

End Function



Function rowCount(count_range As Range, worksht as Worksheet)
Dim intRows As Integer
Dim wkSheet As Worksheet

Set wkSheet = ThisWorkbook.Worksheets(worksht)

wkSheet.Select
count_range.Select

Do Until IsEmpty(ActiveCell)

intRows = intRows + 1 '
ActiveCell.Offset(1, 0).Select

Loop

End Function

I believe I don't even require worksht, i.e. the worksheet where the range is found. Is it unnecessary?



Code within sub that calls function:
strText = Concatenate(code, wksAnimalFood.Range("A1:A60"), wksAnimalFood.Range("B2:B" & countRows(B2)))




Thanks

 

[Andrew Poulsom]

Your rowCount function isn't returning anything. It's not necessary to specify a Worksheet because that is the Range object's Parent. Also it's not necessary to do any Selecting. Can you give some examples of what you want rowCount to return given some sample data for count_range?

 

[Restricted]

Your rowCount function isn't returning anything. It's not necessary to specify a Worksheet because that is the Range object's Parent. Also it's not necessary to do any Selecting. Can you give some examples of what you want rowCount to return given some sample data for count_range?

Great thanks. I have been pre-empted into using 'Select' everywherein Excel 2013, because if I do not specify that in sub procedures, I get a "Subscript out of range" error. Compared earlier versions of Excel where I did not need to do this. So it seems as though that habit has gotten into my Function declarations as well.

How do I ensure my function returns something in VBA?
i.e. C++, it is specified after 'return', however in VBA, IIRC, do I just write the Function name and an assignment operator followed by what I wish to return?

Sample data:

https://hostr.co/file/970/pdZz5LmAkyti/example2.png
(assume rows 8, 9, 10 ... 99999 is empty...)
count_range could be.. A2.

I need it to return the value 6 in this case (6 rows with data entered).

I can then add 1 to this value (yes, I forgot to do this in my function, or I could alternatively do it after calling it in the subprocedure), and then use this to reference concat_range.
i.e. Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & RowCount(B2) ))
So that the appropriate range will now be "B2:B7", hence allowing me to avoid using an arbitrary range like "B2:B999", which although does work.. appears too haphazard.

Does that make sense? Thank you Andrew

 

[Andrew Poulsom]

How do I ensure my function returns something in VBA?
i.e. C++, it is specified after 'return', however in VBA, IIRC, do I just write the Function name and an assignment operator followed by what I wish to return?

Exactly.

If your data is contiguous you can use:

Dim LastRow as Long
LastRow = Range("A2").End(xlDown).Row

 

[Restricted]

Exactly.

If your data is contiguous you can use:

Dim LastRow as Long
LastRow = Range("A2").End(xlDown).Row

Ok so: rowCount = intRows should be written at the end of the function -- done. So even then, am I calling the function correctly? If so, what is with the error (specified in the OP)? How should I best be calling it from within the Concatenation function in the Sub procedure?

Also, yes I am aware of xlDown, however assuming data is not contiguous, I would like to stick by what I have already done through a manual loop to count the rows, however I am not sure if what I have now is efficient as possible and if there are any other constraints I should be aware of. Or is everything fine and now I just need to know how to properly call the function to avoid the error (Object Required)?

Thank you


Also, when you said no selecting is required, how does the function know where to start the loop then? (since I am using ActiveCell, or should that instead be replaced by count_range?)

 

[Andrew Poulsom]

If the data isn't contiguous you would need End(xlUp) from the bottom of the column - Range("A" & Rows.Count).

As for the error what is MyNamedWorksheet?

You can avoid Selecting and ActiveCell like this:

Function rowCount(count_range As Range)
    Dim Cell As Range
    Dim intRows As Long
    Set Cell = count_range.Cells(1, 1)
    Do Until IsEmpty(Cell)
        intRows = intRows + 1
        Set Cell = Cell.Offset(1, 0)
    Loop
    rowCount = intRows
End Function

But that's pretty much the same as using End(xlDown).

 

[Restricted]

If the data isn't contiguous you would need End(xlUp) from the bottom of the column - Range("A" & Rows.Count).

As for the error what is MyNamedWorksheet?

You can avoid Selecting and ActiveCell like this:

Function rowCount(count_range As Range)
    Dim Cell As Range
    Dim intRows As Long
    Set Cell = count_range.Cells(1, 1)
    Do Until IsEmpty(Cell)
        intRows = intRows + 1
        Set Cell = Cell.Offset(1, 0)
    Loop
    rowCount = intRows
End Function

But that's pretty much the same as using End(xlDown).

Ok great thanks.

MyNamedWorksheet, sorry, that is just a Worksheet object I named and it is a reference to my main Worksheet (via Set).

And ok, I was talking hypothetically, basically I don't want to use End(xlUp) and would prefer to use what we have got at the moment.
So may I confirm how do I call this function?

Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & rowCount(B2) ))? That should work?

 

[Andrew Poulsom]

It would need to be:

Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & rowCount(MyNamedWorksheet.Range("B2")))

With your code VBA assumes that B2 is a variable and that variable must contain a Range object.