none
Simple Excel VBA question RRS feed

  • Question

  • OK, while I'm not a newbie to VBA, I've never tried to set a cell from within a VBA function.  Based on what I've read, this should work:

    Public Function SetCell(value As Integer) As Boolean
        SetCell = True
        Range("A1").value = value
    End Function

    The call to this function is in cell A2:

    =SetCell(1)

    When I run this, I ALWAYS get "#VALUE!", and A1 doesn't get set.  If I set a breakpoint on the first line of the function, the value being passed in is 1.

    Anyone have any ideas?

    Wednesday, March 25, 2015 7:54 PM

Answers

  • Re:  user defined function (UDF)

    A function called from a worksheet can only return a value to the cell containing the function.
    This limitation was instituted many years ago because of security concerns.

    Give this modification a try...
    Public Function SetCell(ByRef Ivalue As Double) As Double
      SetCell = Ivalue * 2
    End Function
    '---
    Note that "value" was changed to Ivalue - Vba already has claim to "value".
    Also, "Double" is the default data type for numeric cells.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by tlj286 Thursday, March 26, 2015 12:46 PM
    • Edited by James Cone Monday, October 31, 2016 1:03 PM
    Thursday, March 26, 2015 2:21 AM

All replies

  • Re:  user defined function (UDF)

    A function called from a worksheet can only return a value to the cell containing the function.
    This limitation was instituted many years ago because of security concerns.

    Give this modification a try...
    Public Function SetCell(ByRef Ivalue As Double) As Double
      SetCell = Ivalue * 2
    End Function
    '---
    Note that "value" was changed to Ivalue - Vba already has claim to "value".
    Also, "Double" is the default data type for numeric cells.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by tlj286 Thursday, March 26, 2015 12:46 PM
    • Edited by James Cone Monday, October 31, 2016 1:03 PM
    Thursday, March 26, 2015 2:21 AM
  • Thanks.  Unfortunately, that still leaves me in a quandary. I'll try some stuff and if I can't solve it, I'll try another question.

    Thursday, March 26, 2015 12:51 PM