Call to INDIRECT within user defined function

[Philip Mark Hunt]

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]

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]

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