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

 

[Restricted]

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.

Great thank you. So even though the function only takes Range objects, you still have to pass the object as a Range object (i.e. Range("A:B"))?

Further, for whatever reason, if I decide to use Select() and ActiveCell, how come the following returns an error (Select method of Range class failed)?

Function rowCount(count_range As Range)
Dim intRows As Integer

count_range.Select

Do Until IsEmpty(ActiveCell)
intRows = intRows + 1
ActiveCell.Offset(1, 0).Select
Loop
countRows = intRowCount
End Function

This is the error that I always receive and the error stops on the Select statement (in this case: count_range.Select)
And IIRC, to bypass the error, one must activate the worksheet containing that range before it can be selected. As far as I know, this only occurs in Excel 2013, however what is the cause and is there a way around it or am I doing something wrong?

Thank you

 

[Andrew Poulsom]

To Select a Range its Worksheet must be the ActiveSheet (just like in the User Interface). That's true of all versions of Excel.

 

[Restricted]

To Select a Range its Worksheet must be the ActiveSheet (just like in the User Interface). That's true of all versions of Excel.

O-K I agree. In that regard, may I ask what modifications should I make to the function? Since there is no method such as ActiveWorksheet.Select, does this mean that I had to do what I previously did in the original rowCount function posted?
i.e. Have to add an extra argument in the function that contains a reference to the Worksheet object?

 

[Andrew Poulsom]

If you fell you must Select you could add:

count_range.Parent.Select

but as I said before Selection isn't necessary and is actually bad practice.

 

[Restricted]

If you fell you must Select you could add:

count_range.Parent.Select

but as I said before Selection isn't necessary and is actually bad practice.

Great thanks. And yes I realised, thanks for reinforcing that. I actually do have quite a few in my project, however what are the ways around them (besides the one you had written previously)? Is there an existing thread or reference somewhere with how to avoid them? The way I've learnt VBA was through using Selection, is it just inefficient as in it takes up more resources to process or?

 

[Andrew Poulsom]

You may find this helpful:

Beyond Excel's recorder

 

[Restricted]

You may find this helpful:

Beyond Excel's recorder

Great thanks for your help Andrew!

 

[Restricted]

You may find this helpful:

Beyond Excel's recorder

Actually, in regards to the ".Parent" method, I encounter an error: Method 'Range' of object '_Global' failed
After typing ".Parent", the default Windows beep can be heard... almost like the code has reached an end, if this helps to identify what is wrong..

Disregard above. I used range(count_range).Parent.Select instead of count_range.Parent.Select. (default Windows beep can still be heard however after typing ".Parent"

Even then... I encounter this error:
Method 'Range' of object '_Worksheet" failed
Error occurs on the line: Concatenate(arg1, arg2, MyNamedWorksheet.Range("B2:B" & rowCount(MyNamedWorksheet.Range("B2")))

Any ideas?

 

[Andrew Poulsom]

Please post your entire code.