Why I got wrong result from nested function?

[Billy]

Hello!

In VBA I made two functions: Func1 and Func2 (code is below). They
work on that principle:
- Let say that we wrote on Sheet2 arbitrary values in range A1:B27.
- Now let say that I wrote on Sheet1 in Cell C4 "=Func1()". I get
result 4 what is of course wrong. The result should be number of the
last row on Sheet2 - that is 27.
- Correct result I get if I call that "Func1" from Sub1.

Look like I overlook something but I don't know what. Can anybody help
me with that problem so that Func1 will return correct result which is
27?

---------------------
Code from the module:
---------------------
Option Explicit

Function Func1()
Dim strWsName As String

strWsName = "Sheet2"
Func1 = Func2(strWsName)
End Function

Function Func2(strSh As String)
Dim lngLastRow As Long, oWS As Worksheet

Set oWS = ActiveSheet

Sheets(strSh).Select
Range("A2").Select
ActiveCell.End(xlDown).Select
lngLastRow = ActiveCell.Row
Debug.Print "lngLastRow: " & lngLastRow

oWS.Select
Set oWS = Nothing

Func2 = lngLastRow
End Function

Sub Sub1()
Debug.Print "Work Ok: " & Func2("Sheet2")
End Sub


Thank you!

 

[Tom Ogilvy]

If you use a function in a worksheet, it can't perform actions like
selecting or otherwise change the excel environment (like formatting cells -
not allowed either or changing values in other cells - not allowed either -
as examples of other things you can't do).

I have adjusted you code to get rid of the unnecessary selecting and it
worked fine for me:

Option Explicit

Function Func1()
Application.Volatile
Dim strWsName As String

strWsName = "Sheet2"
Func1 = Func2(strWsName)
End Function

Function Func2(strSh As String)
Dim lngLastRow As Long, oWS As Worksheet

Set oWS = Sheets(strSh)


lngLastRow = oWS.Range("A2").End(xlDown).Row
Debug.Print "lngLastRow: " & lngLastRow

Func2 = lngLastRow
End Function

Since you don't have any argument in Func1, it will not recalculate. I
added application.Volatile so it will recalculate each time the worksheet is
recalculated.

 

[Billy]

Tom, thank You for your corrections. Now works just fine. I didn't
know that functions can't perform actions on the way I tryed to
execute in the code.

Best regards,
Billy