valor - ¿Cómo obtengo la dirección de un rango, incluido el nombre de la hoja de trabajo, pero no el nombre del libro, en Excel VBA?

seleccionar rango variable vba

Question

Si tengo un objeto Range, por ejemplo, digamos que se refiere a la celda A1 en una hoja de trabajo llamada Book1 . Entonces sé que al llamar a Address() obtendré una referencia local simple: $A$1 . Sé que también se puede llamar como Address(External:=True) para obtener una referencia que incluya el nombre del libro de trabajo y el nombre de la hoja de trabajo: [Book1]Sheet1!$A$1 .

Lo que quiero es obtener una dirección que incluya el nombre de la hoja, pero no el nombre del libro. Realmente no quiero llamar a Address(External:=True) y trato de quitar el nombre del libro de trabajo con funciones de cadena. ¿Hay alguna llamada que pueda hacer en el rango para obtener Sheet1!$A$1 ?

Answer 1

¿Por qué no simplemente devuelve el nombre de la hoja de cálculo con la dirección = cell.Worksheet.Name luego puede concatenar la dirección de nuevo como esta dirección = cell.Worksheet.Name & "!" y cell.Address

Answer 2

.Address (,,, TRUE) (Muestra la dirección externa, la dirección completa) :-)

Answer 3

Ben tiene razón. Tampoco puedo pensar en ninguna forma de hacer esto. Sugeriría el método que recomienda Ben o lo siguiente para quitar el nombre del Libro de trabajo.

Dim cell As Range
Dim address As String
Set cell = Worksheets(1).Cells.Range("A1")
address = cell.address(External:=True)
address = Right(address, Len(address) - InStr(1, address, "]"))

Answer 4

Encontré que lo siguiente funcionó para mí en una función definida por el usuario que creé. Concatené la referencia de rango de celda y el nombre de la hoja de trabajo como una cadena y luego la utilicé en una declaración Evaluar (estaba usando Evaluar en Subproducto).

Por ejemplo:

Function SumRange(RangeName as range)   

Dim strCellRef, strSheetName, strRngName As String

strCellRef = RangeName.Address                 
strSheetName = RangeName.Worksheet.Name & "!" 
strRngName = strSheetName & strCellRef        

Luego consulte strRngName en el resto de su código.

Answer 5

Es posible que necesite escribir código que maneje un rango con áreas múltiples, que esto hace:

Public Function GetAddressWithSheetname(Range As Range, Optional blnBuildAddressForNamedRangeValue As Boolean = False) As String

    Const Seperator As String = ","

    Dim WorksheetName As String
    Dim TheAddress As String
    Dim Areas As Areas
    Dim Area As Range

    WorksheetName = "''" & Range.Worksheet.Name & "''"

    For Each Area In Range.Areas
''           =''Sheet 1''!$H$8:$H$15,''Sheet 1''!$C$12:$J$12
        TheAddress = TheAddress & WorksheetName & "!" & Area.Address(External:=False) & Seperator

    Next Area

    GetAddressWithSheetname = Left(TheAddress, Len(TheAddress) - Len(Seperator))

    If blnBuildAddressForNamedRangeValue Then
        GetAddressWithSheetname = "=" & GetAddressWithSheetname
    End If

End Function

Answer 6

La única forma en que puedo pensar es concatenar el nombre de la hoja de trabajo con la referencia de celda, de la siguiente manera:

Dim cell As Range
Dim cellAddress As String
Set cell = ThisWorkbook.Worksheets(1).Cells(1, 1)
cellAddress = cell.Parent.Name & "!" & cell.Address(External:=False)

EDITAR:

Modificar la última línea para:

cellAddress = "''" & cell.Parent.Name & "''!" & cell.Address(External:=False) 

si desea que funcione incluso si hay espacios u otros caracteres divertidos en el nombre de la hoja.

Answer 7

La función de hoja de cálculo de Address() hace exactamente eso. Como no está disponible a través de Application.WorksheetFunction , se me ocurrió una solución utilizando el método Evaluate() .

Esta solución permite que Excel trate espacios y otros personajes divertidos en el nombre de la hoja, lo cual es una buena ventaja sobre las respuestas anteriores.

Ejemplo:

Evaluate("ADDRESS(" & rng.Row & "," & rng.Column & ",1,1,""" & _
    rng.Worksheet.Name & """)")

devuelve exactamente "Hoja1! $ A $ 1", con un objeto Range llamado rng referencia a la celda A1 en la hoja de trabajo Hoja1.

Esta solución solo devuelve la dirección de la primera celda de un rango, no la dirección de todo el rango ("Hoja1! $ A $ 1" frente a "Hoja1! $ A $ 1: $ B $ 2"). Entonces lo uso en una función personalizada:

Public Function AddressEx(rng As Range) As String

    Dim strTmp As String

    strTmp = Evaluate("ADDRESS(" & rng.Row & "," & _
        rng.Column & ",1,1,""" & rng.Worksheet.Name & """)")

    If (rng.Count > 1) Then

        strTmp = strTmp & ":" & rng.Cells(rng.Count) _
            .Address(RowAbsolute:=True, ColumnAbsolute:=True)

    End If

    AddressEx = strTmp

End Function

La documentación completa de la función de hoja de cálculo de Dirección () está disponible en el sitio web de Office: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89

Answer 8

Para confundirme un rango

.Address (False, False, True)

parece dar en formato TheSheet! B4: K9

Si no es así, ¿por qué los criterios ... evite los funcionamientos Str?

probablemente solo tome menos un milisegundo y use 153 electrones ya usados

alrededor de 0.3 Microsec

RaAdd = mid (RaAdd, instr (raadd, "]") +1)

o

''alrededor de 1.7 microsec

RaAdd = split (radd, "]") (1)

Answer 9

[editar el 2009-04-21]

Como señaló Micah, esto solo funciona cuando lo has nombrado
rango particular (de ahí. ¿alguien?) Sí, ¡uy!

[/editar]

Un poco tarde para la fiesta, lo sé, pero en caso de que alguien más lo capte en una búsqueda en Google (como acabo de hacer), también podría intentar lo siguiente:

Dim cell as Range
Dim address as String
Set cell = Sheet1.Range("A1")
address = cell.Name

Esto debería devolver la dirección completa, algo así como "= Sheet1! $ A $ 1".

Suponiendo que no desea el signo igual, puede quitarlo con la función Reemplazar:

address = Replace(address, "=", "")

Answer 10

Dim rg As Range
Set rg = Range("A1:E10")
Dim i As Integer
For i = 1 To rg.Rows.Count

    For j = 1 To rg.Columns.Count
    rg.Cells(i, j).Value = rg.Cells(i, j).Address(False, False)

    Next
Next

Answer 11

Split(cell.address(External:=True), "]")(1)