|
|
|
|
|
|
|
|
|
Recopilación
de códigos y macros del grupo de noticias de Microsoft - Excel
news://msnews.microsoft.com/microsoft.public.es.excel
(La mayoría del experto Héctor
Miguel. Mi eterno agradecimiento por sus aportaciones y su saber hacer y estar)
(También de gran ayuda el
Blog de Jorge Dunkelman: Usar M.Excel eficientemente)
Abrir Visual Basic: ALT+F11
- o colocar el botón en la barra de herramientas (Personalizar la Barra de herramientas - Comandos - Herramientas)
Insertar módulo: Menú Insertar - Módulo: pegar el código facilitado.
Insertar código en una hoja en concreto: doble clic en el nombre de la hoja: pegar el código en la ventana de la derecha.
Insertar código en todo el libro: doble clic en ThisWorkbook.
Ejecutar macros creadas: ALT+F8
- o menú Herramientas - Macros - Macros, seleccionar y Ejecutar
Asignar método abreviado para ejecutar Macros:
menú Herramientas - Macros - Macros, seleccionar y pulsar el botón Opciones
Botón para Macro en la Barra de Herramientas:
Personalizar Barra de Herramientas - Comandos: Macros, arrastrar "Personalizar botón" a la Barra de Herramientas, pulsar con el botón derecho del ratón sobre el botón sacado y seleccionar "Asignar Macro".
También podemos cambiar la imagen asignada, cambiarle el nombre, etc.
1. Abrir en la última celda escrita
Insertar un módulo con lo siguiente:
Sub Auto_Open()
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub2. Abrir archivos automáticamente
Al abrir un libro que se abran automáticamente otros libros.
Insertar en el primer libro el siguiente código en ThisWorkbook:Private Sub Workbook_Open()
Workbooks.Open ("archivo1.xls")
Workbooks.Open ("archivo2.xls")
End Sub3. Marcar celdas con el botón derecho del ratón
Aplicación: resaltar celdas correctas. Se aplica el color determinado (rojo) en las celdas de las columnas A:D haciendo dos veces clic con el botón derecho.
Insertar el código en cada hoja (Worksheet) en el que queramos aplicarlo:Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Columns("a:d")) Is Nothing Then Exit Sub Else Cancel = True
With ActiveCell.Interior
If .ColorIndex = xlColorIndexNone _
Then .ColorIndex = 3 Else .ColorIndex = xlColorIndexNone
End With
End Sub4. Saltar a celdas concretas dentro de la misma hoja
Aplicación: introducir datos en una celda y saltar a la siguiente celda establecida.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case Is = "$F$1": Range("B4").Select
Case Is = "$B$4": Range("D3").Select
Case Is = "$D$3": Range("B9").Select
Case Is = "$B$9": Range("B5").Select
'y así sucesivamente...
End Select
End Sub5. Scroll delimitado en una hoja de un libro
Aplicación: impedir desplazar la hoja fuera del rango marcado en el código.
Insertar el código en ThisWorkbook:Private Sub Workbook_Open()
Worksheets("Hoja1").ScrollArea = "A1:K45"
End Sub6. Reemplazar mayúsculas por minúsculas dentro de las mismas celdas
Aplicación: Teniendo celdas con los nombres en mayúsculas para reemplazarlos por minúsculas, seleccionamos las celdas, abrimos Visual Basic y en la ventana Inspección (si no la tenemos a la vista, menú Ver - Ventana Inmediato o CTRL+G) pegamos la siguiente instrucción:
For Each c In Selection: c.Formula = Application.Proper(c): NextPulsar Entrar y cerrar la ventana de Visual Basic
7. Colorear fila de la celda seleccionada
Aplicación: tener una visión más clara de donde estamos introduciendo datos.
Insertar el código en la hojaPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
Static Fila_Ant As Integer
On Error Resume Next
If Target.Row = Fila_Ant Then Exit Sub
Range("a" & Fila_Ant & ":m" & Fila_Ant).Interior.ColorIndex = xlColorIndexNone
Range("a" & Target.Row & ":m" & Target.Row).Interior.ColorIndex = 20
Fila_Ant = Target.Row
End Sub8. Eliminar todos los nombres definidos
Aplicación:eliminar, mediante la ejecución de una macro, todos los nombre definidos a celdas y rangos en un libro.
Insertar el código en ThisWorkbookSub EliminarNombres()
For Each nombre In ActiveWorkbook.Names
nombre.Delete
Next nombre
End Sub9. Obtener en una hoja el nombre de todas las hojas de un libro
Una vez insertado el código, al abrir el libro tendremos los nombres de todas las hojas en la primera fila, salvo la primera columna.
En ThisWorkbook insertar el código:Private Sub Workbook_Open()
Dim contador As Integer
For contador = 2 To Sheets.Count
With Sheets(1)
.Cells(1, contador).Value = Sheets(contador).Name
End With
Next contador
End Sub
10. Código para realizar una consulta y filtrar
Agradecimiento personal a Héctor Miguel (Grupo Excel)
Ejemplo: Hoja Pagarés Clientes (CLIENTES) con columnas de la A:N con autofiltros (la columna 4 = vacías = sin pagar)
Se inserta una nueva Hoja (Consultas): en A1 el mismo dato que en la columna a consultar (Nombre Cliente), en A2 introduciremos el criterio.
Añadidas líneas de código para que no quite en Hoja Clientes el autofiltro aplicado.
En la nueva hoja (Consultas) agregamos el siguiente código:Private Sub Worksheet_Change(ByVal Target As Range)
Dim DejarAutoFiltros As Boolean
If Target.Address <> "$A$2" Then Exit Sub
With Worksheets("CLIENTES").Range("a1")
DejarAutoFiltros = .Parent.AutoFilterMode
.CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("a1:a2"), _
CopyToRange:=Range("a4:n4"), _
Unique:=False
If DejarAutoFiltros Then .AutoFilter Field:=4, Criteria1:=””
End With
End SubOriginal: eliminaba los autofiltros aplicados:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Worksheets("CLIENTES").Range("a1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("a1:a2"), _
CopyToRange:=Range("a4:n4"), _
Unique:=False
End SubHe añadido una nueva hoja con los nombres únicos de todos los clientes, ordenados alfabéticamente y los he definido con nombre "nombres".
En la Hoja Consultas - Celda A2 se utiliza Validación de datos: Lista - Omitir blancos - Celda con lista desplegable - Origen= nombres (Marcar Mensaje de entrada y Mensaje de error)
Con ello consigo que se despliege la lista de todos los clientes existentes sin tener que recordar su nombre para introducirlo en A2.... siguiente
Para tener acceso a las macros que deseemos siempre que ejecutemos EXCEL éstas deberán guardarse en el Libro de Macros Personal.
Se crea un libro llamado PERSONAL.XLS y dicho libro se ejecuta (abre) siempre que abramos EXCEL pero lo hace de manera oculta. Si tuviéramos que modificarlo lo mostraremos desde el menú VER - MOSTRAR, hacer las modificaciones, guardar y volver a ocultar (VER - OCULTAR)
En Windows XP se guarda en unidad:\Documents and Settings\nombre_usuario\Datos de programa\Microsoft\Excel\XLSTARTEste archivo lo podremos copiar entre diferentes usuarios y/o máquinas colocándolo en la misma trayectoria de los diferentes usuarios y/o máquinas y teniendo en cuenta que no exista otro PERSONAL.XLS.
Las macros generadas pueden tener adjudicado un botón en la barra de herramientas o bien ejecutarlas (ver el punto E de Instrucciones básicas)
1. Comparar datos entre dos hojas, espejo una de la otra y marcar las diferencias
Aplicación: Hoja2 copia de la Hoja1, para saber si en Hoja2 faltan datos o son diferentes de Hoja1.
(En la Hoja2 no debe haber datos que no estén en la Hoja1, el código sería diferente)Insertar el código en ThisWorkbook, cerrar Visual Basic y ejecutar la macro creada: ALT+F8
Sub MarcarFilasDiferentes()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim rng1 As Range, celda1 As Range
Dim lngFila1 As Long, lngFila2 As Long
Dim blnDiferencia As Boolean
Set wks1 = ThisWorkbook.Worksheets("Hoja1")
Set wks2 = ThisWorkbook.Worksheets("Hoja2")
lngFila1 = 1
lngFila2 = 1
While Not IsEmpty(wks1.Cells(lngFila1, 1))
blnDiferencia = False
Set rng1 = wks1.Range(Cells(lngFila1, 1), Cells(lngFila1, _
wks1.Cells(lngFila1, 256).End(xlToLeft).Column))
For Each celda1 In rng1.Cells
If wks2.Cells(lngFila2, celda1.Column).Value <> _
celda1.Value Then
rng1.Interior.ColorIndex = 8
blnDiferencia = True
Exit For
End If
Next
lngFila1 = lngFila1 + 1
If Not blnDiferencia Then lngFila2 = lngFila2 + 1
Wend
Set rng1 = Nothing
Set wks1 = Nothing
Set wks2 = Nothing
End Sub2. Ir a la última celda con datos
Aplicación: seguir introduciendo datos a partir de la última fila-celda con datos.
Insertar un módulo con el siguiente código:
Sub UltimaFila()
Range("A65536").End(xlUp).Offset(1, 0).Select
End SubEjecutar la macro cuando se necesite (ALT+F8) o asignarle un método abreviado o colocar un botón en la barra de herramientas.
3. Cambiar el tamaño de los comentarios
Insertar el código en ThisWoork y una vez realizado ejecutar la macro cuando se necesite.
Sub DarFormatoAComentarios()
Dim cmtC As Comment
For Each cmtC In ActiveSheet.Comments
cmtC.Shape.TextFrame.Characters.Font.Size = 9
cmtC.Shape.Width = 150
cmtC.Shape.Height = 150
cmtC.Shape.AutoShapeType = msoShapeRectangle
Next cmtC
Set cmtC = Nothing
End Sub4. Macro para copiar el contenido seleccionado en otra hoja
Insertar el siguiente código:
Sub CopiaColumnas()
Dim mirango As Range
Dim col As Range
Dim NuevaHoja As Worksheet
Dim i As Integer
Set mirango = Selection
Worksheets.Add
i = 0
For Each col In mirango.Columns
col.Copy ActiveSheet.Range("A1").Offset(, i)
i = i + 1
Next col
End Sub5. Proteger y Desproteger todas las hojas de un libro mediante macros:
Insertar en ThisWorkbook el siguiente código
Sub XXX()
Dim Hoja As Worksheet
For Each Hoja In Worksheets
Hoja.Protect "clave_a_asignar"
Next
End Sub
Sub ZZZ()
Dim Hoja As Worksheet
For Each Hoja In Worksheets
Hoja.Unprotect "clave_a_asignar"
Next
End Sub
- XXX y ZZZ son los nombres de macro a ejecutar. Modificar la clave elegida.
- Insertar dos botones y asignar ambas macros en todas las hojas: Crear en la primera hoja y luego copiar pegar en el resto de hojas.
...
1. Comentario en celda... sin insertar comentario
Para añadir una anotación en una celda sin tener que insertar un comentario introduciremos el dato de la siguiente manera:=1500+N("cuota mensual")Cuando nos coloquemos sobre la celda, veremos en la barra de fórmulas todo el contenido y podremos operar con dicha celda normalmente.
2. Crear rápidamente nombres de rangos
Si ya tenemos en la primera fila los nombres definidos como encabezados, seleccionarlos, menú Insertar - Nombre - Crear: Fila superior. Si los nombres están en la primera columna, seleccionar: Columna izquierda.1- Para ver los nombres de rangos de una hoja, seleccionar el nivel de Zoom a 39%
2- Pulsando F3 vemos todos los nombres de rangos definidos, podemos seleccionar Pegar lista y nos insertará en la celda en la que estemos situados los nombres y los rangos a los que corresponden.
3. Ver fórmulas rápidamente: pulsar ALT+º
4. Seleccionar el rango de datos existentes (sin existir celdas vacías): pulsar CTRL+*
5. Movernos entre diferentes hojas del libro: CTRL+AvPág o CTRL+RePág
6. Insertar filas y columnas con el ratón: situarnos en el punto de inserción (el puntero cambia al símbolo +), pulsar MAY (el puntero cambia a una doble línea con dos flechas) y arrastrar hacia abajo (filas) o hacia la derecha (columnas). Para realizar esto se debe hacer en filas o columnas con datos.
7. Escribir varias filas en una misma celda: pulsar entre líneas ALT+Intro
8. Introducir los mismos datos en varias hojas (por ejemplo, encabezados de columna):
Seleccionar las hojas (si son correlativas mantener pulsada la tecla MAY y hacer clic en cada hoja a seleccionar, si no son correlativas seleccionarlas manteniendo CTRL), escribir los datos comunes en cada hoja y hacer clic en cualquier hoja o botón derecho sobre el nombre de la hoja y seleccionar "Desagrupar hojas" (existe también la opción "Seleccionar todas las hojas, más rápido si son muchas hojas)9. Cálculo de días (EDAD): en A1 la fecha de nacimiento, en otra celda la siguiente fórmula:
=SIFECHA(A1;HOY();"y")&"Año(s), "&SIFECHA(A1;HOY();"ym")&"Mes(es) y "&SIFECHA(A1;HOY();"md")&"Día(s)"
Nos halla los años, meses y días de la persona. Si en otra celda colocamos la fórmula
=HOY()-A1
Nos da el total en días.Días para realizar una tarea: calcular
a. Día de comienzo (A1) + días para realizarla (A2) = ¿cuándo se termina? (A3) =A1+A2
a1. sin días festivos, sólo laborables: =DIA.LAB(A1;A2)
b. Día a finalizar una tarea + días para realizarla (A2) = ¿cuándo hay que comenzar? (A3) = A1-A2
b1. sin días festivos, sólo laborables: =DIA.LAB(A1;-A2)10. Evitar repetición de números en una columna:
Seleccionar la columna, menú Datos, Validación. En Permitir seleccionar Personalizada, marcar Omitir Blancos y en Fórmula introducir:
=CONTAR.SI(A:A;A1)<2
Podemos añadir un mensaje de error en la ficha correspondiente para cuando se introduzca un número repetido.11. Colorear rangos de filas mediante el formato condicional:
Seleccionar un rango, por ejemplo, A1:H60, formato condicional, fórmula:Condición 1 =RESIDUO(ENTERO((FILA()-1)/12);2)=0 Formato color de celda deseado (azul)
Condición 2 =RESIDUO(ENTERO((FILA()-1)/12);2)=1 Formato color de celda deseado (naranja)
Resultado: cada 12 filas del rango A:H se colorea con los diferentes colores (por ejemplo para diferenciar todo un año con sus 12 meses)
12 - 24 -36 - 48 - 60Otra variación:
Condición 1 =RESIDUO(FILA();5)=0
Resultado: cada 5 filas nos colorea la quinta fila en el color seleccionado. 5 -10 -15 -20....
Cambiando el "0" por "1" las filas 1 - 6 - 11 - 16...
Aplicable para cambios de semana laboral (lunes-viernes), por ejemplo.=RESIDUO(FILA()-1;4)<2
Resultado: Colorea el rango seleccionado de dos en dos filas12. Ver todas las hojas de un libro (cuando son tantas que no se pueden ver en la barra de hojas):
Botón derecho del ratón sobre la zona de flechas de desplazamiento de hojasSe puede hacer también por macro: (absurdo... ¿o no?)
Sub CambiarHoja()
With Application.CommandBars("Workbook Tabs").Controls(16)
If Right(.Caption, 3) = "..." Then .Execute Else .Parent.ShowPopup
End With
End Sub
13. Hipervínculo a Hoja mediante fórmula:
Insertar en una celda una fórmula y con ella hallar un hipervínculo a una hoja de cálculo. (El mismo resultado que si insertamos hipervínculo manualmente, a un lugar de este documento, seleccionar Hoja)=EXTRAE(CELDA("nombrearchivo";nombre_hoja!A1);ENCONTRAR("]";CELDA("nombrearchivo";A1))+1;31)
14. Hallar la ruta, nombre de archivo y nombre de hoja:
=CELDA("Filename")
15. Hallar la referencia de un rango definido con un nombre:
=CELDA("direccion";nombre_rango)&":"&CELDA("direccion";DESREF(nombre_rango;FILAS(nombre_rango)-1;COLUMNAS(nombre_rango)-1))- cambiar nombre_rango (4) por el nombre dado.
16. Referencias dinámicas con nombre (del blog de Jorge Dunkelman ver la explicación completa original)
=DESREF(Hoja1!$A$1;0;0;CONTARA(Hoja1!$A:$A);CONTARA(Hoja1!$1:$1))Para su uso con tablas dinámicas: Se crea una "base de datos": definir nombre (td_rango) con referencia a la fórmula arriba indicada. cada vez que se agregan nuevas filas automáticamente están agregadas en la tabla dinámica. Sólo hay que actualizar.
Eficaz para no tener que controlar el rango al que se refiere la tabla dinámica.17. Separar el contenido de celdas:
Supongamos que tenemos una celda con el contenido A150-2008 y queremos separar dicho contenido en dos columnas A150 2008:
Seleccionamos la celda o celdas, menú Datos - Texto en columnas: delimitador "-".. siguiente
