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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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


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

    BygAuldByrd (2012-11-17)

  4. #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

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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


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


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

    BygAuldByrd (2012-11-18)

  8. #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

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

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

    BygAuldByrd (2012-11-19)

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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


  12. #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.

  13. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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


  14. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 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.

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

    BygAuldByrd (2012-11-20)

  16. #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

  17. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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


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

    BygAuldByrd (2012-11-20)

  19. #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


  20. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,243
    Thanks
    202
    Thanked 796 Times in 729 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
  •