excel visual basic
TRANSCRIPT
APUNTES DE PROGRAMACION EXEL CON VISUAL BASIC
REV FECHA BREVE DESCRIPCION DEL CAMBIO PREPARADO POR REVISADO POR APROBADO PORFIRMA DE
APROBACION
A 00/00/00 XXXXX X. Xxxxx X. Xxxx X. Xxxx X. Xxxx
0 00/00/00 XXXX X. Xxxxx X. Xxxxx X. Xxxxx X. Xxxxx
Página 1 de 27
TABLA DE CONTENIDO
1. CREAR UN NUEVO LIBRO EN EXCEL........................................................................................5
2. ESCRIBIR EN UNA CELDA...........................................................................................................5
3. BORDE, COLOR Y GROSOR DE UNA CELDA..........................................................................5
4. CONSEGUIR EL TOTAL FILAS Y COLUMNAS DE UN ARCHIVO..............................................5
5. CONOCER EL NOMBRE DE LA ÚLTIMA COLUMNA ESCRITA.................................................6
6. AUTO AJUSTAR LA COLUMNA EN EXCEL................................................................................6
7. GUARDAR UN ARCHIVO EXCEL CON EL OBJETO COMMONDIALOG...................................6
8. COPIAR TODOS LOS REGISTROS FILTRADOS DE QUERY EN UNA HOJA EXCEL..............6
8.1. Ejemplo:................................................................................................................................ 7
9. IMPORTAR DATOS DE UNA HOJA EXCEL A UNA BASE DE DATOS ACCESS......................8
9.1. Ejemplo:................................................................................................................................ 8
10. COLOCAR COLOR DE FONDO A UNA CELDA EXCEL.............................................................8
11. COMBINAR CELDAS EN EXCEL..................................................................................................8
12. CONTAR CANTIDAD HOJAS EXCEL...........................................................................................8
13. AGREGAR HOJAS A ARCHIVO EXCEL......................................................................................8
14. ELIMINAR HOJAS DE ARCHIVO EXCEL.....................................................................................9
15. COLOCAR NOMBRE A LA HOJA EXCEL....................................................................................9
16. ACTIVAR HOJA EXCEL................................................................................................................9
17. INSERTAR UNA IMAGEN A UN ARCHIVO EXCEL.....................................................................9
18. CONFIGURACIÓN DE LA ORIENTACIÓN DE LA HOJA EXCEL................................................9
19. MOVER Y COPIAR UNA HOJA AL FINAL DE OTRA HOJA EN EXCEL.....................................9
20. TRANSPONER INFORMACIÓN DE EXCEL.................................................................................9
21. OBTENER LA ULTIMA COLUMNA Ó FILA ESCRITA EN EXCEL.............................................10
22. SELECCIONA UNA CELDA........................................................................................................10
23. SELECCIONAR UNA COLUMNA................................................................................................10
24. SELECCIONAR RANGOS DISCONTINUOS...............................................................................10
25. SELECCIONAR UNA CELDA DEL VALOR DE UNA VARIABLE..............................................10
26. SELECCIONAR UN RANGO INDICADO POR VARIABLES......................................................11
27. SELECCIONAR COLUMNAS, FILAS Y CELDAS INDEPENDIENTES......................................11
28. SELECCIONAR CELDAS EN RELACIÓN A OTRA....................................................................11
29. SELECCIONAR Y AMPLIAR UN RANGO...................................................................................11
30. SELECCIONAR: COMBINA CELDAS.........................................................................................12
Página 2 de 27
31. SELECCIONAR: RANGO DE CELDA ACTIVA...........................................................................12
32. SELECCIONAR: DESDE HACIA ABAJO...................................................................................12
33. SELECCIONAR UN RANGO HACIA LA DERECHA...................................................................12
34. SELECCIONAR UN RANGO HACIA LA IZQUIERDA.................................................................13
35. SELECCIONAR UN RANGO HACIA ARRIBA............................................................................13
36. SELECCIONAR LA ÚLTIMA CELDA CON DATOS....................................................................13
37. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NÚMERO.........................13
38. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NRO. CASO 1..................13
39. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NRO. CASO 2..................14
40. SELECCIONAR: LA ÚLTIMA CELDA CON DATOS...................................................................14
41. SELECCIONAR UNA CELDA E INDICA SU POSICIÓN RELATIVA Y ABSOLUTA..................14
42. SELECCIONAR UNA HOJA, PREVIA A LA ACTIVA.................................................................14
43. SELECCIONAR UNA HOJA, POSTERIOR A LA ACTIVA..........................................................15
44. COPIAR: DADA UNA CONDICIÓN.............................................................................................15
45. BORRAR: UNA CELDA...............................................................................................................15
46. BORRA: UNA FILA...................................................................................................................... 16
47. RELLENAR CELDAS................................................................................................................... 16
48. RELLENAR EN VARIAS HOJAS.................................................................................................16
49. FORMATO A LAS CELDAS DEL RANGO SELECCIONADO (NEGRITA, CURSIVA, SUBRAYADO, COLOR, ALINEACIÓN)......................................................................................16
50. FORMATO DE MINÚSCULA A MAYÚSCULA. CASO 1.............................................................17
51. FORMATO DE MINÚSCULA A MAYÚSCULA. CASO 2.............................................................17
52. FORMATO DE MAYÚSCULA A MINÚSCULA. CASO 3.............................................................17
53. COLORES: COLOREAR LAS CELDAS DE UN RANGO SEGÚN SU VALOR..........................17
54. COLORES: RECORRE UN RANGO COLOREANDO LAS CELDAS SEGÚN SU VALOR........18
55. COLORES: OCULTA FILA CON COLOR DETERMINADO........................................................18
56. MOSTRAR: COLUMNA...............................................................................................................19
57. HOJA: DAR NOMBRE A LAS HOJAS........................................................................................19
58. COMENTARIO: INSERTAR UN COMENTARIO.........................................................................19
59. NOMBRE: INSERTAR NOMBRE A LA HOJA............................................................................20
60. NOMBRE: INSERTAR NOMBRE A MAS DE UNA HOJA..........................................................20
61. INSERTAR UNA COLUMNA........................................................................................................20
62. INSERTAR UN NOMBRE A UNA CELDA...................................................................................20
63. ORDENAR: ORDENAR DATOS DE UN RANGO POR UNA COLUMNA...................................20
64. COMPARAR: 2 COLUMNAS ORDENADAS ( DE DISTINTAS HOJAS)....................................21
65. COMPARAR: 2 COLUMNAS, COPIAR EN OTRO DESTINO SOLO REGISTROS ÚNICOS.....21
66. PROTEGER Y DESPROTEGER: UNA HOJA Y COPIAR LOS DATOS EN OTRA....................22
Página 3 de 27
67. IMPRIMIR: HOJA ACTIVA...........................................................................................................22
68. IMPRIMIR: SELECCIONAR ÁREA DE IMPRESIÓN...................................................................22
69. IMPRIMIR: CONFIGURAR IMPRESIÓN......................................................................................23
70. ABRIR: LIBRO............................................................................................................................. 23
71. CERRAR: CERRAR LIBRO SIN GUARDAR...............................................................................23
72. CERRAR: CERRAR LIBRO Y GUARDAR..................................................................................23
73. GUARDAR: LIBRO Y DESACTIVA ALARMA DE AVISO...........................................................24
74. SELECCIONAR CELDAS EN RELACIÓN A OTRA....................................................................24
75. ACTIVA LAS OPCIONES DE ALERTA.......................................................................................24
76. INPUTBOX Y MSGBOX: INGRESAR DATOS Y TENER RESPUESTA.....................................24
77. MENSAJE: ESTRUCTURA SIMPLE...........................................................................................25
78. MENSAJE: CON AVISOS DE OPCIONES..................................................................................25
79. MENSAJE: REDONDEO DE VALORES DENTRO DEL MENSAJE...........................................25
80. BOTONES: BARRA DE HERRAMIENTAS.................................................................................25
81. LLAMAR A UNA MACRO DENTRO DE UNA RUTINA...............................................................26
82. EJECUTAR MACRO SEGÚN EL VALOR DE UNA CELDA.......................................................26
83. POSICION DE LA COLUMNA DE LA CELDA ACTIVA................................................................26
Página 4 de 27
1. CREAR UN NUEVO LIBRO EN EXCEL
Set objExcel = New Excel.Application
Set Libro = objExcel.Workbooks.Add
giHojaActual = 1
Set Hoja = objExcel.Worksheets(giHojaActual)
objExcel.Visible = True
2. ESCRIBIR EN UNA CELDA
Hoja.Cells(fila , columna) =
3. BORDE, COLOR Y GROSOR DE UNA CELDA
Hoja.Cells(fila, Columna).Borders(xlEdgeBottom).LineStyle = xlContinuous
Hoja.Cells(fila, Columna).Borders(xlEdgeBottom).ColorIndex = 14
Hoja.Cells(fila, Columna).Borders(xlEdgeBottom).Weight = xlThick
‘****Con rango de columnas
rango = "A1:C4"
Hoja.Range(rango).Borders(xlEdgeBottom).LineStyle = xlContinuous
Hoja.Range(rango).Borders(xlEdgeBottom).ColorIndex = 14
Hoja.Range(rango).Borders(xlEdgeBottom).Weight = xlThick
4. CONSEGUIR EL TOTAL FILAS Y COLUMNAS DE UN ARCHIVO
'***Da el numero de filas del archivo
i = 1
Do While Trim(Hoja.Cells(i, 1)) <> ""
DoEvents
i = i + 1
Loop
i = i - 1
'***Da el numero de columnas del archivo
Página 5 de 27
j = 1
Do While Trim(Hoja.Cells(1, j)) <> ""
DoEvents
j = j + 1
Loop
5. CONOCER EL NOMBRE DE LA ÚLTIMA COLUMNA ESCRITA
'***Procedimiento donde se obtiene el nombre de la columna final***
j = j - 1
sValor = Cells(1, j).EntireColumn.Address
iPos = InStr(1, sValor, ":")
If Not iPos = 0 Then
sNombre = Left(sValor, iPos - 1)
sNombre = Mid(sNombre, 2, Len(sNombre) - 1)
End If
sOrigen = Trim(sNombre) & Trim(Str(i))
6. AUTO AJUSTAR LA COLUMNA EN EXCEL
Hoja.Range("A:B").EntireColumn.AutoFit
7. GUARDAR UN ARCHIVO EXCEL CON EL OBJETO COMMONDIALOG
CmDialogGuardar.CancelError = True
On Error GoTo ErrHandler
CmDialogGuardar.DialogTitle = "GUARDAR EN"
CmDialogGuardar.FileName = "Reporte de " & Trim(Proyecto)
CmDialogGuardar.ShowSave
Ruta = CmDialogGuardar.FileName
Hoja.SaveAs Ruta
8. COPIAR TODOS LOS REGISTROS FILTRADOS DE QUERY EN UNA HOJA EXCEL
Página 6 de 27
' activo la hoja y la celda en que quiero dejar los datos
objExcel.ActiveSheet.Range("A2").Activate
Hoja.Application.ActiveCell.CopyFromRecordset Rss3
8.1. Ejemplo:
Set objExcel = New Excel.Application
Set Libro = objExcel.Workbooks.Add
Set Hoja = objExcel.Worksheets(1)
Hoja.Activate
sql2 = "SELECT MUESTRA.CODIGO_CLIENTES, MUESTRA.STATUS, "
sql2 = sql2 & "MUESTRA.RAZON_SOCIAL, MUESTRA.DIRECCION, MUESTRA.DISTRIBUIDORA, "
sql2 = sql2 & "MUESTRA.RUTA, MUESTRA.JEFATURA, MUESTRA.MERCADEO, "
sql2 = sql2 & "MUESTRA.LONGITUD, MUESTRA.LATITUD, MUESTRA.GEOREF From MUESTRA "
sql2 = sql2 & "WHERE (((MUESTRA.FECHA)=#" & Format(gsFecha, "mm/dd/yyyy") & "#) AND "
sql2 = sql2 & "((MUESTRA.DISTRIBUIDORA)='" & sDistribuidora & "') AND"
sql2 = sql2 & "((MUESTRA.RUTA)='" & sRuta & "'));"
Rss3.Open sql2, GSC, adOpenDynamic, adLockOptimistic
If Not Rss3.EOF Then
For i = 0 To Rss3.Fields.Count - 1
Hoja.Cells(1, i + 1) = Rss3.Fields(i).Name
Hoja.Cells(1, i + 1).Interior.Color = &HC0C0C0
Next i
' activo la hoja y la celda en que quiero dejar los datos
objExcel.ActiveSheet.Range("A2").Activate
Hoja.Application.ActiveCell.CopyFromRecordset Rss3
End If
Rss3.Close
Hoja.SaveAs sArchivoExcel
objExcel.Quit
Página 7 de 27
9. IMPORTAR DATOS DE UNA HOJA EXCEL A UNA BASE DE DATOS ACCESS
9.1. Ejemplo:
Dim sRutaXLS As String
sRutaXLS = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ruta & "\" & nombre del archive excel & _
";Extended Properties=Excel 8.0"
dbs.Open sRutaXLS
dbs.CursorLocation = adUseClient
sCadena = "INSERT INTO [nombre tabla] IN '" & ruta y nombre de la BD & _
"' SELECT [" & nombre del archivo Excel sin extensión & _
"$].* FROM [" & nombre del archivo Excel sin extensión & "$]"
dbs.Execute sCadena
dbs.Close
10. COLOCAR COLOR DE FONDO A UNA CELDA EXCEL
Hoja.Cells(Fila, columna).Interior.Color = &HFFFF&
11. COMBINAR CELDAS EN EXCEL
Hoja.Range("E1:I1").MergeCells = True
Hoja.Cells(fila, columna) .MergeCells = True
12. CONTAR CANTIDAD HOJAS EXCEL
objExcel.Sheets.Count
13. AGREGAR HOJAS A ARCHIVO EXCEL
Worksheets.Add
Página 8 de 27
14. ELIMINAR HOJAS DE ARCHIVO EXCEL
objExcel.Sheets(numero de hoja).Delete
15. COLOCAR NOMBRE A LA HOJA EXCEL
Hoja.Name=”Prueba”
16. ACTIVAR HOJA EXCEL
giHojaActual = 2
Set Hoja = objExcel.Worksheets(giHojaActual)
Hoja.Activate
17. INSERTAR UNA IMAGEN A UN ARCHIVO EXCEL
'***Inserta una imagen en el archivo excel***
objExcel.ActiveSheet.Pictures.Insert(App.Path & "\EMEVENCA.JPG").Select
18. CONFIGURACIÓN DE LA ORIENTACIÓN DE LA HOJA EXCEL
'***Configura la página de excel y la coloca de forma Horizontal****
objExcel.ActiveSheet.PageSetup.Orientation = xlLandscape
19. MOVER Y COPIAR UNA HOJA AL FINAL DE OTRA HOJA EN EXCEL
Worksheets("Hoja1").Copy After:=Libro.Worksheets("Hoja1")
20. TRANSPONER INFORMACIÓN DE EXCEL
'******* codigo para transponer un rango en excel
lastcol = Hoja.Cells(12, Columns.Count).End(xlToLeft).Column
With Hoja
.Range(.Cells(12, 1), .Cells(i, lastcol)).SpecialCells(xlCellTypeConstants).Copy
.Range("A26").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
Página 9 de 27
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False
21. OBTENER LA ULTIMA COLUMNA Ó FILA ESCRITA EN EXCEL
‘***para la fila
Hoja.Cells(Rows.Count, 1).End(xlUp).Row
‘***para la columna
Hoja.Cells(1, Columns.Count).End(xlToLeft).Column
22. SELECCIONA UNA CELDA
Sub selecciono01()
Range("B7").Select 'selecciona la celda B7
End Sub
23. SELECCIONAR UNA COLUMNA
Sub selecciono02()
Range("B:B").Select 'selecciona toda la columna B
End Sub
24. SELECCIONAR RANGOS DISCONTINUOS
Sub selecciono03()
Range("A4:A10, D10, B5:B20").Select 'selecciona rangos discontinuos
End Sub
25. SELECCIONAR UNA CELDA DEL VALOR DE UNA VARIABLE
Sub selecciono04()
Dim variable As Integer
variable = 5
Página 10 de 27
Range("A" & variable).Select 'selecciona la celda cuya fila será el valor de la variable
End Sub
26. SELECCIONAR UN RANGO INDICADO POR VARIABLES
Sub selecciono05()
variable = 5
rango = ("B2" & ":E" & variable)
Range(rango).Select 'selecciona el rango B2:E hasta la fila indicada en la variable
End Sub
27. SELECCIONAR COLUMNAS, FILAS Y CELDAS INDEPENDIENTES
Sub selecciono06()
Range("A:A, D:F").Select 'selecciona las columnas A, D, E y F
End Sub
Sub selecciono061()
Range("2:2, 4:7").Select 'selecciona las filas 2 y desde 4 hasta la 7.
End Sub
Sub selecciono07()
Sheets(1).Range("A1").Offset(2, 3).Select 'selecciona la celda D3
End Sub
28. SELECCIONAR CELDAS EN RELACIÓN A OTRA
Sub selecciono08()
Range("D3").Select
ActiveCell.Offset(-2, 1).Select 'selecciona la celda que se encuentra 2 filas por encima
'y 1 columna a la derecha de la celda activa (=D3)
End Sub
29. SELECCIONAR Y AMPLIAR UN RANGO
Sub selecciono09()
Página 11 de 27
'ampliar el rango de selección
Range("A1:B5").Select
Selection.Resize(10, 4).Select '(en total serán 10 filas, 4 columnas)
'el resultado será A1:D10
End Sub
30. SELECCIONAR: COMBINA CELDAS
Sub selecciono10()
'combina celdas seleccionadas
Range("B1:E1").Select
Selection.Merge
End Sub
31. SELECCIONAR: RANGO DE CELDA ACTIVA
Sub selecciono11()
'selecciona la región o rango de la celda activa
Range("B2").CurrentRegion.Select
End Sub
32. SELECCIONAR: DESDE HACIA ABAJO
Sub selecciono12()
Range("A2", Range("A2").End(xlDown)).Select 'seleciona desde A2 hacia abajo
End Sub
33. SELECCIONAR UN RANGO HACIA LA DERECHA
Sub selecciono13()Range("A2", Range("A2").End(xlToRight)).Select 'selecciona desde A2 hacia la derechaEnd Sub
Página 12 de 27
34. SELECCIONAR UN RANGO HACIA LA IZQUIERDA
Sub selecciono14()Range("C2", Range("C2").End(xlToLeft)).Select 'selecciona desde C2 hacia la izquierdaEnd Sub
35. SELECCIONAR UN RANGO HACIA ARRIBA
Sub selecciono15()Range("A15", Range("A15").End(xlUp)).Select 'selecciona desde A15 hacia arribaEnd Sub
36. SELECCIONAR LA ÚLTIMA CELDA CON DATOS
Sub selecciono16()'para seleccionar solo la última celda con datosRange("A1").End(xlDown).SelectEnd Sub
37. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NÚMERO
Sub selecciono17()Dim filalibre As IntegerActiveWorkbook.Sheets(1).Activate 'selecciona la Hoja 1Range("A1").Select'el control se hará sobre la columna A, a partir de la fila While ActiveCell.Value <> ""ActiveCell.Offset(1, 0).Select'si la celda contiene datos, pasa a la fila siguienteWendfilalibre = ActiveCell.Row'guarda en la variable filalibre el número de la primer fila sin datos.'muestra el valor en celda "B2"Range("B2") = filalibreEnd Sub
38. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NRO. CASO 1
Sub selecciono118()Dim filalibre As IntegerWorksheets("Hoja1").Selectfilalibre = Range("A1").End(xlDown).Offset(1, 0).Row'la variable filalibre guarda el nro. de la primer celda vacía.'muestra el valor en celda "B2"Range("B2") = filalibreEnd Sub
Página 13 de 27
39. SELECCIONAR LA ÚLTIMA CELDA CON DATOS Y GUARDA EL NRO. CASO 2
Sub ActualizaHoja()'Buscar la última celda con datos, en una columnaSheets(1).Select'selecciona la Hoja 1Range("A2").Select'el control se hará sobre la columna A, a partir de la filaWhile ActiveCell.Value <> ""ActiveCell.Offset(1, 0).Select'si la celda contiene datos, pasa a la fila siguienteWendfila1 = ActiveCell.Row'guarda en la variable fila1 el número de la primer fila sin datos.End Sub
40. SELECCIONAR: LA ÚLTIMA CELDA CON DATOS
Sub buscaultima()'Buscar la última celda con datos, en una columnaSheets("Hoja3").SelectIf Range("A2").Value <> "" Thenfilalibre = Range("A1").End(xlDown).Offset(1, 0).Row + 1Elsefilalibre = 2End If'la variable filalibre guarda el nro. de la primer celda vacía.End Sub
41. SELECCIONAR UNA CELDA E INDICA SU POSICIÓN RELATIVA Y ABSOLUTA
Sub guardadirecc()Dim lugar1 As String, lugar2 As StringRange("B3").Selectlugar1 = ActiveCell.Address'guarda la referencia absolutalugar2 = ActiveCell.Address(False, False)'guarda la referencia relativa'devuelve en C3 las 2 direcciones concatenadasRange("C3").Value = lugar1 & "-" & lugar2End Sub
42. SELECCIONAR UNA HOJA, PREVIA A LA ACTIVA
Sub seleccion1()Sheets("Hoja2").SelectActiveSheet.Previous.Select 'hoja anterior a la activaRange("A2") = ActiveSheet.Name 'nombre de la hoja
Página 14 de 27
Range("A3") = ActiveSheet.Index 'número de hojaEnd Sub
43. SELECCIONAR UNA HOJA, POSTERIOR A LA ACTIVA
Sub seleccion2()Sheets("Hoja2").ActivateActiveSheet.Next.Select 'hoja posterior a la activaEnd Sub
44. COPIAR: DADA UNA CONDICIÓN
Sub Copiando()'copia los registros de la hoja Hoja2 cuyo mes = celda A1 de la Hoja3Dim nromes As Integer, mes As IntegerDim filadestino As IntegerDim dato As String'la variable nromes indica el mes de los registros a copiarnromes = Sheets("Hoja3").Range("A1").Value'variable que indica a partir de qué fila se copiaráfiladestino = 2'busca registros cuya fecha tiene por mes el valor de la variable nromesSheets("Hoja2").ActivateRange("A1").SelectWhile ActiveCell.Value <> ""dato = ActiveCell.Value'obtiene el número de mes del campo fechames = Month(dato)If mes = nromes ThenSelection.EntireRow.CopyActiveSheet.Paste Destination:=Worksheets(3).Cells(filadestino, 1)filadestino = filadestino + 1End IfActiveCell.Offset(1, 0).SelectWendApplication.CutCopyMode = FalseEnd Sub
45. BORRAR: UNA CELDA
Sub borrando()'borra el contenido de las celdas selecciondasRange("C2").SelectSelection.ClearContentsEnd Sub
Página 15 de 27
46. BORRA: UNA FILA
Sub eliminando()'elimina las filas seleccionadasSelection.EntireRow.DeleteEnd Sub
47. RELLENAR CELDAS
Sub llenando_celdas()Dim valor As StringDim celdita As RangeFor Each celdita In ActiveSheet.Range("A10:B13")valor = InputBox("Ingrese valor: ")celdita.Value = valorNext celditaEnd Sub
48. RELLENAR EN VARIAS HOJAS
Sub llenando_hojas()Dim hoja As WorksheetFor Each hoja In Sheetshoja.Range("E13").Value = Datehoja.Range("F13").Value = TimeNext hojaEnd Sub
49. FORMATO A LAS CELDAS DEL RANGO SELECCIONADO (NEGRITA, CURSIVA, SUBRAYADO, COLOR, ALINEACIÓN)
Sub formato()'dando formato a las celdas del rango seleccionadoRange("A1:D10").SelectWith SelectionFont.Bold = True'formato negritaFont.Italic = True'formato cursivaFont.Underline = xlUnderlineStyleSingle'subrayado simpleFont.Color = RGB(255, 0, 0)'color de fuente (para estos valores será rojo)HorizontalAlignment = xlCenter'alineación central (Right=derecha, Left=izquierda)
Página 16 de 27
End WithEnd Sub
50. FORMATO DE MINÚSCULA A MAYÚSCULA. CASO 1
Sub menorAmayor()Dim cell As RangeDim rango As Object'convierte texto en mayúsculasSet rango = SelectionFor Each cell In rangocell.Value = UCase(cell.Value)NextEnd Sub
51. FORMATO DE MINÚSCULA A MAYÚSCULA. CASO 2
Sub menorAmayor()'Pasar de minúsculas a mayúsculasSet rango = Range("a1:a3")For Each Cell In rangoCell.Value = UCase(Cell.Value)NextEnd Sub
52. FORMATO DE MAYÚSCULA A MINÚSCULA. CASO 3
Sub mayorAmenor()'Pasar de mayúsculas a minúsculasSet rango = Range("a1:a3")For Each Cell In rangoCell.Value = LCase(Cell.Value)NextEnd Sub
53. COLORES: COLOREAR LAS CELDAS DE UN RANGO SEGÚN SU VALOR
Sub colores1()Dim fila1 As Integer, fila2 As IntegerDim col1 As Integer, col2 As IntegerDim fila As Integer, columna As Integer'recorre el rango A1:E20 de la hoja activa coloreando las celdas según su valorfila1 = 1fila2 = 20col1 = 1col2 = 5For fila = fila1 To fila2
Página 17 de 27
For columna = col1 To col2If Cells(fila, columna).Value < 1000 ThenCells(fila, columna).Font.ColorIndex = 5 'azulElseIf Cells(fila, columna).Value < 1500 ThenCells(fila, columna).Font.ColorIndex = 3 'rojoElseCells(fila, columna).Font.ColorIndex = 9 'marrónEnd IfEnd IfNext columnaNext filaEnd Sub
54. COLORES: RECORRE UN RANGO COLOREANDO LAS CELDAS SEGÚN SU VALOR
Sub colores2()Dim fila1 As Integer, fila2 As IntegerDim col1 As Integer, col2 As IntegerDim fila As Integer, columna As Integer, valor As Integer'recorre el rango A1:E20 de la hoja activa coloreando las celdas según su valorfila1 = 4fila2 = 20col1 = 1col2 = 5For fila = fila1 To fila2For columna = col1 To col2valor = Cells(fila, columna).ValueSelect Case valorCase Is < 1000Cells(fila, columna).Font.ColorIndex = 5 'azulCase 1000 To 1499Cells(fila, columna).Font.ColorIndex = 3 'rojoCase 1500 To 1999Cells(fila, columna).Font.ColorIndex = 9 'marrónCase 2000 To 2999Cells(fila, columna).Font.ColorIndex = 7 'fucsiaCase ElseCells(fila, columna).Font.ColorIndex = 6 'amarilloEnd SelectNext columnaNext filaEnd Sub
55. COLORES: OCULTA FILA CON COLOR DETERMINADO
Página 18 de 27
Sub OcultaPorColor()
'recorre la col A de la hoja activa, si encuentra celda con color de fuente automática oculta la filaRange("A3").SelectWhile ActiveCell.Value <> ""If ActiveCell.Font.ColorIndex = xlColorIndexAutomatic ThenActiveCell.EntireRow.Hidden = TrueEnd IfActiveCell.Offset(1, 0).SelectWend'para ocultar filas con color de fuente rojo será:'If ActiveCell.Font.ColorIndex = 3 Then'buscar la lista de colores en la AyudaEnd Sub
56. MOSTRAR: COLUMNA
Sub MuestraTodas()'muestra filas ocultas de la hoja ColoresApplication.ScreenUpdating = False'Desactive la actualización de la pantalla para acelerar el código de la macroSheets("Hoja1").SelectRows("2:1000").SelectSelection.EntireRow.Hidden = FalseRange("C3").SelectApplication.ScreenUpdating = True'Recuerde que debe volver a establecer la propiedad ScreenUpdating como True cuando finalice la macro.End Sub
57. HOJA: DAR NOMBRE A LAS HOJAS
Sub nombre_hoja()Dim MiNombre As StringDim hoja As WorksheetFor Each hoja In WorksheetsMiNombre = InputBox("Ingrese nombre de hoja")hoja.Name = MiNombreNext hojaEnd Sub
58. COMENTARIO: INSERTAR UN COMENTARIO
Sub comentarios()Range("B2").Select 'previamente inserte un comentario en la celda "B2"ActiveCell.Offset(0, 1).Value = ActiveCell.Comment.Text'copia el comentario de la celda activa en la celda que
Página 19 de 27
'se encuentra en la columna siguiente.End Sub
59. NOMBRE: INSERTAR NOMBRE A LA HOJA
Sub nombre_hoja()Dim MiNombre As StringMiNombre = InputBox("Ingrese nombre de hoja")ActiveSheet.Name = MiNombreEnd Sub
60. NOMBRE: INSERTAR NOMBRE A MAS DE UNA HOJA
Sub nombres_hojas()Dim MiNombre As StringDim hoja As WorksheetFor Each hoja In WorksheetsMiNombre = InputBox("Ingrese nombre de hoja")hoja.Name = MiNombreNext hojaEnd Sub
61. INSERTAR UNA COLUMNA
Sub insertando()'inserta una columna por delante de la selecciónSelection.EntireColumn.InsertEnd Sub
62. INSERTAR UN NOMBRE A UNA CELDA
Sub nominar()Worksheets("BALANCE").Range("e3").Name = "AC"Worksheets("BALANCE").Range("e19").Name = "PC"Worksheets("BALANCE").Range("e4").Name = "AC_Disponibilides"Worksheets("BALANCE").Range("e5").Name = "AC_Inversiones"Worksheets("BALANCE").Range("e7").Name = "AC_Cred_Ventas"Worksheets("METODO").Range("C14").Name = "r_1"End Sub
63. ORDENAR: ORDENAR DATOS DE UN RANGO POR UNA COLUMNA
Sub Ordenando()'ordena la hoja Orden por la col A en forma ascendente teniendo la primera fila como encabezadoSheets("Hoja1").Range("A1").SelectSelection.Sort key1:=Range("a1"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomEnd Sub
Página 20 de 27
64. COMPARAR: 2 COLUMNAS ORDENADAS ( DE DISTINTAS HOJAS)
Sub comparando() ‘Si las celdas coinciden se copia toda la fila en una tercer hoja.copiada = 2 Sheets("Hoja1").Range("A2").Select final = Range("A2").End(xlDown).Row For fila = 2 To final 'compara la col 1 de la hoja1 y la col 2 de la hoja 2If Cells(fila, 1) = ActiveSheet.Next.Cells(fila, 2).Value Then ActiveCell.EntireRow.Copy Destination:=Sheets("Hoja3").Cells(copiada, 1) ActiveSheet.Next.Cells(fila, 1).EntireRow.Copy Destination:=Sheets("Hoja3")._Cells(copiada + 1, 1)'quitar si no se copia lo de la hoja2 copiada = copiada + 2'más 1 si no se copia lo de la hoja 2 End If ActiveCell.Offset(1, 0).Select Next End Sub
65. COMPARAR: 2 COLUMNAS, COPIAR EN OTRO DESTINO SOLO REGISTROS ÚNICOS
Sub comparando()‘Cada columna debe estar ordenada de menor a mayorDim fila1 As Integer, fila2 As Integer, fila3 As Integer'compara a partir de la fila2, las columnas 1 y 2 de una misma hojafila1 = 2fila2 = 2fila3 = 2Range("A2").SelectWhile Cells(fila1, 1).Value <> ""'compara las col 1 y 2, copiando en la 4If Cells(fila1, 1).Value = Cells(fila2, 2).Value Thenfila1 = fila1 + 1fila2 = fila2 + 1Cells(fila1, 1).SelectElseIf Cells(fila1, 1).Value < Cells(fila2, 2).Value Or Cells(fila2, 2).Value = "" ThenCells(fila1, 1).Copy Destination:=Cells(fila3, 4)fila3 = fila3 + 1fila1 = fila1 + 1Cells(fila1, 1).SelectElseIf Cells(fila2, 2).Value <> "" ThenCells(fila2, 2).Copy Destination:=Cells(fila3, 4)fila3 = fila3 + 1
Página 21 de 27
fila2 = fila2 + 1End IfEnd IfEnd IfWendWhile Cells(fila2, 2).Value <> ""Cells(fila2, 2).Copy Destination:=Cells(fila3, 4)fila3 = fila3 + 1fila2 = fila2 + 1Cells(fila2, 2).SelectWendEnd Sub
66. PROTEGER Y DESPROTEGER: UNA HOJA Y COPIAR LOS DATOS EN OTRA
Sub copiandoYprotegiendo()'protege la hojaActiveSheet.Protect "contraseña"'copia el contenido del rango en la hoja siguiente, en rango D1Range("A2:A3").SelectSelection.Copy 'previamente se habrá seleccionado algoActiveSheet.Paste Destination:=ActiveSheet.Next.Cells(1, 4)Application.CutCopyMode = False'desprotege la hoja para poder ocultar una columnaActiveSheet.Unprotect "contraseña"'oculta la col 1 de la hoja activaActiveCell.EntireColumn.Hidden = TrueEnd Sub
67. IMPRIMIR: HOJA ACTIVA
Sub imprimiendo()'vista previa de la hoja activaActiveSheet.PrintPreview'imprime la hoja activaActiveSheet.PrintOut Copies:=1, Collate:=TrueEnd Sub
68. IMPRIMIR: SELECCIONAR ÁREA DE IMPRESIÓN
Sub AreaImpresion()Dim primera, ultima As VariantRange("A1").SelectIf ActiveCell.Value = "" ThenSelection.End(xlToRight).SelectEnd Ifprimera = ActiveCell.Address
Página 22 de 27
ActiveCell.SpecialCells(xlLastCell).Selectultima = ActiveCell.AddressActiveSheet.PageSetup.PrintArea = (primera & ":" & ultima)End Sub
69. IMPRIMIR: CONFIGURAR IMPRESIÓN
Sub Configurando()With ActiveSheet.PageSetup'para el encabezadoLeftHeader = "Nombre Empresa" 'ingresar un textoCenterHeader = "&T" 'Time u horaRightHeader = "&D" 'Date o fecha'para el pie de páginaLeftFooter = "&A" 'nombre de hojaCenterFooter = "&F" 'File o nombre de libroRightFooter = "&P" 'Page o número de páginaEnd WithEnd Sub
70. ABRIR: LIBRO
Sub AbriendoLibros()'oculta la ejecución de los siguientes pasos de la macroApplication.ScreenUpdating = False'abre un segundo librolibro (ajustar la ruta)Application.Workbooks.Open "C:\Mis documentos\Libro2.xls"'activa el segundo libroWorkbooks("Libro2.xls").Worksheets("Hoja2").Activate'abriendo un libro y deshabilitando la actualización de vínculos'Workbooks.Open Filename:="C:\Mis documentos\Vinculado.xls", UpdateLinks:=0'se vuelve al estado normal de ejecuciónApplication.ScreenUpdating = TrueEnd Sub
71. CERRAR: CERRAR LIBRO SIN GUARDAR
Sub CerrandoLibros_1()'cierra el libro sin guardar los cambiosWorkbooks("Libro2.xls").Close FalseEnd Sub
72. CERRAR: CERRAR LIBRO Y GUARDAR
Sub CerrandoLibros_2()'guarda y cierra el libro activoActiveWorkbook.Save
Página 23 de 27
ActiveWorkbook.CloseEnd Sub
73. GUARDAR: LIBRO Y DESACTIVA ALARMA DE AVISO
Sub GuardandoLibros()'oculta mensajes de alerta, ejecutando la opción predeterminadaApplication.DisplayAlerts = False 'guardando el segundo libroWorkbooks("Libro2.xls").SaveAs Filename:="C:\Mis documentos\Libro2.xls", FileFormat:=xlNormal, Password:="clave", ReadOnlyRecommended:=False'omitiendo algunas opcionesWorkbooks("Vinculado.xls").SaveAs Filename:="C:\Mis documentos\Vinculado.xls"'cerrando un libro guardadoWorkbooks("Vinculado.xls").Close'guardando el libro activo con nombre = valor de celdaActiveWorkbook.SaveAs Filename:=Range("A2").ValueEnd Sub
74. SELECCIONAR CELDAS EN RELACIÓN A OTRA
Sub selecciono08()Range("D3").SelectActiveCell.Offset(-2, 1).Select 'selecciona la celda que se encuentra 2 filas por encima'y 1 columna a la derecha de la celda activa (=D3)End Sub
75. ACTIVA LAS OPCIONES DE ALERTA
Sub avisos()'volver al estado normal la ejecución de los mensajes de alertaApplication.DisplayAlerts = TrueEnd Sub
76. INPUTBOX Y MSGBOX: INGRESAR DATOS Y TENER RESPUESTA
Sub Main()Dim strNombre As StringDim strApellido As StringDim strMsg As StringstrNombre = InputBox("Ingrese su nombre:", "Datos Personales")strApellido = InputBox("Ingrese su nombre:", "Datos Personales")strMsg = "Bienvenido " & strNombre & " " & strApellidoMsgBox strMsgEnd Sub
Página 24 de 27
77. MENSAJE: ESTRUCTURA SIMPLE
Sub mensaje()MsgBox "Texto del mensaje", vbOKOnly + vbInformation, "Titulo del Mensaje"End Sub
78. MENSAJE: CON AVISOS DE OPCIONES
Sub pregunta()intRespuesta = MsgBox("Desea terminar el proceso?", vbYesNo + vbQuestion, "MsgBox como función")If intRespuesta = vbYes ThenMsgBox ("guarde previamente la planilla") ' Terminar el procesoElseMsgBox ("guarde la planilla y luego salga del sistema") ' Continuar con el procesoEnd IfEnd Sub
79. MENSAJE: REDONDEO DE VALORES DENTRO DEL MENSAJE
Sub redondeado()Dim Fraccion As SingleFraccion = 3.8MsgBox "El número redondeado es: " & CInt(Fraccion), vbOKOnly, "Ejemplo"End Sub
80. BOTONES: BARRA DE HERRAMIENTAS
Sub Herramientas()Dim EnMenu As CommandBarDim miboton As CommandBarButtonDim micontrol As CommandBarControl'estas son para los botones de la barra de herramientas o StandardSet EnMenu = Application.CommandBars("Standard")For Each miboton In EnMenu.ControlsOn Error Resume Next'para conocer los números de cada botónMsgBox miboton.Caption & " - " & miboton.ID 'esto es para saber el número de control'If miboton.ID = 3 Then miboton.Enabled = False 'inhabilita el botón de Guardar'volverla a True antes de cerrar el libroNextSet EnMenu = Nothing'estas son para las opciones del menú EdiciónSet EnMenu = Application.CommandBars("Edit")For Each micontrol In EnMenu.ControlsOn Error Resume Next'para conocer los números de cada opción del menúMsgBox micontrol.Caption & " - " & micontrol.ID 'esto es para saber el número de control
Página 25 de 27
'If micontrol.ID = 19 Then micontrol.Enabled = false 'inhabilita la opción Copiar'volverla a True antes de cerrar el libroNextSet EnMenu = NothingEnd Sub
81. LLAMAR A UNA MACRO DENTRO DE UNA RUTINA
Call Carlos ´donde Carlos es el nombre de la macro
82. EJECUTAR MACRO SEGÚN EL VALOR DE UNA CELDA
Es bastante simple.
Desde Excel , tipea ALT+F11 para ir a Visual Basic (Herramientas\Macro\Editor de VisualBasic)
En el "explorador de Proyectos", ver el libro (Ej VBProject (Libro1)) que te interesa , haz "doble click" en "Hoja1".
El cursor se posicionará en la ventana de "Código", pulsalo y selecciona Worksheet.
Allí vuelca alli, el siguiente código :
'*********** Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$A$1" And Target.Value = 2 Then MsgBox "Es un 2 en A1" macro1 End If End Sub '***********
Vuelve a Excel y prueba diferentes valores en la celda A1 de la hoja 1 . Cuando introduzcas un 2 , se genera un mensaje. Y luego se ejecuta la "macro1"
83. POSICION DE LA COLUMNA DE LA CELDA ACTIVA
La siguiente macro te lo dice
Public Sub ColumnaActual()
Dim Col As Integer
Página 26 de 27
Col = ActiveCell.Column
If Col > 27 Then
MsgBox "Estas despues de la columna AA"
End If
Página 27 de 27