Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why I got wrong result from nested function?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why I got wrong result from nested function?
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. -- Regards, Tom Ogilvy "Billy" wrote in message om... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why I got wrong result from nested function?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrong Result with an IF function | Excel Worksheet Functions | |||
What is wrong with this nested function? | Excel Worksheet Functions | |||
wrong result if function values over 256 | Excel Worksheet Functions | |||
EXCEL 2000 - IF function returns wrong result from logical test | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions |