La instrucción SET en VBA de Excel
relacionada con la instrucción SET del VBA de Excel.
Yo entiendo la instrucción SET cómo una manera de crear objetos y reducir el tiempo de "picado" de código. Si no es así... Por favor rectificarme.
Yo quiero hacer un programa en VBA para Excel, en que el mismo programa lo voy a usar para hojas dónde a veces las columnas se situarán en posiciones distintas. Por lo que se me ocurrió de usar la instrucción SET para poder modificar a "posteriori" un pequeño trozo de código y no todo entero.
Supongamos que quiero escribir en las diez primeras celdas de la columna A y hoja 4 el número tres:
Sub set_columna()
Set c4 = Sheets(4).Columns("A")
For i = 1 To 10
Range(c4 & i).Value = 10
Next
End Sub
Pues el código me da error...
¿Alguna idea de porqué?
3 respuestas
Por un lado si te pones a pensar ... comprendo el error. En office del 2016 la columna A iría de A1 a A1048576. Por lo que la columna("A") seria igual a un range("A1 : A1048576"). Con lo que no tiene ningún sentido escribir range("("A1 : A1048576")"&i), que es lo que escribí.
Por otro lado, después de leer con detenimiento vuestras respuestas y con vuestro permiso puesto que sois los expertos, creo entender que la instrucción SET permite rebautizar algo. Este algo puede ser un rango, hoja, etc. Es decir, no creo propiamente un objeto, sino que algo que ya existe, lo rebautizo... le digo que eso se llama así. Es decir, asigno a una variable un objeto.
:)
.
Hola! José. Te lo voy a explicar "fácil"...
___________________
- Cuando en un código ves algo como:
c = 6
la variable "c" es -a todos tus efectos- equivalente al número 6. Por ejemplo:
MsgBox c
Te mostrará un mensaje en un cuadro de diálogo con un 6... ¿No es cierto?...
Resumiendo: "c = 6" asigna a la variable "c" el número 6.
___________________
- Ahora bien: cuando lo que vas a asignar a una variable es un objeto (una o varias celdas, una o varias hojas, un libro, un objeto gráfico, etc) se utiliza "Set". Por ejemplo:
Set c = Sheets(4).Columns("A")
A partir de entonces, la variable "c" equivale al rango de celdas Sheets(4). Columns("A").
___________________
Por lo anterior resulta fácil dedudir el por qué de tus errores. Cuando dices:
Range(c4 & i).Value = 10
entonces quieres decir:
Range(Sheets(4).Columns("A") & i).Value = 10
Lo cual -obviamente- es un disparate, ¿Comprendes?...
.
.
.
.
Por ello es que tu expresión: - "... Yo entiendo la instrucción SET cómo una manera de crear objetos ..." es -por demás- equivocada.
Quien crea objetos ActiveX es la función CreateObject.
Y una vez creado el objeto -volvemos a lo ya comentado- mediante Set asignás ese objeto a una variable: ¿Ok?
.
.
¡Gracias!
:)
Exacto, José: ¡Lo "agarraste" a la perfección!...
Así que sólo restaría ver aquello de: - "... quiero escribir en las diez primeras celdas de la columna A y hoja 4 el número tres ...".
Te muestro varias maneras:
Set c4 = Sheets(4)
For i = 1 To 10
c4.Range("A" & i) = 3
Nextó
Set c4 = Sheets(4)
c4.Range("A1:A10") = 3ó
Sheets(4).Range("A1:A10") = 3ó
With Sheets(4)
.Range("A1:A10") = 3
End WithSaludos, Mario R.
.
.
La orden SET convierte tu información en objeto, al cual solo le vas asignando propiedades como colorear, ajustar columnas, formater, añadir fórmulas, o copiar información en un solo paso, por ejemplo si tengo un rango variable de filas y columnas 4 filas por 10 columnas que comienzan en la celda A1, lo único que hago es escribir set datos = range("A1"). Currentregion con esto no importa cuantas filas o columnas añada a quite la orden SET tomara en cuenta estos cambios, combinada con with nos da macros muy eficientes, dominado esta instrucción te olvidas de usar los do y puedes trabajar cómodamente con volúmenes grandes de información con muy pocas instrucción
Haz este ejercicio para que entiendas un poco como trabaja la orden
Sub cuenta()
Range("a1").CurrentRegion.Clear 'limpia la hoja partiendo desde a1 hasta donde encuentre una fila o columna vacias
Range("a1").Resize(15, 10).Formula = "=randbetween(1,50)"
'crea un area dinamica de datos en este caso de 15x10, puedes agregar o quitar
'y la instruccion lo tomara en cuenta
Set datos = Range("a1").CurrentRegion
With datos
.Value = .Value 'convierte celdas con formula en valores
.EntireColumn.AutoFit 'ajusta las columnas a los datos
filas = .Rows.Count 'cuenta el numero de filas
columnas = .Columns.Count 'cuenta el numero de columnas
'suma todas las columnas y coloca el resultado un fila abajo de la ultima fila
.Rows(filas + 1).Resize(1, columnas).Formula = _
"=sum(" & .Columns(1).Address(False, False) & ")"
'suma todas las filas y coloca el resultado un despues de la ultima columna
.Columns(columnas + 1).Resize(.Rows.Count, 1).Formula = _
"=sum(" & .Rows(1).Address(False, False) & ")"
'ajusta todos los datos
Set datos = .CurrentRegion 'ajusta el set a los datos, añade los totales
filas = .Rows.Count 'cuenta de nuevo el numero de filas
columnas = .Columns.Count 'cuenta de nuevo el numero de columnas
'formatea en color rojo y letra blanca en negrita la fila totales
With .Rows(filas)
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.Font.Bold = True
End With
'formatea en color rojo y letra blanca en negrita la columna totales
With .Columns(columnas)
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.Font.Bold = True
End With
End With
End Sub