Funcin LIMPIA
Este es un aporte inicial de Hctor Miguel (+ una mejora posterior de Adrin muy importante!) para mejorar nuestras funciones sololetras y solonumeros. La funcin LIMPIA permite limpiar una cadena de texto y tiene 3 opciones: extraer solo los nmeros, extraer todo excepto los nmeros o extraer solo letras [A-Z].
Como se ve solo requiere 2 argumentos: - celda con texto a limpiar - 1 para solo nmeros, 2 para todo menos nmeros o 3 para solo letras [A-Z] A continuacin la macro que permite usar la funcin LIMPIA: Funcin LIMPIA Visual Basic
Function Limpia(cadena As Strin Dim pat As String Select Case num_car_az Case 2: pat = "[0-9]"
1 Function Limpia(cadena As String, Optional num_car_az As Byte = 1) 2 Dim pat As String 3 Select Case num_car_az 4 Case 2: pat = "[0-9]" 5 Case 3: pat = "[^a-z|]" 6 Case Else: pat = "[^0-9]" 7 End Select 8 With CreateObject("vbscript.regexp") 9 .Global = True 10 .IgnoreCase = True 11 .Pattern = pat 12 Limpia = .Replace(cadena, "") 13 End With 14 If num_car_az = 1 Then Limpia = CLng(Limpia) 15 End Function
14 formas de acelerar y optimizar tus macros excel
Esta informacin es muy til para quienes manejen el tema de programacin de macros excel. Tus macros van lentas? Problemas a la hora de ejecutarlas? Cules son las tcnicas recomendadas? Cuando de programacin de macros excel se trata, el tema de la eficiencia y la velocidad es clave. Hay 2 leyes fundamentales que hay que recordar: . a. Cuanto menos cdigo tiene una macro mejorpor qu? Ayuda a que la macro se ejecute mucho ms rpido Simplifica la tarea a la hora de modificar/ampliar/reparar la macro . b. Cuanto ms rpido se ejecuta una macro mejor!por qu? Mejora la experiencia del usuario No mantiene la PC ocupada tanto tiempo
Respecto de usar menos cdigo depender de las habilidades del programador excel en cuestin. Hemos visto infinidad de casos donde 30 o 40 lneas de cdigo VBA se pueden resumir en 5 o 6 lneas (algo similar pasa con las frmulas excel). Siempre hay macros o frmulas que hacen la tarea de forma ms directa y sin dar tantas vueltas! Otra recomendacin clave es invertir mucho tiempo inicial en planificar y analizar la lgica del trabajo. Esto nos va a ahorrar muchos problemas y dolores de cabeza posteriores! Hay algunas instrucciones puntuales que siempre conviene usar y que van a acelerar y optimizar nuestras macros en todos los casos. Vamos a ver repasar algunas tcnicas puntuales que podemos usar al comienzo, durante y al final de nuestras macros. . AL COMIENZO DE LAS MACROS
1. Apagar el parpadeo de pantalla Lo hacemos con la instruccin: Application.screenupdating=False Evita los movimientos de pantalla que se producen al seleccionar celdas, hojas y libros
2. Apagar los clculos automticos Lo hacemos con la instruccin: Application.calculation=xlCalculationManual Evita que se recalcule todo cada vez que se pegan o modifican datos
3. Apagar los eventos automticos
Lo hacemos con la instruccin: Application.EnableEvents=False Evita que se disparen macros de evento si las hubiere
4. Apagar visualizacin de saltos de pgina Lo hacemos con la instruccin: ActiveSheet.DisplayPageBreaks = False Sirve para evitar algunos problemas de compatibilidad entre macros Excel 2003 vs. 2007/2010 En resumen, siempre debemos comenzar las macros as: Application.screenupdating=False Application.calculation=xlCalculationManual Application.EnableEvents=False ActiveSheet.DisplayPageBreaks = False . AL FINAL DE LAS MACROS
5. Borrar contenido de portapapeles Lo hacemos con la instruccin: Application.CutCopyMode = False Permite limpiar el portapapeles en caso de haber copiado datos Adems debemos volver a su estado original las instrucciones con las que comenzamos la macro. En resumen, siempre debemos finalizar las macros as: Application.screenupdating=True Application.calculation=xlCalculationAutomatic Application.EnableEvents=True ActiveSheet.DisplayPageBreaks = True Application.CutCopyMode = False . OTRAS TECNICAS UTILES
6. Usar la instruccin WITH Se usa para evitar tener que referenciar un mismo objeto muchas veces Ejecucin leeenta Sheets(1).Range(A1:Z1).Font.Italic = True Sheets(1).Range(A1:Z1).Font.Interior.Color = vbRed Sheets(1).Range(A1:Z1).MergeCells = True
Ejecucin rpida! With Sheets(1).Range(A1:Z1) .Font.Italic = True .Font.Interior.Color = vbRed .MergeCells = True End With
7. Evitar la instruccin SELECT Se genera sobre todo en las macros grabadas La mayora de las veces no es necesario seleccionar para cumplir el objetivo Ejecucin leeenta Range(E1).Select Selection.Copy Range(D10).Select ActiveSheet.Paste
Ejecucin rpida! Range(E1).Copy Range(D10)
8. Evitar loops FOR EACH Tener que ir celda por celda consume mucho tiempo Se puede resolver el problema de forma ms directa! Ejecucin leeenta For Each cell In Range(A1:A10000) If cell = Empty Then cell = 0 Next cell * Los loops siempre son leeentos * En este caso recorre 10.000 celdas! Ejecucin rpida! Existen diversas formas de evitar los loops. La solucin depender del caso concreto en cuestin. Generalmente se usan algunas de estas tcnicas: agrupar, ir a especial, filtros, filtros avanzados. La idea es poder realizar la accin sobre todos los elementos al mismo tiempo, en lugar de tener que ir uno a uno! 9. Usar las funciones nativas de Excel No quieras reinventar la rueda. Quizs ya exista una funcin Excel que lo haga! Las macros siempre ejecutan ms rpido las funciones nativas de Excel
Ejecucin leeenta mProducto = 1 For i = 1 to 100 mProducto = mProducto * Cells(3,i) Next Ejecucin rpida! mProducto = Application.WorkSheetFunction.Product(Range(C1:C100))
10. Forzar la declaracin de variables En el editor VBA, men Herramientas > Opciones > pestaa Editor > marcar Requerir declaracin de variables Luego usar la variable correcta: si es fecha usar Date, si es texto usar String, si es valor usar Long Evitar el uso de la variable Variant ya que insume ms recursos Usar nombres de variables que nos digan algo (por ej. UltimaFila o FilaZ en lugar de f o uf) 11. Escribir las macros en mdulos y no en hojas Las hojas pueden ser borradas o copiadas y esto generara problemas inesperados 12. Separar el proceso en varias macros (divide y conquistars) Si tu macro hace muchas cosas conviene separarla en muchas macros pequeas y luego unirlas Es ms fcil para controlar, auditar, etc Adems te permite luego poder rehusar alguna parte del proceso en otras macros Macro muy laaarga Sub MegaMacro() Codigo limpia datos Codigo carga datos Cdigo arregla datos Cdigo arma reporte End Sub()
Mejor dividir en diferentes macros para cada proceso Sub LimpiaDatos() Codigo End Sub Sub CargaDatos() Codigo End Sub
Sub ArreglaDatos() Codigo End Sub Sub ArmaReporte() Codigo End Sub
Finalmente podemos unir todos los procesos Sub ProcesoCompleto() Call LimpiaDatos Call CargaDatos Call ArreglaDatos Call ArmaReporte End Sub() 13. Ser cuidadoso con la instruccin ON ERROR RESUME NEXT Esta instruccin hace que la macro siga avanzando aunque encuentre un error En algunos casos esto har que se ignoren errores que no deberan ser ignorados Podras tener errores (bugs) y no enterarte! 14. Comentar bien las macros Qu pasara si tuvieras que volver a revisar/arreglar/ampliar tu cdigo 8 meses despus? Aadir comentarios te ayudar a describir y recordar la lgica y te ahorrar mucho tiempo!
Funciones SoloLetras y SoloNumeros
Con estas 2 funciones a medida hechas con macros podemos separar de una cadena de texto alfanumrica solo los nmeros o solo las letras.
Ultimo momento! Tenemos una funcin similar a esta pero mejorada llamada Funcin LIMPIA Visual Basic
Function SoloLetras(target As R Dim MyStr As String, i As Intege MyStr = "" If Len(target.Value) = 0 Then Go
1 Function SoloLetras(target As Range) 2 Dim MyStr As String, i As Integer 3 MyStr = "" 4 If Len(target.Value) = 0 Then GoTo GoExit 5 For i = 1 To Len(target.Value) 6 If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1) 7 Next i 8 GoExit: 9 SoloLetras = MyStr 10 End Function
Visual Basic
Function SoloNumeros(target A Dim MyStr As String, i As Intege MyStr = "" If Len(target.Value) = 0 Then Go
1 Function SoloNumeros(target As Range) 2 Dim MyStr As String, i As Integer 3 MyStr = "" 4 If Len(target.Value) = 0 Then GoTo GoExit 5 For i = 1 To Len(target.Value) 6 If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1) 7 Next i 8 GoExit: 9 SoloNumeros = MyStr 10 End Function
Funcin LIMPIA
Este es un aporte inicial de Hctor Miguel (+ una mejora posterior de Adrin muy importante!) para mejorar nuestras funciones sololetras y solonumeros. La funcin LIMPIA permite limpiar una cadena de texto y tiene 3 opciones: extraer solo los nmeros, extraer todo excepto los nmeros o extraer solo letras [A-Z].
Como se ve solo requiere 2 argumentos: - celda con texto a limpiar - 1 para solo nmeros, 2 para todo menos nmeros o 3 para solo letras [A-Z] A continuacin la macro que permite usar la funcin LIMPIA: Funcin LIMPIA Visual Basic
Function Limpia(cadena As Strin Dim pat As String Select Case num_car_az Case 2: pat = "[0-9]"
1 Function Limpia(cadena As String, Optional num_car_az As Byte = 1) 2 Dim pat As String 3 Select Case num_car_az 4 Case 2: pat = "[0-9]" 5 Case 3: pat = "[^a-z|]" 6 Case Else: pat = "[^0-9]" 7 End Select 8 With CreateObject("vbscript.regexp") 9 .Global = True 10 .IgnoreCase = True 11 .Pattern = pat 12 Limpia = .Replace(cadena, "") 13 End With 14 If num_car_az = 1 Then Limpia = CLng(Limpia) 15 End Function
Abrir libro sin habilitar macros
Muchas veces tenemos macros de evento que se ejecutan automticamente al abrir el libro. Por ejemplo, puedes tener una macro de evento tal que si el libro se abre luego de cierta fecha entonces se cierre automticamente. La cuestin es que si deseas modificar esa macro luego que pas la fecha, entonces debers abrir el libro sin habilitar las macros. Para conseguirlo, sencillamente debes mantener la tecla SHIFT presionada mientras abres el libro y de ese modo no se habilitarn las macros. La tecla SHIFT es la que tiene una flecha grande que apunta hacia arriba (suele estar encima de la tecla CTRL)
Replicar BUSCARV con macros
Con esta macro se pueden hacer bsquedas, similar a lo que se hace usualmente con la funcin Excel BUSCARV. Visual Basic
Sub Buscar() 'Definimos variabl Dim lookupvalue As Variant, val value = Range("A1").value 'celd
1 2 3 4 Sub Buscar() 'Definimos variables 5 Dim lookupvalue As Variant, value As Variant, lookupRange As Range 6 value = Range("A1").value 'celda con el valor que buscamos 7 Set lookupRange = Range("A10:C100") 'rango donde buscar 8 'Queremos la columna 3 9 lookupvalue = Application.VLookup(value, lookupRange, 3, False) 10 'Si no encuentra valor finaliza 11 If IsError(lookupvalue) Then 12 Exit Sub 13 'Si lo encuentra lo devuelve 14 Else 15 MsgBox lookupvalue 16 End If 17 End Sub 18 19 20
Replicar BUSCARV con macros
Con esta macro se pueden hacer bsquedas, similar a lo que se hace usualmente con la funcin Excel BUSCARV.
Visual Basic
Sub Buscar() 'Definimos variabl Dim lookupvalue As Variant, val value = Range("A1").value 'celd
1 2 3 4 Sub Buscar() 'Definimos variables 5 Dim lookupvalue As Variant, value As Variant, lookupRange As Range 6 value = Range("A1").value 'celda con el valor que buscamos 7 Set lookupRange = Range("A10:C100") 'rango donde buscar 8 'Queremos la columna 3 9 lookupvalue = Application.VLookup(value, lookupRange, 3, False) 10 'Si no encuentra valor finaliza 11 If IsError(lookupvalue) Then 12 Exit Sub 13 'Si lo encuentra lo devuelve 14 Else 15 MsgBox lookupvalue 16 End If 17 End Sub 18 19 20
