Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call to INDIRECT within user defined function
Cut down version of spreadsheet
Status Type Col 4 Col 5 Row 6 Found A Row 8 On Way A Row 9 On Way G Row 10 Bidding C Row 11 Found B I am trying to write a built in function which will look from one worksheet to another, and return the number of occurences of a certain status against a particular type. I wrote the following function with a view to doing this, but all I get is a VALUE error. I tried to use the Debug.Print but nothing appears in the Immediate window when I get there and, infact, I can't look at the VBA window until after the calculation has finished in the worksheet. I tried inserting a breakpoint after the Debug.Print statement but even with that I don't get anything displayed in the Immediate window. I am really having difficulty with understanding how to use the Debug facility properly at all. I have a thought that the basics of my problem is how I am calling INDIRECT but I may be wrong in that too. ************ Function Type_Prospectives(fFR As Integer, fLR As Integer, fWSName As String, _ fCAT As String, fTS As String) As Integer Type_Prospectives = 0 Dim I As Integer Dim IV1 As String Dim IV2 As String Dim TV1 As String Dim TV2 As String For I = fFR To fLR IV1 = fWSName & "!R" & (Str(I)) & "C4" IV2 = fWSName & "!R" & (Str(I)) & "C5" TV1 = Application.WorksheetFunction.INDIRECT(IV1, False) TV2 = Application.WorksheetFunction.INDIRECT(IV2, False) Debug.Print I, IV1, IV2, TV1, TV2 If TV1 = fCAT Then If TV2 = fTS Then Type_Prospectives = Type_Prospectives + 1 End If End If Next I ************ Typical call to function (in full spreadsheet) reads as: =Type_Prospectives(6,145,Inventory,"Found","A") Three are three potential values of fCAT, and eleven of fTS, so I am looking to call this 33 times in the full spreadsheet. One other strange quirk that I would welcome insight on is the fact that when I tried to have the function statement as a single line, VBA would not even let me save - the line just went red and I could not proceed. When I inserted the second line and used the underscore continuation character, it was all OK, at least as regards the Function statement anyway. Any advice welcome, please. Best regards Philip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call to INDIRECT within user defined function
IV1 = fWSName & "!R" & (Str(I)) & "C4"
gives an address. and TV1 = Application.WorksheetFunction.INDIRECT(IV1, False) retrieves the value from that address. I'd use: TV1 = worksheets(fwsname).cells(i,4).value But because you're not passing all the ranges that excel needs to know so that it can calculate when something changes, your UDF may not show the correct results. You can add: Function Type_Prospectives(...) Application.volatile ... End Function But this will make it so that excel recalcs this UDF whenever it recalcs. So the value shown in that formula cell could still be incorrect if excel hasn't recalculated. Before you trust the results, make sure you force a recalculation. And remember, since you're passing a string as the worksheet name: Function Type_Prospectives(fFR As Integer, _ fLR As Integer, _ fWSName As String, _ fCAT As String, _ fTS As String) As Integer This: =Type_Prospectives(6,145,Inventory,"Found","A") should be: =Type_Prospectives(6,145,"Inventory","Found","A") Or you could use a formula like: =sumproduct(--(indirect("'inventory'!r6c4:r145c4",false)="found" ), --(indirect("'inventory'!r6c5:r145c5",false)="A")) You could also use other cells to hold those variables. =sumproduct(--(indirect("'"&a1&"'!r"&a2&"c4:r"&a3&"c4",false)=A4 ), --(indirect("'"&a1&"'!r"&a2&"c5:r"&a3&"c5",false)=a5 )) Where A1 holds the worksheet name A2 holds the first row A3 holds the last row A4 holds the cat string A5 holds the TS string Ps. If you go with the UDF, change all the Integer's to Long's. Long's are better. Philip Mark Hunt wrote: Cut down version of spreadsheet Status Type Col 4 Col 5 Row 6 Found A Row 8 On Way A Row 9 On Way G Row 10 Bidding C Row 11 Found B I am trying to write a built in function which will look from one worksheet to another, and return the number of occurences of a certain status against a particular type. I wrote the following function with a view to doing this, but all I get is a VALUE error. I tried to use the Debug.Print but nothing appears in the Immediate window when I get there and, infact, I can't look at the VBA window until after the calculation has finished in the worksheet. I tried inserting a breakpoint after the Debug.Print statement but even with that I don't get anything displayed in the Immediate window. I am really having difficulty with understanding how to use the Debug facility properly at all. I have a thought that the basics of my problem is how I am calling INDIRECT but I may be wrong in that too. ************ Function Type_Prospectives(fFR As Integer, fLR As Integer, fWSName As String, _ fCAT As String, fTS As String) As Integer Type_Prospectives = 0 Dim I As Integer Dim IV1 As String Dim IV2 As String Dim TV1 As String Dim TV2 As String For I = fFR To fLR IV1 = fWSName & "!R" & (Str(I)) & "C4" IV2 = fWSName & "!R" & (Str(I)) & "C5" TV1 = Application.WorksheetFunction.INDIRECT(IV1, False) TV2 = Application.WorksheetFunction.INDIRECT(IV2, False) Debug.Print I, IV1, IV2, TV1, TV2 If TV1 = fCAT Then If TV2 = fTS Then Type_Prospectives = Type_Prospectives + 1 End If End If Next I ************ Typical call to function (in full spreadsheet) reads as: =Type_Prospectives(6,145,Inventory,"Found","A") Three are three potential values of fCAT, and eleven of fTS, so I am looking to call this 33 times in the full spreadsheet. One other strange quirk that I would welcome insight on is the fact that when I tried to have the function statement as a single line, VBA would not even let me save - the line just went red and I could not proceed. When I inserted the second line and used the underscore continuation character, it was all OK, at least as regards the Function statement anyway. Any advice welcome, please. Best regards Philip -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call to INDIRECT within user defined function
Thank you very much Dave. I must read up more on that Worksheets call. My
function now works. Thank you for the reminder re the recalc; I use F9 regularly, as there are many formulae in this spreadsheet and the base data changes regularly, as I add to and find more items online for my book collection. The call line was just a typo on my part, I actually use Laurent Longre's SHEETNAME function from his morefunc, a set of additional functions which have been a real godsend over the last few months. Warmest regards Philip "Dave Peterson" wrote: IV1 = fWSName & "!R" & (Str(I)) & "C4" gives an address. and TV1 = Application.WorksheetFunction.INDIRECT(IV1, False) retrieves the value from that address. I'd use: TV1 = worksheets(fwsname).cells(i,4).value But because you're not passing all the ranges that excel needs to know so that it can calculate when something changes, your UDF may not show the correct results. You can add: Function Type_Prospectives(...) Application.volatile ... End Function But this will make it so that excel recalcs this UDF whenever it recalcs. So the value shown in that formula cell could still be incorrect if excel hasn't recalculated. Before you trust the results, make sure you force a recalculation. And remember, since you're passing a string as the worksheet name: Function Type_Prospectives(fFR As Integer, _ fLR As Integer, _ fWSName As String, _ fCAT As String, _ fTS As String) As Integer This: =Type_Prospectives(6,145,Inventory,"Found","A") should be: =Type_Prospectives(6,145,"Inventory","Found","A") Or you could use a formula like: =sumproduct(--(indirect("'inventory'!r6c4:r145c4",false)="found" ), --(indirect("'inventory'!r6c5:r145c5",false)="A")) You could also use other cells to hold those variables. =sumproduct(--(indirect("'"&a1&"'!r"&a2&"c4:r"&a3&"c4",false)=A4 ), --(indirect("'"&a1&"'!r"&a2&"c5:r"&a3&"c5",false)=a5 )) Where A1 holds the worksheet name A2 holds the first row A3 holds the last row A4 holds the cat string A5 holds the TS string Ps. If you go with the UDF, change all the Integer's to Long's. Long's are better. Philip Mark Hunt wrote: Cut down version of spreadsheet Status Type Col 4 Col 5 Row 6 Found A Row 8 On Way A Row 9 On Way G Row 10 Bidding C Row 11 Found B I am trying to write a built in function which will look from one worksheet to another, and return the number of occurences of a certain status against a particular type. I wrote the following function with a view to doing this, but all I get is a VALUE error. I tried to use the Debug.Print but nothing appears in the Immediate window when I get there and, infact, I can't look at the VBA window until after the calculation has finished in the worksheet. I tried inserting a breakpoint after the Debug.Print statement but even with that I don't get anything displayed in the Immediate window. I am really having difficulty with understanding how to use the Debug facility properly at all. I have a thought that the basics of my problem is how I am calling INDIRECT but I may be wrong in that too. ************ Function Type_Prospectives(fFR As Integer, fLR As Integer, fWSName As String, _ fCAT As String, fTS As String) As Integer Type_Prospectives = 0 Dim I As Integer Dim IV1 As String Dim IV2 As String Dim TV1 As String Dim TV2 As String For I = fFR To fLR IV1 = fWSName & "!R" & (Str(I)) & "C4" IV2 = fWSName & "!R" & (Str(I)) & "C5" TV1 = Application.WorksheetFunction.INDIRECT(IV1, False) TV2 = Application.WorksheetFunction.INDIRECT(IV2, False) Debug.Print I, IV1, IV2, TV1, TV2 If TV1 = fCAT Then If TV2 = fTS Then Type_Prospectives = Type_Prospectives + 1 End If End If Next I ************ Typical call to function (in full spreadsheet) reads as: =Type_Prospectives(6,145,Inventory,"Found","A") Three are three potential values of fCAT, and eleven of fTS, so I am looking to call this 33 times in the full spreadsheet. One other strange quirk that I would welcome insight on is the fact that when I tried to have the function statement as a single line, VBA would not even let me save - the line just went red and I could not proceed. When I inserted the second line and used the underscore continuation character, it was all OK, at least as regards the Function statement anyway. Any advice welcome, please. Best regards Philip -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to call user defined function (UDF) from custom menu in Excel2003? | Excel Programming | |||
Call user defined function in an add-in | Excel Programming | |||
How to call a function of an user defined DLL from Excel? | Excel Programming | |||
Excel "Insert Formula" dialog always call my user defined function | Excel Programming | |||
Call GoalSeek from a user-defined-function | Excel Programming |