usar - Cómo evitar el uso de Select en Excel VBA
select cell vba (13)
"... y me doy cuenta de que mi código sería más reutilizable si pudiera usar variables en lugar de las funciones Seleccionar".
Si bien no puedo pensar en nada más que en un puñado aislado de situaciones en las que .Select sería una mejor opción que las referencias directas a las celdas, me levantaría en defensa de la Selection y señalaría que no debería descartarse por las mismas razones .Select debe evitarse.
Hay ocasiones en las que las sub-rutinas de macros cortas que ahorran tiempo y que están asignadas a combinaciones de teclas de acceso rápido disponibles con el toque de un par de teclas ahorran mucho tiempo. Ser capaz de seleccionar un grupo de celdas para promulgar el código operacional en las maravillas de las obras cuando se trata de datos ocultos que no se ajustan a un formato de datos de toda la hoja de trabajo. De la misma manera que puede seleccionar un grupo de celdas y aplicar un cambio de formato, seleccionar un grupo de celdas para ejecutar un código de macro especial puede ser un gran ahorro de tiempo.
Ejemplos de sub marco basado en la selección:
Public Sub Run_on_Selected()
Dim rng As Range, rSEL As Range
Set rSEL = Selection ''store the current selection in case it changes
For Each rng In rSEL
Debug.Print rng.Address(0, 0)
''cell-by-cell operational code here
Next rng
Set rSEL = Nothing
End Sub
Public Sub Run_on_Selected_Visible()
''this is better for selected ranges on filtered data or containing hidden rows/columns
Dim rng As Range, rSEL As Range
Set rSEL = Selection ''store the current selection in case it changes
For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
Debug.Print rng.Address(0, 0)
''cell-by-cell operational code here
Next rng
Set rSEL = Nothing
End Sub
Public Sub Run_on_Discontiguous_Area()
''this is better for selected ranges of discontiguous areas
Dim ara As Range, rng As Range, rSEL As Range
Set rSEL = Selection ''store the current selection in case it changes
For Each ara In rSEL.Areas
Debug.Print ara.Address(0, 0)
''cell group operational code here
For Each rng In ara.Areas
Debug.Print rng.Address(0, 0)
''cell-by-cell operational code here
Next rng
Next ara
Set rSEL = Nothing
End Sub
El código real para procesar podría ser cualquier cosa, desde una sola línea hasta varios módulos. He utilizado este método para iniciar rutinas de larga duración en una selección irregular de celdas que contienen los nombres de archivos de libros externos.
En resumen, no descarte la Selection debido a su asociación cercana con ActiveCell y ActiveCell . Como una propiedad de la hoja de trabajo tiene muchos otros propósitos.
(Sí, sé que esta pregunta era sobre .Select , no sobre Selection pero quería eliminar cualquier idea errónea que los codificadores novatos de VBA pudieran inferir).
He escuchado mucho sobre el abominable entendimiento de usar .Select en Excel VBA, pero no estoy seguro de cómo evitar usarlo. Estoy descubriendo que mi código sería más reutilizable si pudiera usar variables en lugar de las funciones Select . Sin embargo, no estoy seguro de cómo referirme a cosas (como ActiveCell etc.) si no estoy usando Select .
He encontrado este artículo en rangos y este ejemplo en los beneficios de no usar Select pero no puedo encontrar nada sobre cómo .
Algunos ejemplos de cómo evitar seleccionar
Usar variables Dim ''d
Dim rng as Range
Set la variable en el rango requerido. Hay muchas maneras de referirse a un rango de celda única
Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = Range("NamedRange")
o un rango de múltiples celdas
Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1,1), Cells(10,2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10,2)
Puede utilizar el acceso directo al método Evaluate , pero esto es menos eficiente y, por lo general, debe evitarse en el código de producción.
Set rng = [A1]
Set rng = [A1:B10]
Todos los ejemplos anteriores se refieren a celdas en la hoja activa . A menos que específicamente desee trabajar solo con la hoja activa, también es mejor atenuar una variable de la hoja de Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
With ws
Set rng = .Range(.Cells(1,1), .Cells(2,10))
End With
Si desea trabajar con ActiveSheet , para mayor claridad, es mejor ser explícito. Pero tenga cuidado, ya que algunos métodos de la hoja de Worksheet cambian la hoja activa.
Set rng = ActiveSheet.Range("A1")
De nuevo, esto se refiere al libro de trabajo activo . A menos que específicamente desee trabajar solo con ActiveWorkbook o ThisWorkbook , también es mejor atenuar una variable de Workbook .
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
Si desea trabajar con ActiveWorkbook , para mayor claridad, es mejor ser explícito. Pero tenga cuidado, ya que muchos métodos de WorkBook cambian el libro activo.
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
También puede usar el objeto ThisWorkbook para referirse al libro que contiene el código en ejecución.
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Una pieza de código común (mala) es abrir un libro, obtener algunos datos y luego volver a cerrar
Esto es malo:
Sub foo()
Dim v as Variant
Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
Workbooks.Open("C:/Path/To/SomeClosedBook.xlsx")
v = ActiveWorkbook.Sheets(1).Range("A1").Value
Workbooks("SomeAlreadyOpenBook.xlsx").Activate
ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
Workbooks(2).Activate
ActiveWorkbook.Close()
End Sub
Y sería mejor como
SUb foo()
Dim v as Variant
Dim wb1 as Workbook
Dim wb2 as Workbook
Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
Set wb2 = Workbooks.Open("C:/Path/To/SomeClosedBook.xlsx")
v = wb2.Sheets("SomeSheet").Range("A1").Value
wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
wb2.Close()
End Sub
Pase los rangos a sus Sub y Function como variables de rango
Sub ClearRange(r as Range)
r.ClearContents
''....
End Sub
Sub MyMacro()
Dim rng as Range
Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
ClearRange rng
End Sub
También debe aplicar métodos (como Find y Copy ) a las variables
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2
Si realiza un bucle en un rango de celdas, a menudo es mejor (más rápido) copiar los valores del rango en una matriz variante primero y pasar por ese
Dim dat As Variant
Dim rng As Range
Dim i As Long
Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value '' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
dat(i,1) = dat(i,1) * 10 ''or whatever operation you need to perform
next
rng.Value = dat '' put new values back on sheet
Esta es una pequeña muestra de lo que es posible.
El uso IMHO de .select proviene de personas que, como yo, comenzaron a aprender VBA por necesidad a través de la grabación de macros y luego modificaron el código sin darse cuenta de que .select y la selection subsiguiente son solo un intermediario innecesario.
.select puede evitarse, como ya se ha publicado, trabajando directamente con los objetos ya existentes, lo que permite varias referencias indirectas, como calcular i y j de una manera compleja y luego editar la celda (i, j), etc.
De lo contrario, no hay nada implícitamente incorrecto con .select y puede encontrar usos para esto fácilmente, por ejemplo, tengo una hoja de cálculo que llena con la fecha, activa una macro que hace algo de magia y la exporta en un formato aceptable en una hoja separada , que, sin embargo, requiere algunas entradas finales manuales (impredecibles) en una celda adyacente. Así que aquí llega el momento para .select que me ahorra ese movimiento adicional del mouse y haga clic.
Este es un ejemplo que borrará el contenido de la celda "A1" (o más si el tipo de selección es xllastcell, etc.). Todo hecho sin tener que seleccionar las celdas.
Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents
Espero que esto ayude a alguien.
Estos métodos son más bien estigmatizados, por lo que tomar la iniciativa de @Vityata y @Jeeped para dibujar una línea en la arena:
Por qué no llamar .Activate , .Activate , Selection , ActiveSomething methods / properties
Básicamente porque se les llama principalmente para manejar la entrada del usuario a través de la interfaz de usuario de la aplicación. Dado que son los métodos a los que se llama cuando el usuario maneja los objetos a través de la interfaz de usuario, son los que registra el grabador de macros, y es por eso que llamarlos es frágil o redundante para la mayoría de las situaciones: no tiene que seleccionar un Objeto para realizar una acción con la Selection inmediatamente después.
Sin embargo, esta definición resuelve las situaciones en las que se solicitan:
Cuándo llamar .Activate , .Selection , .ActiveSomething , .ActiveSomething methods / properties
Básicamente, cuando esperas que el usuario final juegue un papel en la ejecución.
Si está desarrollando y espera que el usuario elija las instancias de objeto para que su código las maneje, entonces .Selection o .ActiveObject son apropiados.
Por otro lado, .Activate y .Activate son útiles cuando puede inferir la siguiente acción del usuario y desea que su código guíe al usuario, posiblemente ahorrando tiempo y clics del mouse. Por ejemplo, si su código acaba de crear una nueva instancia de un gráfico o una actualización, es posible que el usuario quiera verificarlo y usted pueda llamar .Activate en él o en su hoja para ahorrarle al usuario el tiempo que lo busca; o si sabe que el usuario necesitará actualizar algunos valores de rango, puede seleccionar ese rango programáticamente.
Evitar Select y Activate es el movimiento que lo hace un poco mejor desarrollador de VBA. En general, la Select y la Activate se utilizan cuando se graba una macro, por lo que la hoja de trabajo o rango principal siempre se considera la activa.
Así es como puedes evitar Select y Activate en los siguientes casos:
Añadiendo una nueva hoja de trabajo y copiando una celda en ella:
De (código generado con grabadora de macros):
Sub Makro2()
Range("B2").Select
Sheets.Add After:=ActiveSheet
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "NewName"
ActiveCell.FormulaR1C1 = "12"
Range("B2").Select
Selection.Copy
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
A:
Sub TestMe()
Dim ws As Worksheet
Set ws = Worksheets.Add
With ws
.Name = "NewName"
.Range("B2") = 12
.Range("B2").Copy Destination:=.Range("B3")
End With
End Sub
Cuando quieras copiar rango entre hojas de trabajo:
Desde:
Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste
A:
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")
Usando rangos nombrados de lujo
Puedes acceder a ellos con [] . Lo que es realmente hermoso, comparado con el otro. Compruebe usted mismo:
Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
Set MonthlySales = Range("MonthlySales")
Set Months =[Months]
Set MonthlySales = [MonthlySales]
El ejemplo de arriba se vería así:
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]
No copiar valores, sino tomarlos.
Por lo general, si está dispuesto a select , lo más probable es que esté copiando algo. Si solo está interesado en los valores, esta es una buena opción para evitar seleccionar:
Range("B1:B6").Value = Range("A1:A6").Value
Trate siempre de referir la hoja de trabajo también
Este es probablemente el error más común en la vba . Siempre que copie rangos, a veces no se hace referencia a la hoja de cálculo y, por lo tanto, VBA considera la hoja de ActiveWork.
''This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
Dim rng As Range
Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub
''This works always!
Public Sub TestMe2()
Dim rng As Range
With Worksheets(2)
.Range(.Cells(1, 1), .Cells(2, 2)).Copy
End With
End Sub
¿Realmente nunca puedo usar .Select o .Activate para nada?
El único momento en que podría estar justificado para usar .Activate y .Activate es cuando quiera asegurarse de que se seleccione una Hoja de trabajo específica por razones visuales. Por ejemplo, que su Excel siempre se abriría con la hoja de trabajo de la portada seleccionada primero, ignorando cuál era la hoja de actividad cuando se cerró el archivo. Por lo tanto, algo como esto está absolutamente bien:
Private Sub Workbook_Open()
Worksheets("Cover").Activate
End Sub
Indique siempre el libro de trabajo, la hoja de trabajo y la celda / rango.
Por ejemplo:
Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)
Debido a que los usuarios finales siempre harán clic en los botones y, tan pronto como el foco se aleje del libro de trabajo con el que el código quiere trabajar, las cosas van completamente mal.
Y nunca use el índice de un libro de trabajo.
Workbooks(1).Worksheets("fred").cells(1,1)
No sabe qué otros libros de trabajo estarán abiertos cuando el usuario ejecute su código.
Noté que ninguna de estas respuestas menciona la propiedad .Offset . Esto también se puede usar para evitar el uso de la acción Select cuando se manipulan ciertas celdas, particularmente en referencia a una celda seleccionada (como menciona el OP con ActiveCell ).
Aqui hay un par de ejemplos.
También asumiré que el "ActiveCell" es J4 .
ActiveCell.Offset(2, 0).Value = 12
- Esto cambiará la celda
J6para que sea un valor de 12. - Un menos -2 habría hecho referencia a J2
ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)
- Esto copiará la celda en
k4aL4. - Tenga en cuenta que "0" no es necesario en el parámetro de compensación si no es necesario (, 2)
- Similar al ejemplo anterior, un menos 1 sería
i4
ActiveCell.Offset(, -1).EntireColumn.ClearContents
- Esto borrará los valores en todas las celdas de la columna k.
Esto no quiere decir que sean "mejores" que las opciones anteriores, sino que solo enumeran alternativas.
Respuesta rápida:
Para evitar el uso del método .Select , puede establecer una variable igual a la propiedad que desee.
► Por ejemplo, si desea el valor en la Cell A1 , puede establecer una variable igual a la propiedad de valor de esa celda.
- Ejemplo
valOne = Range("A1").Value
► Por ejemplo, si desea el nombre de código de ''Sheet3'' podría establecer una variable igual a la propiedad de nombre de código de esa hoja de trabajo.
- Ejemplo
valTwo = Sheets("Sheet3").Codename
Espero que eso ayude. Hazme saber si tienes alguna pregunta.
Tenga en cuenta que a continuación comparo el enfoque Select (el que el OP quiere evitar), con el enfoque Range (y esta es la respuesta a la pregunta). Así que no dejes de leer cuando veas el primer Select.
Realmente depende de lo que estés tratando de hacer. De todos modos un ejemplo simple podría ser útil. Supongamos que desea establecer el valor de la celda activa en "foo". Usando ActiveCell escribirías algo como esto:
Sub Macro1()
ActiveCell.Value = "foo"
End Sub
Si desea usarlo para una celda que no es la activa, por ejemplo, para "B2", primero debe seleccionarla, de esta manera:
Sub Macro2()
Range("B2").Select
Macro1
End Sub
Usando Rangos, puede escribir una macro más genérica que se puede usar para establecer el valor de cualquier celda que desee para lo que quiera:
Sub SetValue(cellAddress As String, aVal As Variant)
Range(cellAddress).Value = aVal
End Sub
Luego puedes reescribir Macro2 como:
Sub Macro2()
SetCellValue "B2", "foo"
End Sub
Y Macro1 como:
Sub Macro1()
SetValue ActiveCell.Address, "foo"
End Sub
Espero que esto ayude a aclarar las cosas un poco.
Un pequeño punto de énfasis que agregaré a todas las excelentes respuestas dadas anteriormente:
Probablemente, lo más importante que puede hacer para evitar el uso de Select es, en la medida de lo posible, usar rangos con nombre (combinados con nombres de variables significativos) en su código VBA . Este punto se mencionó anteriormente, pero se pasó por alto un poco; Sin embargo, merece especial atención.
Aquí hay un par de razones adicionales para hacer un uso liberal de los rangos con nombre, aunque estoy seguro de que podría pensar en más.
Los rangos con nombre hacen que su código sea más fácil de leer y entender.
Ejemplo:
Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
''e.g, "Months" might be a named range referring to A1:A12
Set MonthlySales = Range("MonthlySales")
''e.g, "Monthly Sales" might be a named range referring to B1:B12
Dim Month As Range
For Each Month in Months
Debug.Print MonthlySales(Month.Row)
Next Month
Es bastante obvio lo que contienen los rangos nombrados, Months y MonthlySales , y lo que está haciendo el procedimiento.
¿Porque es esto importante? Parcialmente porque es más fácil para otras personas entenderlo, pero incluso si eres la única persona que verá o usará tu código, debes usar rangos con nombre y buenos nombres de variables porque OLVIDARÁS lo que quisiste hacer con eso Un año más tarde, perderá 30 minutos para averiguar qué está haciendo su código.
Los rangos con nombre aseguran que sus macros no se rompan cuando (¡no si!) La configuración de la hoja de cálculo cambia.
Considera, si el ejemplo anterior hubiera sido escrito así:
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")
Dim rng3 As Range
For Each rng3 in rng1
Debug.Print rng2(rng3.Row)
Next rng3
Este código funcionará bien al principio, es decir, hasta que usted o un futuro usuario decidan "gee wiz, creo que agregaré una nueva columna con el año en la columna A ", o colocaré una columna de gastos entre los meses y columnas de ventas, o agregar un encabezado a cada columna. Ahora, tu código está roto. Y debido a que utilizó nombres de variables terribles, le llevará mucho más tiempo averiguar cómo solucionarlo de lo que debería.
Para empezar, si ha usado rangos con nombre, las columnas Months y Sales podrían moverse a su gusto, y su código continuará funcionando correctamente.
Voy a dar la respuesta corta ya que todos los demás dieron la larga.
Obtendrá .select y .activate cada vez que registre macros y los reutilice. Cuando seleccionas una celda o una hoja, simplemente la activa. Desde ese momento en adelante, siempre que use referencias no calificadas como Range.Value , solo usarán la celda y la hoja activas. Esto también puede ser problemático si no observa dónde se ubica su código o si un usuario hace clic en el libro de trabajo.
Por lo tanto, puede eliminar estos problemas haciendo referencia directamente a sus celdas. Que va
''create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
''OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)
O tu podrias
''Just deal with the cell directly rather than creating a range
''I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
''OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"
Existen varias combinaciones de estos métodos, pero esa sería la idea general expresada tan pronto como sea posible para personas impacientes como yo.
Se deben evitar dos razones principales por las que .Select / .Activate / Selection / Activecell / Activesheet / Activeworkbook etc.
- Se ralentiza su código.
- Por lo general, es la principal causa de errores de tiempo de ejecución.
¿Cómo lo evitamos?
1) Trabajar directamente con los objetos relevantes.
Considera este código
Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"
Este código también se puede escribir como
With Sheets("Sheet1").Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With
2) Si es necesario declara tus variables. El mismo código anterior se puede escribir como
Dim ws as worksheet
Set ws = Sheets("Sheet1")
With ws.Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With