• Excel VBA: How to manipulate a cell/range address within a UDF

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Excel VBA: How to manipulate a cell/range address within a UDF

    Author
    Topic
    #486360

    Hi All,

    I’m try to transfer a range name or cell address into a function so that it can be manipulated within the function.

    My simplified test function is shown below:

    Code:
    [INDENT][COLOR=#0000ff]Function JunkTestAddress(rngA As Range)[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]
    [/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]’   Usage: =JunkTestAddress(rngA)[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]’           Where rngA = Named Range, eg Data Table with range $A$3:$C$12[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]
    [/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]Dim Msg, Button, Title, Response As String[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]
    [/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]JunkTestAddress = Application.WorksheetFunction.Address (1,Application.WorksheetFunction.Column(rngA), 4)[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]
    [/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]    Title = “JunkTestAddress Function…”[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]    Button = vbExclamation[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]    Msg = “rngA: ” & rngA & vbCrLf & _[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]            “JunkTest Address: ” & JunkTestAddress[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]    Response = MsgBox(Msg, Button, Title)[/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]
    [/COLOR][/INDENT]
    [INDENT][COLOR=#0000ff]End Function[/COLOR][/INDENT]
    

    However it appears that the putting the Named Range or a cell address as the function parameter transfers the value contained in the cell to the function, rather than the cell/named range address, consequently the result returned by the function is #VALUE!

    Here is my named range:
    32381-20121116-JunkTestAddress-Data-Table

    Commenting out the JunkTestAddress line results in the MessageBox displaying the value contained in cell referenced by rngA:
    32382-20121116-JunkTestAddress-Dialogue

    The result I am looking for is rngA = $C$3.

    How can I transfer the function cell/range address parameter into the function so I can manipulate it? (Putting “” around rngA parameter when using the function and treating it as a string does not appear to resolve the issue.)

    All assistance in resolving this will be appreciated.

    Thanks in anticipation

    BygAuldByrd

    Viewing 8 reply threads
    Author
    Replies
    • #1357419

      B-A-B,

      This code returns the range as a string.

      Code:
      Function RangeAddress(rngPassed As Range) As String
      
           'Calling Sequence: =RangeAddress(DataTable)
      
           RangeAddress = rngPassed.Address(, xlA1)
         
      End Function
      

      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1357667

        Hi RetiredGeek,

        Thank you for the RangeAddress(rngPassed) function, it works just fine as a stand alone function.

        However, when I use the technique in my JunkTestAddress function as shown below all I get is #VALUE!😡

        Code:
        [SIZE=3]Function JunkTestAddress(rngA As Range) As String
        
        ‘ Usage: =JunkTestAddress(rngA)
        ‘ Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
        
        [/SIZE]Dim rngPassed As String[SIZE=3]
        
        rngPassed = rngA.Address(, xlA1)
        
        JunkTestAddress = Application.WorksheetFunction.Address(1, Application.Worksheet.Column(rngPassed), 4)
        
        End Function
        [/SIZE]

        What I am expecting from my function is the address of the first column in the table, in the example shown the result should be $B$1.

        If I put the formula = Address(1, Column(DataTable), 4) or = Address(1, Column($B$3:$D$12), 4) into an Excel WorkSheet I get the expected result.

        Any idea why I’m not getting the expected result? This is driving me nuts!

        Your input is much appreciated

        Cheers

        BygAuldByrd

    • #1357736

      B-A-B,

      VBA does not like the Application.Worksheet.Column!
      32415-WorksheetColumnError
      It doesn’t seem to care if I use the rngA or rngPassed values.

      The only thing I can think of right now is to write code to breakdown the string reference e.g. [noparse]$B$3:$D$12[/noparse] to extract the column. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1357745

      B-A-B,

      Seems like a lot of WorksheetFunctions cause this error if tried in VBA.
      Here’s some code that will return an Absolute reference using the 1st Column of a range. I assumed from your code that you always wanted row 1, however the code could be adjusted for another row or the row could be calculated or passed. :cheers:

      Code:
      Public Function zJunkTestAddress(rngA As Range) As String
      
      ' Usage: =JunkTestAddress(rngA)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
      
         Dim rngPassed As String
         Dim iPos      As Integer
      
         rngPassed = rngA.Address(, xlA1)
         rngPassed = Right(rngPassed, Len(rngPassed) - 1)
         iPos = InStr(1, rngPassed, "$")
         rngPassed = Left(rngPassed, iPos - 1)
         
         zJunkTestAddress = "$" & rngPassed & "$1"
         
      End Function
      

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1357827

        Hi RetiredGeek,

        Obviously not retired:p

        You’ve solved my problem again.

        Many thanks

        Cheers

        BygAuldByrd

    • #1357832

      Or:

      Code:
      JunkTestAddress = Application.Cells(1, rngA.Column).Address(False, False, xlA1)
    • #1357858

      Rory,

      Very nice!

      B-A-B,

      To get a Fixed Reference use.

      Code:
      JunkTestAddress = Application.Cells(1, rngA.Column).Address(,, xlA1)

      :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1357921

        Hi Rory,

        Thanks for your tip, it’s indirectly assisted me in solving a couple of other issues I had.

        Your tip has given me the path to finding the 1st column, Last column, 1st Row and Last Row strings for a defined Named Range.

        For those reading this thread and interested, here are my function code samples:

        Code:
        Public Function Table1stColumn(rngA As Range) As String
        
        ‘ Usage: =Table1stColumn(rngA)
        ‘ Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
        
        
        Dim rngPassed As String
        Dim iPos As Integer
        
        
        rngPassed = rngA.Address(, xlA1)
        rngPassed = Right(rngPassed, Len(rngPassed) – 1)
        iPos = InStr(1, rngPassed, “$”)
        rngPassed = Left(rngPassed, iPos – 1)
           
        Table1stColumn = “$” & rngPassed
        ‘   Table1stColumn = rngPassed
        
        
        End Function
        
        ‘   *******************
        
        
        Public Function Table1stRow(rngA As Range) As String
        
        
        ‘ Usage: =Table1stRow(rngA)
        ‘ Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
        
        
        Dim rngPassed As String
        Dim iPos As Integer
           
        rngPassed = Application.Cells(rngA.Row, rngA.Column).Address(, xlA1)
        rngPassed = Right(rngPassed, Len(rngPassed) – 1)
        iPos = InStr(1, rngPassed, “$”)
        rngPassed = Right(rngPassed, iPos – 1)
        Table1stRow = “$” & rngPassed
        ‘   Table1stRow = rngPassed
           
        End Function
        
        ‘   *******************
        
        
        Public Function TableLastRow(rngA As Range) As String
        
        
        ‘ Usage: =Table1stRow(rngA)
        ‘ Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
        
        
        Dim rngPassed As String
        Dim iPos As Integer
           
        rngPassed = rngA.Address(, xlA1)
        iPos = InStr(1, rngPassed, “:”)
        rngPassed = Right(rngPassed, iPos – 2)
        TableLastRow = “$” & rngPassed
        ‘   TableLastRow = rngPassed
           
        End Function
        
        ‘   *******************
        
        
        Public Function TableLastColumn(rngA As Range) As String
        
        
        ‘ Usage: =TableLastRow(rngA)
        ‘ Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
        
        
        Dim rngPassed As String
        Dim iPos As Integer
           
        rngPassed = rngA.Address(, xlA1)
        iPos = InStr(1, rngPassed, “:”)
        rngPassed = Right(rngPassed, iPos + 1)
        rngPassed = Right(rngPassed, Len(rngPassed) – 1)
        iPos = InStr(2, rngPassed, “$”)
        rngPassed = Left(rngPassed, iPos – 1)
        TableLastColumn = “$” & rngPassed
        ‘   TableLastColumn = rngPassed
           
        End Function
        
        
        

        Thanks again to Rory and RetiredGeek for their valuable assistance.

        Cheers

        BygAuldByrd

    • #1357929

      B-A-B,

      You’re welcome and thanks for posting your solutions to help others. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1357966

      I’m not sure you fully got my point 😉

      Code:
      Public Function Table1stColumn(rngA As Range) As String
      
      ' Usage: =Table1stColumn(rngA)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
      
      
         Table1stColumn = "$" & Split(rngA.Cells(1).Address, "$")(1)
      End Function
      
      
      Public Function Table1stRow(rngA As Range) As String
      
      
      ' Usage: =Table1stRow(rngA)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
      
      
         Table1stRow = "$" & rngA.Row
      End Function
      Public Function TableLastRow(rngA As Range) As String
      
      
      ' Usage: =Table1stRow(rngA)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
         With rngA
            TableLastRow = "$" & .Cells(.Count).Row
         End With
      End Function
      
      
      Public Function TableLastColumn(rngA As Range) As String
      
      
      ' Usage: =TableLastRow(rngA)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
      
      
         With rngA
            TableLastColumn = "$" & Split(.Cells(.Count).Address, "$")(1)
         End With
      End Function
      
      • #1358096

        Hi Rory,

        Many thanks. That’s an even better solution that mine:) I’ve now scrapped my versions

        Now all I have to do is understand exactly how your functions work:confused:

        Cheers

        BygAuldBYrd

    • #1358112

      B-A-B,

      Or if you want a single function?

      Code:
      Public Function TableInfo(rngTable As Range, iItem As Integer, bAbs As Boolean) As String
      
      ' Usage: =TableInfo(rngA,iItem,iAbs)
      ' Where rngA = A Named Range, eg DataTable with range $B$3:$D$12
      '       iItem = 1 - Table First Column
      '               2 = Table First Row
      '               3 = Table Last Column
      '               4 = Table Last Row
      '       bAbs  = True returns Absolute reference ($)
      '               False returns Relative reference
      
         With rngTable
            TableInfo = IIf(bAbs, "$", "") & Split(Replace(.Cells.Address, ":", ""), "$")(iItem)
         End With
      
      End Function
      

      32440-FunctionResultsTable
      Rory, Couldn’t have done it with out your code :clapping: :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      • #1358131

        Hi Guys,

        Nothing like a little competition to come up with some excellent code:clapping:

        Love your even further simplified version RetiredGeek:bananas:

        :cheers:

        BygAuldByrd

    • #1358161

      B-A-B,

      Not really competition more like learning from each other’s experience and then trying to learn more. These products are so complex that nobody knows it all and we all have a different perspective on things so everyone has something to contribute. That’s what I think the Lounge is all about. :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    Viewing 8 reply threads
    Reply To: Excel VBA: How to manipulate a cell/range address within a UDF

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: