removing the function call using vba

Question

I am trying to remove the function call after a value is returned.

for example:

function testFunc(x as integer)
testfunc = x
end function

after calling this function I would like the cell value where the function was called to equal x and not =testFunc(x). Is this possible or do the limits placed on functions restrict this type of action

This is then being used as a formula and just like a formula you can always copy and paste only the value, after the formula returns a value. — Scott Craner, Feb 02 '16 at 18:28
But just to be clear this cannot be done in the function itself. — Scott Craner, Feb 02 '16 at 18:35
I am aware of the option to copy and paste just the value, but I am looking for a solution is handled within the function. I am trying to make the function as simple to use as possible. thank you for the input though — M. Bonini, Feb 02 '16 at 18:51

score 0 · Answer 1 · answered Feb 02 '16 at 20:30

0

Try this short macro:

End Function
Sub FunctionReplacer()
   Dim r As Range

   For Each r In ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
      If InStr(1, LCase(r.Formula), "testfunc") > 0 Then
         r.Value = r.Value
      End If
   Next r
End Sub

answered Feb 02 '16 at 20:30

Gary's Student

95,722
10
59
99

while this works well it doesn't suit all my need. which is in no way your fault because I wasn't very clear. I was looking for something that would work within the function so that extra steps would not be necessary. – M. Bonini Feb 02 '16 at 22:01

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

I was able to create a solution (not a very pretty or elegant one) using a workaround I found on a different question

I used the code the link provided to allow myself to copy and paste simply the value using pastespecial

edited May 23 '17 at 11:45

Community

1
1

answered Feb 02 '16 at 22:05

M. Bonini

1

removing the function call using vba

2 Answers2