UsedRange inflado, Sheets(3) y el rango que no se reasigna
El libro «Inventario.xlsm» contiene las hojas «Stock», «Movimientos» y una hoja de gráfico llamada «Evolución». En «Stock», la columna A guarda el código de artículo en las filas 2 a 2.300, pero las filas 2.301 a 48.000 conservan bordes y relleno de una importación antigua. La columna B tiene fórmulas que devuelven cadena vacía cuando no hay dato. La macro Recalcular dimensiona el bucle con ActiveSheet.UsedRange.Rows.Count, calcula la última fila con Range("A65536").End(xlUp).Row y escribe el total en Sheets(3).Range("A1"). Además, un auxiliar Marcar(r As Range) recibe el rango de salida y le aplica Set r = r.Offset(1, 0). El analista ejecuta la macro con otro libro abierto y, a veces, con la hoja de gráfico seleccionada.
1. ¿Por qué `UsedRange.Rows.Count` devuelve 48.000 cuando solo hay 2.300 registros? ¿Y por qué `Range("A65536").End(xlUp).Row` puede dar una última fila incorrecta en este libro? Indica qué usarías.
Read the worked answer
UsedRange no mide datos, mide extensión usada. El rango usado va desde la primera celda utilizada hasta la última que tenga contenido o formato. Los bordes y rellenos que quedaron de la importación antigua cuentan como uso, así que Excel informa de 48.000 filas. El bucle recorre 45.700 filas vacías y, si dentro hay escrituras, el rendimiento se hunde.
El 65536 es otra trampa. Ese era el límite de filas de los libros .xls; en .xlsx y .xlsm hay 1.048.576. Escribir 65536 funciona por accidente en hojas pequeñas, pero si algún dato llega más abajo, End(xlUp) arranca desde un punto intermedio y devuelve una fila que no es la última. Lo correcto es Cells(Rows.Count, 1).End(xlUp).Row.
Y la columna elegida importa. End(xlUp) se detiene en la primera celda no vacía que encuentra subiendo. Una celda con fórmula que devuelve "" no está vacía para VBA: contiene una fórmula. Si se buscara la última fila por la columna B, el resultado se desplazaría hasta donde lleguen esas fórmulas. Por eso se busca por la columna A, que tiene los códigos reales.
Alternativa robusta: Cells.Find(What:="*", After:=Cells(1,1), LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, comprobando antes que el Find no devuelva Nothing. Para el recuento, mejor Application.WorksheetFunction.CountA sobre la columna de códigos que fiarse de UsedRange.
2. El código escribe en `Sheets(3).Range("A1")` y a veces falla. ¿Qué riesgo concreto hay en este libro y qué escribirías en su lugar?
Read the worked answer
Sheets incluye hojas de gráfico; Worksheets no. La colección Sheets contiene todos los tipos de hoja en orden de pestañas: hojas de cálculo, de gráfico, de diálogo antiguas y módulos de hoja. En este libro, el tercer elemento es «Evolución», una hoja de gráfico, que no tiene Range; la instrucción falla con un error de miembro no válido.
El índice es frágil por diseño. Aunque el tercer elemento fuera una hoja de cálculo, el número depende del orden de las pestañas: si el usuario arrastra «Movimientos» delante de «Stock», el 3 apunta a otra hoja y la macro escribe el total en el sitio equivocado, sin error. Un fallo silencioso es peor que uno ruidoso.
Qué escribir en su lugar.
- Por nombre y con libro explícito:
ThisWorkbook.Worksheets("Stock").Range("A1").Value = total. - Si el libro puede renombrarse, usar el nombre en clave de la hoja (
wsStock.Range("A1")), que no cambia al cambiar la pestaña. - Nunca
ActiveSheetpara escribir resultados: si el usuario está en otro libro,ActiveSheetpertenece a ese otro libro.
Regla práctica. Los índices numéricos solo son aceptables en bucles que recorren una colección completa y solo si dentro del bucle se comprueba el tipo (If TypeName(sh) = "Worksheet" Then). Para escribir en un destino concreto, nombre explícito y libro calificado, siempre.
3. El auxiliar `Marcar(r As Range)` ejecuta `Set r = r.Offset(1, 0)` y el llamador comprueba después que su variable apunta a la fila siguiente, pero no ha cambiado. Explícalo y di qué implica para modificar datos.
Read the worked answer
En VBA el paso de argumentos es por referencia salvo que se indique lo contrario. Con ByRef, el parámetro es un alias de la variable del llamador: Set r = ... reengancha esa misma variable a otro objeto y el llamador lo ve. Si el llamador no ve el cambio, el parámetro está declarado (o se ha declarado en la llamada) como ByVal r As Range: entonces r es una copia de la referencia y Set r = ... solo reengancha la copia local, que se destruye al salir del procedimiento.
Matiz importante: ByVal no protege los datos. Aunque la referencia sea una copia, apunta al mismo objeto Range de la hoja. r.Value = 0 o r.Interior.Color = vbYellow modifican la hoja igual, porque el objeto subyacente es compartido. ByVal solo evita que el procedimiento cambie a qué apunta la variable del llamador.
Cómo decidir.
- Si el auxiliar solo lee o formatea, declarar
ByVal r As Range: deja claro que no se reasignará la referencia del llamador. - Si necesita devolver un rango desplazado o ampliado, no lo haga por efecto lateral: declare
Function SiguienteFila(r As Range) As Rangey devuelva el rango, o pase el rango porByRefy documéntelo. - Un efecto lateral oculto en un
Subes una fuente clásica de errores difíciles de rastrear: el llamador cree que su variable no ha cambiado y actúa sobre otra zona de la hoja.
Comprobación rápida: poner un punto de interrupción tras la llamada y examinar la variable en la ventana Inspección; si apunta a la fila original, el argumento viajó por valor.