Address of Excel.Range & Range

R

r

Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r
 
J

JLGWhiz

You are one over the limit. If you remove one cell address from the value
of s, then it works.
 
P

Peter T

Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T
 
R

r

non è un problema per me risolvere l'inconveniente ...
la mia era solo una segnalazione ...
sapere se eravate a conoscenza.

Sub test()
Dim rng As Excel.Range
Dim s As String

s = "$C$1:$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49:$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79:$A$81,$A$83,A90,a91,a95:a97"

Set rng = Range2(s)
If TypeName(rng) = "Range" Then
rng.Select
Else
MsgBox "Nessun range"
End If
End Sub


Function Range2(s As String) As Range
Dim rng As Excel.Range
Dim RE As Object
Dim v
Set RE = CreateObject("vbscript.regexp")
RE.Global = True
RE.Pattern = "\$?[A-z]+\$?\d+:)\$?[A-z]+\$?\d+)?"
If RE.test(s) Then
Set rng = Evaluate(CStr(RE.Execute(s)(0)))
For Each v In RE.Execute(s)
Set rng = Application.Union(rng, Evaluate(CStr(v)))
Next
End If

If TypeName(rng) = "Range" Then
Set Range2 = rng
End If
End Function

saluti
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


Peter T said:
Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T


r said:
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r
Click to expand...
Click to expand...
 
R

r

sorry I forgot to translate ...

is not a problem for me to solve the problem ...
I was just a warning ...
whether you were aware

r said:
non è un problema per me risolvere l'inconveniente ...
la mia era solo una segnalazione ...
sapere se eravate a conoscenza.

Sub test()
Dim rng As Excel.Range
Dim s As String

s = "$C$1:$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49:$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79:$A$81,$A$83,A90,a91,a95:a97"

Set rng = Range2(s)
If TypeName(rng) = "Range" Then
rng.Select
Else
MsgBox "Nessun range"
End If
End Sub


Function Range2(s As String) As Range
Dim rng As Excel.Range
Dim RE As Object
Dim v
Set RE = CreateObject("vbscript.regexp")
RE.Global = True
RE.Pattern = "\$?[A-z]+\$?\d+:)\$?[A-z]+\$?\d+)?"
If RE.test(s) Then
Set rng = Evaluate(CStr(RE.Execute(s)(0)))
For Each v In RE.Execute(s)
Set rng = Application.Union(rng, Evaluate(CStr(v)))
Next
End If

If TypeName(rng) = "Range" Then
Set Range2 = rng
End If
End Function

saluti
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


Peter T said:
Public Sub m4()
Dim i As Long, arr
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"

s = Replace(s, "$", "")

If Len(s) <=255 Then
Set rng = Range(s)
Else
arr = Split(s, ",")
Set rng = Range(arr(0))
For i = 1 To UBound(arr)
Set rng = Union(rng, Range(arr(i)))
Next
End If
rng.Select

End Sub

Regards,
Peter T


r said:
Hello,
Reflected these exceptions on the properties address and range, you are
aware of the problem?

Option Explicit
'ATTENZIONE!
'Comportamento non documentato di
'Address riferito a Range
'Rng.Address restituisce fino a 257
'caratteri

Sub test_Address()
Dim rng As Excel.Range
Dim i As Long
Dim s As String
Set rng = [a1]
For i = 3 To 200 Step 2
Set rng = Application.Union(rng, Evaluate("a" & i))
Next
rng.Select
[a1] = rng.Address
Debug.Print Len(rng.Address)
End Sub

Public Sub m()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "C$1,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83,$A$85"
Debug.Print Len(s) '257
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub



Public Sub m2()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81,$A$83"
Debug.Print Len(s) '256
Set rng = Range(s) 'error
rng.Select

Set rng = Nothing

End Sub

Public Sub m3()
Dim rng As Range
Dim s As String

Set rng = Range("A1")
s = "$C$5,$C$1,$C$3,$A$1,$A$3,$A$5,$A$7,$A$9,$A$11,$A$13,$A$15," _
& "$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29," _
& "$A$31,$A$33,$A$35,$A$37,$A$39,$A$41,$A$43," _
& "$A$45,$A$47,$A$49,$A$51,$A$53,$A$55,$A$57," _
& "$A$59,$A$61,$A$63,$A$65,$A$67,$A$69,$A$71," _
& "$A$73,$A$75,$A$77,$A$79,$A$81"
Debug.Print Len(s) '255
Set rng = Range(s) 'ok
rng.Select

Set rng = Nothing

End Sub

regards
r
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate Stanine Values 6
Mail a different file(s) to each person in a range 0
Combine Ranges to Draw Borders Issue 3
for each in a range 1
CurrentRegion - Usual Behavior? 2
Unexplained failure of wdFindStop at end of search 0
Method 'Range' of object '_Worsheet' failed in Union of 3 Ranges 7
filter and delete 2

Top