Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

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

    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:
    Function JunkTestAddress(rngA As Range)
    ' Usage: =JunkTestAddress(rngA)
    ' Where rngA = Named Range, eg Data Table with range $A$3:$C$12
    Dim Msg, Button, Title, Response As String
    JunkTestAddress = Application.WorksheetFunction.Address (1,Application.WorksheetFunction.Column(rngA), 4)
    Title = "JunkTestAddress Function..."
    Button = vbExclamation
    Msg = "rngA: " & rngA & vbCrLf & _
    "JunkTest Address: " & JunkTestAddress
    Response = MsgBox(Msg, Button, Title)
    End Function
    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:
    20121116 JunkTestAddress Data Table.JPG

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

    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
    Last edited by BygAuldByrd; 2012-11-16 at 02:23.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2012-11-17)

  5. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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:
    Function JunkTestAddress(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
    
    rngPassed = rngA.Address(, xlA1)
    
    JunkTestAddress = Application.WorksheetFunction.Address(1, Application.Worksheet.Column(rngPassed), 4)
    
    End Function
    
    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

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    B-A-B,

    VBA does not like the Application.Worksheet.Column!
    WorksheetColumnError.JPG
    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. $B$3:$D$12 to extract the column.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    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.
    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

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2012-11-18)

  9. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Obviously not retired

    You've solved my problem again.

    Many thanks

    Cheers

    BygAuldByrd

  10. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    Or:
    Code:
    JunkTestAddress = Application.Cells(1, rngA.Column).Address(False, False, xlA1)
    Regards,
    Rory
    Microsoft MVP - Excel.

  11. The Following User Says Thank You to rory For This Useful Post:

    BygAuldByrd (2012-11-19)

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Rory,

    Very nice!

    B-A-B,

    To get a Fixed Reference use.
    Code:
    JunkTestAddress = Application.Cells(1, rngA.Column).Address(,, xlA1)
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. #9
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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
    Last edited by BygAuldByrd; 2012-11-19 at 20:20.

  14. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    B-A-B,

    You're welcome and thanks for posting your solutions to help others.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  15. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts
    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
    Regards,
    Rory
    Microsoft MVP - Excel.

  16. The Following User Says Thank You to rory For This Useful Post:

    BygAuldByrd (2012-11-20)

  17. #12
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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

    Cheers

    BygAuldBYrd

  18. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    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
    FunctionResultsTable.JPG
    Rory, Couldn't have done it with out your code
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  19. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2012-11-20)

  20. #14
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Guys,

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


    Love your even further simplified version RetiredGeek




    BygAuldByrd


  21. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •