P
Philip Mark Hunt
Apologies to anyone who has already read this in Functions. I realised after
a few daysa of no response that I had posted in the wrong stream.
Excel 2007 query
Error received in VBA window (Debug) = <Out of Context>
Error on Spreadsheet = #VALUE#
I have 'formatted' this function in much the same way as my twenty or more
other functions in this spreadsheet, yet I end up with Out of Context. I
have tried various approaches including an 'On Calculate' called sub-routine,
but all to no avail. The code for the function is stored in Module1 of an
xlam, which is where all my other working functions are also located.
Please Help.
Cut down summary of workbook
Worksheet 1 (Name = "Inventory")
Column C (3) – Alphabetic List of Titles, e.g.
AIR AND WATER
AIRCRAFT
INSECTS
ROBOTS
SOUND
SUPERSONIC JETS
VIKINGS
Column K (11) – Function built summary of holdings, examples of end values
CG
None Held
CGIJ
L
G
None Held
C
Worksheet 7 (Name = "None Held")
Row 1 – column values separated by semi-colons for the purposes of this
extracted summary -
"Title";5;6;163;19
Row 2 and subsequent 20 rows, call to function that is not working
Call text is
=Locate_None_Helds(B1+1,$D$1,ROW(),$E$1,SHEETNAME(1),SHEETNAME())
Text of Function =
Function Locate_None_Helds(fFR As Long, fLR As Long, fCR As Long, fCNH As
Long, _
fWSName1 As String, fWSName2 As String) As String
Application.Volatile
Locate_None_Helds = ""
Dim tStatus As String
Dim fTitle As String
Dim I As Long
Dim J As Long
Dim K As Long
K = fCNH + 1
J = fCR
If fCR > K Then
Locate_None_Helds = ""
Else
For I = fFR To fLR
tStatus = Worksheets(fWSName1).Cells(I, 11).Value
fTitle = Worksheets(fWSName1).Cells(I, 3).Value
If tStatus = "None Held" Then
Worksheets(fWSName2).Cells(J, 2) = I
Locate_None_Helds = fTitle
Exit For
End If
Next I
End If
End Function
Every time it gets to – Locate_None_Helds = fTitle
the error <Out of Context> arises, and I just don't understand why.
In my many other functions I use exactly the same idea of assigning a value
to the name of the function, and that assigned value is what ends up in the
calling cell, but on this occasion it won't work and I just don't understand
why.
Please help.
With many thanks in anticipation
Philip, Perth, Western Australia
a few daysa of no response that I had posted in the wrong stream.
Excel 2007 query
Error received in VBA window (Debug) = <Out of Context>
Error on Spreadsheet = #VALUE#
I have 'formatted' this function in much the same way as my twenty or more
other functions in this spreadsheet, yet I end up with Out of Context. I
have tried various approaches including an 'On Calculate' called sub-routine,
but all to no avail. The code for the function is stored in Module1 of an
xlam, which is where all my other working functions are also located.
Please Help.
Cut down summary of workbook
Worksheet 1 (Name = "Inventory")
Column C (3) – Alphabetic List of Titles, e.g.
AIR AND WATER
AIRCRAFT
INSECTS
ROBOTS
SOUND
SUPERSONIC JETS
VIKINGS
Column K (11) – Function built summary of holdings, examples of end values
CG
None Held
CGIJ
L
G
None Held
C
Worksheet 7 (Name = "None Held")
Row 1 – column values separated by semi-colons for the purposes of this
extracted summary -
"Title";5;6;163;19
Row 2 and subsequent 20 rows, call to function that is not working
Call text is
=Locate_None_Helds(B1+1,$D$1,ROW(),$E$1,SHEETNAME(1),SHEETNAME())
Text of Function =
Function Locate_None_Helds(fFR As Long, fLR As Long, fCR As Long, fCNH As
Long, _
fWSName1 As String, fWSName2 As String) As String
Application.Volatile
Locate_None_Helds = ""
Dim tStatus As String
Dim fTitle As String
Dim I As Long
Dim J As Long
Dim K As Long
K = fCNH + 1
J = fCR
If fCR > K Then
Locate_None_Helds = ""
Else
For I = fFR To fLR
tStatus = Worksheets(fWSName1).Cells(I, 11).Value
fTitle = Worksheets(fWSName1).Cells(I, 3).Value
If tStatus = "None Held" Then
Worksheets(fWSName2).Cells(J, 2) = I
Locate_None_Helds = fTitle
Exit For
End If
Next I
End If
End Function
Every time it gets to – Locate_None_Helds = fTitle
the error <Out of Context> arises, and I just don't understand why.
In my many other functions I use exactly the same idea of assigning a value
to the name of the function, and that assigned value is what ends up in the
calling cell, but on this occasion it won't work and I just don't understand
why.
Please help.
With many thanks in anticipation
Philip, Perth, Western Australia