help needed to convert a sub to a function in VBA

[mxpmxp]

Hi I'm a newbie with VBA. I found this macropod's contribution in the Forum and I have tried to convert it to a function but for some reason I'm not getting the same result. I would appreciate your help.

The original code:

Sub GetRefs()
Dim MyRange As Range, strFormula As String, strVal As String
With ActiveCell
  strFormula = .Formula
  For Each MyRange In .Precedents.Cells
    With MyRange
      strVal = """" & Range(.Address).Value & """"
      strFormula = Replace(strFormula, .Address, strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
    End With
  Next
  MsgBox .Formula & vbCr & strFormula
End With
End Sub

My slightly modified code:

Function GetRef(refCell As Range)
Dim MyRange As Range, strFormula As String, strVal As String
'With ActiveCell
With refCell
  strFormula = .Formula
  On Error Resume Next
  For Each MyRange In .Precedents.Cells
    With MyRange
      strVal = """" & Range(.Address).Value & """"
      strFormula = Replace(strFormula, .Address, strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal)
      strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal)
    End With
  Next
  GetRef = strFormula
End With
End Function

 

[SkipVought]

hi,

Where does your function reside?

How are you using it?

BTW, it works. Here's the formula I have in C2...
[tt]
=A2+A1
[/tt]
Here's the FUNCTION FORMULA in D2
[tt]
=GetRef(C2)
[/tt]
Here's the function result in D2
[tt]
=A2+A1
[/tt]





Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Sub may or may not accept parameter(s), but it does NOT return anything.

Function may or may not accept parameter(s), but it DOES return a value.

So you need to specify what your Function will return, for example:

Function GetRef(refCell As Range) [blue]As String[/blue]

...

GetRef = "ABcd XyZ"

End Function

If you do not have [tt]As String[/tt] (for example) your function will return a Variant.

Have fun.

---- Andy

 

[mxpmxp]

Hey, thanks for your responses.

Andrzejek, yes, I missed to specify the result type, I corrected it, but does not improve the behaviour.

SkipVought, apparently it works pretty good but the right result should be "(content of A1)"+"(content of A2)"

for example, if A1 = 7 and A2 = 44 then it should return "7"+"44"

(try using the macro instead of the function)

What I'm looking for is to have the ability to use the function programatically with any cell containig a formula.

 

[combo]

There is no access to precedents in UDF, one gets the cell asked for its precedents instead. A simple test with:

Function FirstPrecedentTest(refCell As Range) As String
    FirstPrecedentTest = refCell.DirectPrecedents.Cells(1).Address
End Function

returns refCell address, whatever is in this cell.

combo

 

[mxpmxp]

Thanks, is that simple! :-(