MateriasEscuelaProfesoresTransparenciaDemostración

Materias · 🏛️ Universidad y trabajo

Excel/VBA y Apps Script

Ejercicios resueltos de Excel/VBA y Apps Script paso a paso, con el error que más se comete en cada uno: Apps Script: llamadas al servicio y tiempo de ejecución, VBA: trabajar sobre rangos sin Select y más. Gratis y en español.

Preguntarle al tutor de Excel/VBA y Apps Script

Sin instalar nada. Puedes probar antes de crear cuenta.

Ejercicios resueltos de Excel/VBA y Apps Script

Resueltos paso a paso, como te los explicaría el tutor, y con el error que más se comete.

Apps Script: llamadas al servicio y tiempo de ejecución

Un script escribe 5.000 filas en Google Sheets con un bucle que hace hoja.getRange(i, 1).setValue(dato) y se cae con «Se superó el tiempo máximo de ejecución» (6 minutos). ¿Por qué tarda tanto y cómo se arregla?

Cómo se resuelve

  1. 1Lo lento no es el bucle. Cinco mil vueltas de JavaScript puro se ejecutan en milisegundos; lo que cuesta es que cada setValue sale de Apps Script, viaja al servicio de Sheets, espera la confirmación y vuelve. Son 5.000 viajes de ida y vuelta.
  2. 2La regla general de Apps Script es leer una vez, calcular en memoria y escribir una vez. Todo lo que se pueda hacer sin tocar la hoja, se hace sin tocarla.
  3. 3Se arma primero el arreglo completo: un arreglo de arreglos, donde cada elemento interno es una fila. Y se escribe de un solo golpe con hoja.getRange(2, 1, datos.length, datos[0].length).setValues(datos). Un viaje en lugar de 5.000, y el script pasa de minutos a segundos.
  4. 4Lo mismo aplica del otro lado: se lee con un solo getValues() del rango completo y se recorre el arreglo, en vez de llamar getValue() celda por celda. Mezclar lecturas y escrituras dentro del bucle es todavía peor, porque obliga a Sheets a recalcular entre una y otra.
  5. 5SpreadsheetApp.flush() solo se usa cuando de verdad hace falta forzar la escritura pendiente; ponerlo dentro del bucle devuelve el script a la lentitud original.
  6. 6Si aun con esto el trabajo no cabe en los 6 minutos (decenas de miles de filas con cálculo pesado), se parte en tandas: se procesa un bloque, se guarda en PropertiesService la fila por la que iba y un disparador por tiempo retoma desde ahí.

Respuesta: Cada setValue es una llamada al servicio de Sheets: el costo son los 5.000 viajes, no el bucle. Se arma el arreglo en memoria y se escribe todo con un solo setValues.

El error de siempre: Culpar al bucle y ponerse a optimizar el JavaScript, o peor, meter un Utilities.sleep() «para que la hoja alcance a responder», que solo agrega tiempo al que ya sobraba. El costo está en cruzar la frontera hacia el servicio de Sheets, y esa frontera se cruza una vez por cada get/set. Reducir el número de llamadas es lo único que cambia el resultado.

VBA: trabajar sobre rangos sin Select

Esta macro recorre 20.000 filas, pinta de rojo las de saldo vencido, tarda casi 4 minutos y la pantalla parpadea todo el tiempo. Mejórala. For i = 1 To 20000 Cells(i, 3).Select If Selection.Value > 0 Then Selection.Interior.Color = vbRed Next i

Cómo se resuelve

  1. 1El Select es el culpable principal. Mueve la selección real de la hoja, lo que obliga a Excel a repintar la pantalla y a actualizar la barra de estado 20.000 veces. Y no aporta nada: VBA puede leer y escribir cualquier celda sin seleccionarla.
  2. 2Primera corrección, quitar el Select y trabajar directo sobre la celda: If Cells(i, 3).Value > 0 Then Cells(i, 3).Interior.Color = vbRed. Con solo eso la macro suele bajar a menos de la mitad del tiempo.
  3. 3Segunda, apagar lo que Excel hace de más mientras corre la macro: Application.ScreenUpdating = False y Application.Calculation = xlCalculationManual al empezar, y volver a encenderlos al terminar. Se encienden dentro de un manejador de errores (On Error GoTo), porque si la macro se cae con el recálculo apagado, el usuario queda con un Excel que «no suma» y nadie entiende por qué.
  4. 4Tercera, si se quiere la versión rápida de verdad: leer el rango a un arreglo con v = Range("C1:C20000").Value, recorrer el arreglo en memoria (que es instantáneo) y pintar solo las filas que cumplen. Leer 20.000 celdas de un golpe cuesta una fracción de lo que cuesta leerlas una por una.
  5. 5Y la pregunta que había que hacerse antes de escribir la macro: si lo único que se necesita es que las celdas se vean rojas, el formato condicional lo hace sin código, se recalcula solo cuando el dato cambia y no hay que mantener nada. La macro se justifica si además hay que mover datos o generar un reporte.

Respuesta: Quitar los Select y actuar directo sobre las celdas, apagar ScreenUpdating y el recálculo mientras corre, y de ser posible leer el rango a un arreglo. Si solo es color, usar formato condicional sin macro.

El error de siempre: Dejar los Select porque «así los escribió la Grabadora de macros». La grabadora anota lo que el usuario hizo con el mouse —seleccionar y después actuar— y por eso su código está lleno de Select y ActiveCell. VBA no lo necesita: puede actuar sobre cualquier rango sin tocar la selección. Quitar los Select es la primera limpieza de casi toda macro grabada, y casi siempre es la que más tiempo ahorra.

¿Y si tu ejercicio no es ninguno de estos?

No hace falta buscar el ejercicio exacto en internet. Trae el tuyo, o pide los que te falten.

📸El ejercicio que tengo yoTómale una foto a tu cuaderno o al taller y lo resolvemos paso a paso, con tus números.Resolver el mío →🔁Más ejercicios como estosQue te arme ejercicios nuevos de Apps Script: llamadas al servicio y tiempo de ejecución, al nivel que pidas y con la solución explicada.Generar ejercicios →💡No entendí el temaTe lo explica desde cero, con otras palabras, y se detiene donde te trabes. Puedes preguntar mil veces.Explícamelo →📝Tengo parcialSube un parcial viejo de tu profesor y practica con ejercicios de esa misma forma.Prepararme →

Todo esto es gratis y sin instalar nada. Puedes probar antes de crear cuenta.

Lo que cubre

  • Apps Script: llamadas al servicio y tiempo de ejecución
  • VBA: trabajar sobre rangos sin Select

Preguntas que le puedes hacer

  • Apps Script: llamadas…Explícame «Apps Script: llamadas al servicio y tiempo de ejecución» con un ejemplo práctico y dime en qué suele equivocarse la gente.
  • VBA: trabajar sobre r…Explícame «VBA: trabajar sobre rangos sin Select» con un ejemplo práctico y dime en qué suele equivocarse la gente.

Cómo enseña este tutor

Es riguroso y completo: justifica cada paso y nombra el teorema, la norma o la buena práctica que está aplicando, para que puedas citarla en tu trabajo o en el parcial.

  • · Te muestra el camino completo, no solo el resultado.
  • · Si algo no te cuadra, te lo explica otra vez de otra manera.
  • · Con una foto de tu cuaderno te dice dónde está el error.
  • · Te arma quizzes de lo que acabas de ver para saber si de verdad quedó.

Otras materias de Universidad y trabajo

Un producto deLOTIC