none
Refering to a single cell in a named range in a function RRS feed

  • Question

  • Sheet1 has a column named Age, hence Age is a range. 

    In Sheet2 I can select a cell in any row, enter the formula
            =Age
    and the value of the value from the corresponding row in the Age column.

    How do I do the same thing in a VBA function?  Specifically, I want to pass the function a min and a max and determine if Age is between min and max.


    • Edited by DiDoDe Friday, August 11, 2017 2:47 AM typo
    Friday, August 11, 2017 2:43 AM

Answers

  • D,
    re: age function
    Possibly...
    '---
    Public Function OLDENOUGH(ByRef First As Variant, ByRef Last As Variant) As Variant
    On Error GoTo NotLegal
    Dim X As Variant

    If VBA.TypeName(Application.Caller) = "Range" Then
      Set X = Application.Caller
      If Range("Age")(X.Row, 1).Value >= First And Range("Age")(X.Row, 1).Value <= Last Then
         OLDENOUGH = True
      Else
         OLDENOUGH = False
      End If
    End If
    Exit Function
    NotLegal:
    OLDENOUGH = "you are under arrest"
    End Function

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

    • Marked as answer by DiDoDe Friday, August 11, 2017 5:24 PM
    • Edited by James Cone Sunday, August 13, 2017 11:37 AM
    Friday, August 11, 2017 3:02 PM

All replies

  • D,
    re: age function
    Possibly...
    '---
    Public Function OLDENOUGH(ByRef First As Variant, ByRef Last As Variant) As Variant
    On Error GoTo NotLegal
    Dim X As Variant

    If VBA.TypeName(Application.Caller) = "Range" Then
      Set X = Application.Caller
      If Range("Age")(X.Row, 1).Value >= First And Range("Age")(X.Row, 1).Value <= Last Then
         OLDENOUGH = True
      Else
         OLDENOUGH = False
      End If
    End If
    Exit Function
    NotLegal:
    OLDENOUGH = "you are under arrest"
    End Function

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

    • Marked as answer by DiDoDe Friday, August 11, 2017 5:24 PM
    • Edited by James Cone Sunday, August 13, 2017 11:37 AM
    Friday, August 11, 2017 3:02 PM
  • Thanks! I will play with that a bit.
    Friday, August 11, 2017 3:19 PM