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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi RetiredGeek,

Obviously not retired:p

You’ve solved my problem again.

Many thanks

Cheers

BygAuldByrd

Reply | Quote

Or:

Code:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote