Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding external cell dependents works in a Sub() but not a UDF.
In Excel 2002 SP 3, I need a UDF that displays a concatenated string
of the texts of the external dependents of a given cell. So if say Sheet1!A1 has 3 external dependents--Sheet2!A1 (text “cat”), Sheet2!A2 (“dog”) and Sheet3!A1 (“bird”), then formula “=MyUDF(Sheet1! A1)” should return “catdogbird”. But I find no posts using .NavigateArrows in a Function(). All examples are Sub()’s. True to that, this code: Do Set D = .NavigateArrow(False, 1, i) If Not (D.Worksheet Is SourceRange.Worksheet) Then Result = Result & D.Text End If i = i + 1 Loop Until <Whatever works perfectly for me (cycles thru all externals) in a Sub(), but fails in a Function(). Can it therefore not be done in a UDF? If not, do any of you Knights of the Web have a workaround? Thanks very much. *** [P.S. The first post on: http://groups.google.com/group/micro...ba3a450db971a2 intriguingly is in a Function(). But it self-defeats because its first line: If TypeOf Application.Caller Is Range Then GoTo theExit is always true (at least for me), hence his code never executes. And anyway, he uses the same .NavigateArrows logic as above, and if you comment out the above line it fails as expected.] *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding external cell dependents works in a Sub() but not a UDF.
I didn't use the NavigateArrows method (I just don't know what it
does) but this works for me: Public Function ConcatenateDependantCellsTexts(TheMasterCell As Range) As String Dim v As Range For Each v In TheMasterCell.Dependents ConcatenateDependantCellsTexts = ConcatenateDependantCellsTexts & v.Text Next v End Function But!! - keep in mind that you can't really use it in a cell as a function because it would cause a circular reference - i.e., just by entering this function you'd increase the MasterCell's dependatns by 1 (i.e, the cell that you enter the function into will also become a dependent of the mastercell. Also, as far as i know the only real difference between sub and function is that a function returns a value whereas sub doesn't. On Jun 24, 12:26*am, wrote: In Excel 2002 SP 3, I need a UDF that displays a concatenated string of the texts of the external dependents of a given cell. So if say Sheet1!A1 has 3 external dependents--Sheet2!A1 (text “cat”), Sheet2!A2 (“dog”) and Sheet3!A1 (“bird”), then formula “=MyUDF(Sheet1! A1)” should return “catdogbird”. But I find no posts using .NavigateArrows in a Function(). All examples are Sub()’s. True to that, this code: * * Do * * * Set D = .NavigateArrow(False, 1, i) * * * If Not (D.Worksheet Is SourceRange.Worksheet) Then * * * * Result = Result & D.Text * * * End If * * * i = i + 1 * * Loop Until <Whatever works perfectly for me (cycles thru all externals) in a Sub(), but fails in a Function(). Can it therefore not be done in a UDF? If not, do any of you Knights of the Web have a workaround? Thanks very much. *** [P.S. The first post on: http://groups.google.com/group/micro...rogramming/bro... intriguingly is in a Function(). But it self-defeats because its first line: If TypeOf Application.Caller Is Range Then GoTo theExit is always true (at least for me), hence his code never executes. And anyway, he uses the same .NavigateArrows logic as above, and if you comment out the above line it fails as expected.] *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding external cell dependents works in a Sub() but not a UDF.
AB:
Thanks much for reply. *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to traverse cell.Dependents | Excel Programming | |||
external data works in 2003 not in 2000 | Excel Programming | |||
External link only works for numbers -- for text values, #N/A is displayed unless linked file is open | Links and Linking in Excel | |||
Checking cell for Dependents | Excel Programming | |||
Checking cell for Dependents | Excel Programming |