TRUCOS MACROS EXEL.docx

1

Auto_Open() y Auto_Close()

Existe una macro de autoarranque que se ejecuta cuando se abre el libro. Se llama auto_open().Y existe otra que se ejecuta justo antes de cerrar el libro que se llama auto_close(). Sub Auto_Open() Dim hora As Double Dim saludo As String hora = (Now - Int(Now)) * 24 Select Case hora Case 6 To 14 saludo = "Buenos días" Case 14 To 21 saludo = "Buenas tardes" Case Else saludo = "Buenas noches" End Select MsgBox saludo & " Amo"End Sub Equivalente a auto_open existe otra macro Workbook_Open, pero ésta ha de ser guardada no en un módulo normal, sino en ThisWorkbook. Pruebe lo siguiente: El siguiente procedimiento permite abrir automáticamente el libro Balance.xls al abrir el libro Informe.xls. El procedimiento ha de estar en ThisWorkbook del libro Informe.xls. Sub Workbook_Open() 'Apertura de libro Balance Workbooks.Open Filename:="C:/Temp/Balance.xls" 'Activación del libro Informe Windows("Informe.xls").ActivateEnd Sub

Blog

2

Saludo al arrancar

Crear un libro Personal.xls como se ha indicado anteriormente con la macro de autoarranque siguiente: Sub Auto_open() MsgBox ("Que tengas un buen día")End Sub Esta macro creada en el libro Personal.xls hace que al iniciar Excel nos salga un mensaje saludándonos.

3 Nombrar Hoja desde una celda

Esta macro permite asignar el nombre que pongamos en la celda A1 como nombre de la Hoja actual.

Sub NombreHoja() ActiveSheet.Name = Range("A1").ValueEnd Sub

4

Exportar un módulo1. Pase al editor de visual basic y active el módulo a exportar.2. Seleccione Archivo/ Exportar archivo. Aparece un cuadro de diálogo.3. En cuadro de edición Nombre de Archivo, teclee el nombre para el archivo donde se guardará elmódulo, por ejemplo "General.Bas", observe que .BAS es la extensión de estos archivos.4. Pulse sobre el botón Guardar.

5

Importar un módulo1. Active el editor Visual Basic.2. Seleccione Archivo/ Importar Archivo. Aparece un cuadro de diálogo.3. Seleccione en la lista Buscar en: la carpeta donde tiene ubicado el archivo a importar.4. Una vez localizada la carpeta, seleccione el archivo a importar (General.Bas en el ejemplo) y pulsesobre Abrir.

6

La cláusula PrivatePuede anteponer la cláusula private a todos los procedimientos y funciones que sean llamados sólo desdeel mismo módulo, es una forma de ahorrar memoria y hacer que el programa corra un poco más rápido. Sinecesita llamar un procedimiento o función desde otro módulo, nunca debe precederlo por la cláusulaprivate, recuerde que esta cláusula restringe el ámbito de utilización de un procedimiento a su propiomódulo.

7Inspección rápida de variables

Cuando ejecuta un programa paso a paso, si sitúa el puntero de ratón sobre una variable, se muestra el valor de la misma.

8

Modificar el valor de una variable en tiempo de ejecuciónA veces resulta interesante cambiar el valor de alguna variable cuando se está ejecutando el programa,para ver que ocurre si coge determinados valores, para terminar un bucle, etc.Para ello agregue a la ventana de inspección la variable que desee cambiar, cambie su valor sobre la propia ventana de inspección y continúe la ejecución del programa.

9Ventana Inmediato

Puedes inspeccionar variables desde esta ventana anteponiendo la palabra print antes de la variable, o el signo ?.

10 Debug.Print

Esta expresión permite efectuar la depuración del programa de forma más cómoda. Se introduce en un programa antecediendo a una variable que se desea comprobar en la ventana Inmediato. De esta forma conseguiremos ver todos los valores que toma la variable cada vez que el programa pasa por este comando. Se ven en la Ventana Inmediato [Ctr+G]..Ejemplo:

Sub Dos_a_la_diez() Dim i As Integer Dim t As Integer t = 1 For i = 1 To 10 t = t * 2 Debug.Print "Dos a la " & i & " = " & t Next MsgBox t

End Sub

Después de efectuada la depuración se quita la línea que contiene el comando Debug.Print.

11

Propiedad ListFillRange del ComboBox

Con esta propiedad deberemos definir los elementos que debe mostrar la lista, debe especificarse el rangoque contiene los elementos a mostrar, el rango debe ser una columna (o dos , o tres, etc.).

12

Propiedad LinKedCell del ComboBox

En esta propiedad debe especificar en que celda debe copiarse el elemento seleccionado de la lista.Cuidado con esta propiedad, tenga en cuenta que los elementos de la lista son tratados como datos detipo String aunque contenga números o fechas, por lo que en estos casos, a veces será necesario aplicarfunciones de conversión de datos antes que el dato se copie en la hoja. Por ejemplo, si alguna vezconstruye una lista con números verá que el dato seleccionado se alinea a la derecha, si son fechas, no semuestra con el formato correspondiente.

13

Propiedad ListIndex del ComboBox

Mediante esta propiedad podremos saber que elemento de la lista es el seleccionado por su número deorden. Es decir, si está seleccionado el primero, ListIndex valdrá 0, si está seleccionado el segundo valdrá1, etc. Si no hay ningún elemento seleccionado valdrá -1. Tenga en cuenta que esta propiedad sólo estádisponible en tiempo de ejecución, es decir la podremos leer mientras esté funcionando el programa, nose puede establecer en modo diseño, observe que no aparece en la ventana propiedades del cuadrocombinado.

14

Cambiar el nombre de un Móludo

Al insertar módulos en el Editor de Visual Basic, se utilizan los nombres: Módulo 1, Módulo 2, etc. Podemos cambiar el nombre el un módulo en sus propiedades (F4). La única propiedad de un Módulo es 'name'.

15 Nombre de usuario: UserName

Vamos a crear una función que proporcione el nombre de usuario. Primero utilizando la Grabadora de Macros. Haga lo siguiente. Active la Grabadora y luego seleccione Herramientas, Opciones, pestaña General. Realice un cambio en el nombre de usuario. Cerrar la ventana de Opciones pulsando en Aceptar. Y detener la Grabadora. La macro obtenida será la siguiente: Sub Macro1()'' Macro1 Macro' Macro grabada el 06/08/2004 por Adolfo

'

' With Application .UserName = "Adolfo Aparicio" .StandardFont = "Arial" .StandardFontSize = "10" .DefaultFilePath = "C:\Documents and Settings\Adolfo\Mis documentos" .EnableSound = False .RollZoom = False End WithEnd Sub Ahora vamos a crear la función =Usuario siguiendo el procedimiento inverso. Funcition Usuario() Usuario = Application.UserNameEnd Function Puede probarla. La encontrará en la categoría de Funciones Definidas por el usuario.

16

Extensiones de los nombres de macro

- Módulos de clase. Extensión .cls.- Formularios. Extensión .frm.- Módulos estandar. Extensión .bas.

17Exit Sub, Exit Function

Se usan eventualmente para salir de un procedimiento o función.

18

Macro que pregunta si se desea salir de la aplicación

Sub SalirAplic() If MsgBox("¿Quiere salir de la aplicación", vbQuestion + vbYesNo, "Ultima pregunta") = vbYes Then Application.Quit End IfEnd Sub

19 Llamada a un procedimiento

[Call] NombreProc [(lista de argumentos)]

La palabra clave Call es opcional para llamar a un procedimiento, salvo que existan argumentos, en cuyo caso es obligatoria. Los argumentos deben ir entre paréntesis y pueden ser valores o variables.

Sub Suma(a As Byte, b As Byte) MsgBox "La suma es " & a + bEnd Sub

Sub sumar() Call Suma(3, 4)

End Sub

Para llamar a un procedimiento de otro módulo

NombreDelMódulo.NombreDelProcedimiento

Ejemplo: ThisWorkbook.SalirAplic

Para llamar a un procedimiento de otro libro

Application.Run "NombreDelLibro!NombreDelMódulo.NombreDelProcedimiento"

Ejemplo: Application.Run "Informe.xls!ThisWorkbook.SalirAplic.xls"

Al ejecutar este comando, el libro Informe.xls debe estar abierto.

20

Proteger con contraseña las macrosAlt + F11Herramientas => Propiedades de VBAProject => Protección => Bloquear proyecto para visualización => Contraseña

21

Convertir una función en un Complemento

Podemos hacer que una función creada por el usuario este disponible en todos los Libros. Para ello, hemos de convertir el Libro que contiene la función en un Complemento.

Los Complementos disponibles se ven haciendo: Herramientas => Complementos.

Excel carga los complementos guardados en el directorio donde tengáis instalado MsOffice y luego en una carpeta que varía con la versión que tengas instalada. Para XP puede ser:

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\AddIns\

Para convertir el libro que contiene nuestra Función en un Complemento, bastara ir al Menú Archivo/Guardar como y elegir el formato Complemento de Microsoft Excel (*.xla). Si lo guardáis en el directorio que tiene tu versión asignado estará siempre disponible.Un aspecto a tener en cuenta es que los Complementos no son editables, es decir no podrásmodificar o añadir mas Funciones. Por eso es recomendable guardar primero el Libro en formato *.xls y luego como *.xla.

22

Para localizar la última celda de una lista

Sub Final() While ActiveCell.Value <> "" ActiveCell.Offset(1, 0).Select WendEnd Sub

23 Abrir un libro existente:Sub AbrirLibro() Workbooks.Open ("C:\Mis documentos\Ejemplo.xls")End Sub

Activar un libro ya abierto:Sub ACtivarLibro() Workbooks("Ejemplo.xls").ActivateEnd Sub

Crear un libro nuevo:

Sub NuevoLibro() Workbooks.AddEnd Sub

24

Borrar un registro de una tabla que contenga un dato concretoSub BorrarFilas() While ActiveCell.Value <> "" If ActiveCell.Value <> "Cadiz" Then ActiveCell.Offset(1, 0).Range("A1").Select Else Selection.EntireRow.Delete End If WendEnd Sub

25

Creación de una Fórmula personalizadaCálculo del término amortizativo de un préstamo tipo francés (de pagos constantes). Similar a la fórmula =PAGO pero en este caso usando tipo nominal y con fraccionamiento. Function Termino(Principal@, Años As Byte, tipo_nominal!, fraccionamiento As Byte) Dim im! 'Tipo efectivo del subperiodo im = tipo_nominal / fraccionamiento / 100 Termino = Principal * im / (1 - (1 + im) ^ (-Años * fraccionamiento))End FunctionPongamos un ejemplo para un principal de 100.000 €, 10 años, tipo nominal anual del 6%, con fraccionamiento mensual. En este caso, al ser el fraccionamiento mensual el término obtenido es la mensualidad.Sub ejemplo()End Sub

26

Macro que cierra ExcelSe puede asociar a un botón o a un icono.Sub Auto_Close() Application.QuitEnd Sub

27

Macro que elimina las barras de desplazamiento Sub Desbarra() With ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False End WithEnd Sub

28 Macro que protege el libro y la hoja

Sub Blindaje() ActiveWorkbook.Protect Password:="1234" Sheets("Hoja1").Protect Password:="1234"End Sub

29

Macro que borra los ceros de un rango

Sub BorrarCeros() For Each Celda In Range("C11:G24") If Celda.Value = 0 Then Celda.ClearContents NextEnd SubSi sólo se quieren ocultar los ceros se ha de poner formato de celda personalizado de #.###

30

Macro que reemplaza una palabra por otra

Sub RemplazarPalabra() Dim Palabra As String Dim Hoja As Long Palabra = Trim(InputBox("Introduzca la palabra a buscar: ")) For Hoja = 1 To Sheets.Count Sheets(Hoja).Activate Cells.Replace What:=Palabra, Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Next HojaEnd SubEn este caso reemplazamos por "", lo que supone borrar la palabra buscada.

31

Calculo del NIF

Esta función calcula el NIF (Número de Identificación Fiscal) utilizado en España.

Function nif(dni As Long) As String nif = Mid("TRWAGMYFPDXBNJZSQVHLCKE", (dni Mod 23) + 1, 1)End Function

32

Función que calcula la fila que ocupa cierto valor en una tabla

Está pensado para que la matriz se ponga comenzando en la celda A1, sin encabezado y sin que se repitan los elementos.

Function DimeFila(Rango As Range, Valor_a_buscar) As Integer For Each c In Rango If c.Value = Valor_a_buscar Then DimeFila = c.Row End If NextEnd Function

33 Función que muestra la fórmula de una celda

Devuelve la fórmula que contiene una celda en lenguaje local

Function DisplayCellFormula(InputCell As Range) As String DisplayCellFormula = InputCell.FormulaLocalEnd Function Si se quita la palabra 'Local' devuelve la fórmula en inglés.

34

Determinación de si un número es primo o no es primo

Sub primo() Dim primo As Boolean Dim n As Long Dim d As Long n = Val(InputBox("Introduce un número natural", "Entrada de Datos")) d = 2 primo = True Do While primo And d < n If n Mod d = 0 Then primo = False End If d = d + 1 Loop If primo Then MsgBox (n & " es primo") Else MsgBox (n & " no es primo") End IfEnd Sub

35

Factorial

Function facto(n) Dim i As Integer facto = 1 i = 0 Do i = i + 1 facto = facto * i Loop While i <> nEnd Function

36 Inicializar una matriz

Se inicializa con ERASE'En este caso se ponen todos los valores a EmptySub test() Dim c(1 To 20, 1 To 10) As Variant For i = 1 To 20 For j = 1 To 10 c(i, j) = RND Next j Next i

MsgBox c(1,1) Erase c MsgBox c(1,1)End Sub

'En este caso se pone la variable c a su estado inicial'Borrandose los valores, las dimensiones y recuperandose'la memoria usada.Sub test1() Dim c As Variant c = ActiveSheet.Range("A1:J20").Value MsgBox c(1, 1) Erase c 'Esto dara error ya que no hay ninguna matriz MsgBox c(1, 1)End Sub

37

Evitar los movimientos de pantalla mientras se ejecuta una macro

Para evitar que durante la ejecución de una macro se vean todos los movimientos y cambios del cursor, para evitar las "chirivitas" que se ven al ejecutar una macro:

Al principio de la macro escribe

Application.ScreenUpdating = False

y al final

Application.ScreenUpdating = True

38

Evitar que al ejecutar una macro nos haga preguntas

Al principio de la macro escribe

Application.DisplayAlerts= False

y al final

Application.DisplayAlerts=True39 While...Wend

Esta estructura permite ejecutar las instrucciones contenidas mientras la condición sea verdadera.

La siguiente macro posiciona el cursor en la última celda llena de la columna A de la Hoja1.Sub final1() i = 1 While Worksheets("Hoja1").Cells(i, 1).Value <> "" Worksheets("Hoja1").Cells(i, 1).Select i = i + 1 Wend

End Sub

Otra variante:Sub final2() While ActiveCell.Value <> "" ActiveCell.Offset(1, 0).Select Wend ActiveCell.Offset(-1, 0).SelectEnd SubLa segunda macro requiere posicionar inicialmente el cursor en la primera celda llena.

40

Manejo de Rangos

Para señalar una tabla se hace con CurrentRegion. Un ejemplo:

Dim R As RangeDim filas As LongSet R = Range("A1").CurrentRegionfilas = R.Rows.Count

41

Ocultar Hojas

Podemos ocultar o mostrar Hojas de un libro. En el ejemplo siguiente, la Hoja2 esta inicialmente oculta, y para poder trabajar con ella la macro la mustra, y al finalizar la vuelve a ocultar.

Sub oculta() Sheets("Hoja2").Visible = True ActiveWorkbook.Sheets("Hoja2").Activate '... ... Sheets("Hoja2").Visible = FalseEnd Sub

42

Determinar la fila hasta la que llega un rango

Podemos determinar facilmente la fila hasta la que llega un rango. Supongamos el rango B5:B100, del que sabemos en que fila acaba (la 100), pero no sabemos en que fila comienza. Ejecutando la macro la variable n tomará el valor 5, que es la fila de comienzo del rango.

Dim n As LongRange("B100").End(xlUp).Selectn = Selection.Row

43Retardar el tiempo de ejecución de una Macro

Application.Wait Now + TimeValue("00:00:3")44 Utilización de un Array

Sub ColumnaArray()Dim i As ByteDim usouso = Array("AS", "AT", "BC", "BM", "BV", "BB", "BO", "DI", "EN", "FB")Worksheets("Hoja1").Activate

Range("B4").SelectFor i = 1 To 10 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = uso(i - 1) ActiveCell.Offset(1, 0).ActivateNext iEnd Sub

45

Dejar una fórmula en una celda

Lo mejor de las Macros es que pueden interactuar con la hoja de cálculo. Todas las funciones de Excel estan disponibles para ser utilizadas en las macros. Por ejemplo, podemos hacer la media del valor de dos celdas y dejar el cálculo en otra celda.Range("I3") =Application.WorksheetFunction.Average(Range("H3"), Range("J3"))Pero si lo que queremos es dejar no el valor del cálculo sino la propia fórmula se debería hacer de esta forma:Range("I3").Formula ="=average(H3,J3)"Si queremos introducir la fórmula de la TIR para toda una columna, podemos escribir el siguiente código:Range("I7").Formula ="=IRR(C:C)"

Si lo que deseamos es introducir en una celda, no la fórmula, sino el cálculo de la TIR aplicada a toda una columna, el código será el siguiente:

Range("I7") = Application.WorksheetFunction.IRR(Columns("C:C").EntireColumn)Y si de paso queremos poner formato lo hacemos así:Range("H3:J3").NumberFormat ="##.#0%"

miércoles, febrero 13

David pregunta "Quiero preguntarle algo al usuario, y ejecutar un macro si selecciona SI o no hacer nada si selecciona NO"

Use un MsgBox

If MsgBox("La pregunta", vbYesNo,"Pregunta") = vbYes Then'Haga algo aquíElse'Seleccionó NO, no haga nadaEnd If

posted by Juan Pablo at 8:36:10 AM

lunes, febrero 11

Raúl pregunta "Necesito identificar mediante código si una celda contiene una ecuación; algo así como las funciones IsEmpty o IsNumeric pero con ecuaciones.El problema es que las ecuaciones me las identifica como numérico, y no sé discriminarlas de las que sólo contienen números.He probado hasta con

If Left(Celda, 1) <> "=" Then

pero claro, una ecuación no es un string y no funciona. ¿Alguien me puede indicar alguna función concreta o solución alternativa?Gracias"

Si un rango tiene o no una fórmula lo indica la propiedad HasFormula así:

If Cells(1,1).HasFormula thenMsgBox "Si tiene"ElseMsgBox "No tiene"End If


domingo, febrero 10

Jorge pregunta "Deseo sacar el minimo de un conjunto de valores donde hay 0 pero no quieroque me los tome en cuenta, ya que para mi el minimo es 10 hay algunafuncion de minimo que pueda realizar esto, gracias"

Esto se puede hacer con una fórmula CSE.

{=MIN(SI(A1:A10,A1:A10))}

Esta calcula el mínimo de TODOS los números diferentes de 0. Para que seanlos MAYORES que 0 toca cambiarla a

{MIN(SI(A1:A10>0,A1:A10))}


sábado, febrero 9

Jose pregunta "Quisiera saber como le doy un formato numérico deseparador de miles y dos decimales desde una macro deVB.¿Que función he de utilizar? Gracias"

La propiedad NumberFormat es la que tiene el texto (Una cadena) del formato de la(s) celda(s). La siguiente linea cambia el formato de la celda A1

Range("A1").NumberFormat = "#,##0.00"


viernes, febrero 8

Meredith pregunta "Estoy haciendo un macro que ponga una marca en la celda activa. Estoy usando el siguiente código:

ActiveCell.FormulaR1C1 = "x"

Quiero saber cómo cambiar la fuente de Arial a Wingdings en la macro."

Los Rangos (Y otros objetos) incluyen un objeto para la Fuente, que controla, por ejemplo, si está en Negrilla, Cursiva, el tamaño, y el tipo de letra utilizado. Para cambiar el tipo de letra, hay que usar la propiedad name así:

ActiveCell.Font.Name = "Wingdings"


jueves, febrero 7

Rolando pregunta "Buenos días, tengo una hoja de calculo con funciones en español. ¿como puedo hacer que un excel en inglés reconozca las funciones en español? Gracias de antemano por su atención."

Si las fórmulas usadas son "internas", como BUSCARV, SUMA, PROMEDIO, etc., Excel las traduce sin problema, es decir, se pueden pasar de Excel en español a Excel en Inglés. Si hay fórmulas usadas de algún complemento, éstas están creadas en el idioma original, y no son "multilingues", por lo que sí habría necesidad de traducir una por una.

posted by Juan Pablo at 12:09:33 PM

miércoles, febrero 6

Jen pregunta "Hay alguna forma de maximizar Excel cuando abro un libro ?"

Para hacer esto, hay que hacer click derecho en el icono del libro (Al lado del menu Archivo), y seleccionar Ver Código

Debería aparecer lo siguiente, y el cursor en la mitad de las dos lineas.

Private Sub Workbook_Open()

End Sub

Ahí, hay que poner este código

Application.WindowState =xlMaximized

y Listo !


martes, febrero 5

Mike pregunta "Hay alguna forma de remover ciertos caracteres de una cadena de texto?. Por ejemplo, tengo el código BN19 4GX y quiero quitar todos los caracteres numéricos, dejando como resultado BN GX"

Esto se puede hacer fácilmente en VBA.

Primero, hay que entrar al editor de VB (Alt + F11), ir a Insertar y seleccionar Módulo. Ahí, se puede copiar este código:

Function QuitarNumeros(Rng As String) As String Dim Tmp As String Dim i As Integer

Tmp = Rng For i = 0 To 9 Tmp = Application.Substitute(Tmp, i, "") Next i QuitarNumeros = Tmp End Function

Ahora, otra vez en Excel, se puede usar esta fórmula, asumiendo que el texto inicial estaba en A1.

=QuitarNumeros(A1)

y se tendrá como resultado 'BN GX'


lunes, febrero 4

Matthew pregunta "Hay alguna tecla rápida para cambiar de una hoja a otra ?"

Hay varias, dependiendo de lo que necesito. Las dos que más utilizo son:

Control Tab o Control Shift Tab: Cambia entre ventanas (O libros)

Control Pg Down o Control Pg Up: Cambia entre hojas de un mismo libro.


viernes, febrero 1

Murray pregunta "Quiero tener un total acumulado de los números que tengo en una columna. Por ejemplo, si en la columna A tengo 12 15 12 16 22 14 y 11, en la columa B quisiera ver 12+15, 12+15+12, 12+15+12+16 y así. Traté de usar la función de AutoSuma, pero tengo que cambiar los rangos. Cómo se puede hacer esto ?"

Para hacer esto hay que usar las referencias ABSOLUTAS ("$A$2"), RELATIVAS (A2) y MIXTAS ($A2, A$2)

Si los datos comienzan en la fila 2, en la celda B2 hay que poner la siguiente fórmula y arrastrar hacia abajo:

=SUMA($A$2:$A2)


jueves, enero 31

Kristie pregunta "Quiero convertir una columna que tiene el apellido y el nombre así (Appellido, Nombre) en dos columnas, una que tenga el apellido y la otra con el nombre. Hay alguna forma de hacer esto?"

Primero, seleccione el rango de los nombres, después ir a Datos, Texto en Columnas. En el primer paso seleccionar Delimitado, en el segundo paso hay que asegurarse que la 'coma' este seleccionada, y en el tercero hay que seleccionar una nueva celda de destino, para no borrar los datos existentes.

Finalmente, presionar Terminar y listo !


miércoles, enero 30

Daniel pregunta "Estoy tratando de encontrar cómo adicionar un 50% a cada uno de los números en un rango seleccionado. Necesito incrementar cada número en 50%, o multiplicarlo por 1.5"

Esto ayuda a mostrar una opción de Pegado Especial que no es muy utilizada. En una celda desocupada poner 1.5 o 150%. Copiarla (Control C), ahora hay que seleccionar el rango de números, ir a Edición, Pegado Especial, seleccionarValores or Fórmulas (Eso depende de qué se necesite hacer...), y seleccionar en el segundo cuadro Multiplicar. Listo, hacer click en Aceptar y LISTO ! así de fácil !!


martes, enero 29

El Inputbox que viene con VBA es bastante útil, pero también muy limitado, porque no permite seleccionar desde ahí una celda de una hoja de cálculo. Para esto toca usar el Inputbox de Excel, así:

Application.Inputbox(Prompt:="Seleccione una celda",Title:="Seleccionar",Type:=8)

Esto devuelve Falso si se cancela, o una referencia a la celda/rango seleccionado. De esta forma, se podría utilizar una variable para el rango seleccionado, así

Set MiRango = Application.Inputbox(.....)

Hay más tipos, que permiten hacer más cosas, las que se pueden encontrar en la ayuda.


lunes, enero 28

Mike pregunta "Es posible cambiar el color de fondo de una celda con un macro ?"

La propiedad Interior de las celdas maneja, entre otras cosas, el color de fondo de las celdas. Para modificarlo toca usar o ColorIndex (Para usar uno de los 56 colores prestablecidos en Excel) or Color, así

ActiveCell.Interior.ColorIndex = 36 'Amarillo claro

or

Range("A1:A6").Interior.Color = RGB(200,160,35) 'Naranja claro


sábado, enero 26

A veces es necesario vincular dos listas, para que el usuario pueda seleccionar los datos dependiendo de sus selecciones anteriores. Un ejemplo de esto es el siguiente.

John pregunta "Tengo una lista de países (USA, Australia, Inglaterra), y un listado de ciudades de estos países. Cómo puedo hacer para que cuando el usuario seleccione USA, en la otra lista aparezcan únicamente ciudades de USA (Nueva York, Los Angeles,

Washington), o seleccione Australia y aparezcan Camberra, Perth y Sidney ?"

Lo primero que hay que hacer es darle nombre a los rangos. Primero, seleccione el rango de países, y nómbrelo PAISES. Ahora, seleccione todas las ciudades (Sin el título) de USA y nómbrelo USA. Repita este proceso para cada país.

Suponiendo que la lista de países se encuentra en A1, hay que ir a Datos, Validación, seleccionar Lista, y en "Origen" poner:

=PAISES

(Asegúrese de que "Celda con lista desplegable" esté activada)

Ahora, si la segunda lista aparece en B1, selecciónela, vaya a Datos, Validación, seleccione Lista, y ahora en "Origen" ponga

=INDIRECTO(A1)

Ahora, cada vez que seleccione un país en A1, las ciudades correspondientes a este país aparecerán en B1.


viernes, enero 25

Esta es una sugerencia para los usuarios de MrExcel.com en español (Aunque les sirve a todos los usuarios internacionales).

El Message Board y otras páginas de MrExcel.com funcionan principalmente en Inglés, por lo que la respuesta a las preguntas, funciones, sugerencias serán recibidas en este idioma. Para "traducir" facilmente una fórmula de Excel en Inglés a Excel en Español (Por ejemplo, =FLOOR(A1,5)), se puede hacer lo siguiente.

Seleccionar la celda en la que se quiere insertar la fórmula. Presionar Alt + F11, para ingresar al editor de Visual Basic. Si no hay un panel abajo visible que se llama inmediato, presionar Ctrl + G. En este cuadro se escribe lo siguiente:

ActiveCell.Formula = "<< la fórmula aquí >>"y presionar [Enter]

para el ejemplo anterior, se haría así

ActiveCell.Formula = "=FLOOR(A1,5)"

Al volver a Excel se puede ver que la celda seleccionada tiene la fórmula "traducida", así :

=MULTIPLO.INFERIOR(A1,5)


jueves, enero 24

Jenny pregunta "Hay un método fácil para sumar cada dos celdas ?, tengo una columna con 250 celdas, y tengo que seleccionarlas manualmente usando Ctrl. Hay algo que pueda hacer?"

Cada dos celdas es equivalente a sumar todas las celdas pares o todas las impares. Para saber si una celda es de fila impar o par hay que usar la función RESIDUO así

=RESIDUO(A1,2)

devolverá 0 si A1 es impar y 1 si es par. Traduciendo esto a la fórmula quedaría así:

=SUMA((RESIDUO(FILA(A1:A250)-FILA(A1),2)=0)*(A1:A250))

Esta es una fórmula matricial, para entrarla hay que presionar Control Shift Enter al tiempo. O usar la siguiente fórmula No-matricial:

=SUMAPRODUCTO((RESIDUO(FILA(A1:A250)-FILA(A1),2)=0)*(A1:A250))



PPD pregunta "Como se tendría que dar formato a una celda para presentar el contenido (que está en segundos) y mostrarlo como "horas:minutos:segundos"?

Por ejemplo si el contenido es 16548 (segundos) que pusiera: 4:35:48 (horas:minutos:segundos)"

Para hacer esto hay que recordar que Excel guarda la información de fechas (y horas) como fracciones de día. Por lo tanto, para tomar un

valor en segundos hay que convertirlo a días así:

Dividir el valor en segundos (16548) en (60 s / min * 60 min/hr * 24 hr/dia) para que de un total de

=16548/(24*60*60)o=A1/(24*60*60)

Y este resultado se puede presentar como hh:mm:ss


martes, enero 22

Aquí hay un truco para los programadores de VBA que utilicen Office 2000 o Office XP.

Jose Luis pregunta

"Cuando llamo al Help sin emplear el Asistente (F1, asistente inhabilitado) la pantalla de Excel se me hace más estrecha para que en pantalla esté la hoja de cálculo actual y la ventana de ayuda.

Hay alguna forma de evitar este comportamiento.Lo que quiero es que Excel se quede en su tamaño y la ayuda se me muestre maximizada. Aunque cambie el tamaño de Excel tan pronto pulso sobre la ventana de ayuda se me vuelve al tamaño de mitad de pantalla, y cuando cierro la ayuda se vuelve al tamaño maximizado o anterior tamaño que lo tuviese."

Para modificar este comportamiento hay que entrar a modificar el Registro. Es importante hacer un BackUp del Registro antes de realizar cambios sobre él. Si no sabe hacer esto, por favor no intente realizar los cambios propuestos. MrExcel.com no asume ninguna responsabilidad por las consecuencias de los cambios realizados.

Entrar al Regedit, buscar la llave HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Common\HelpViewer

Si ahí no aparece una variable que se llama 'IsFloating' hay que crearla, como DWORD, y asignarle el valor de 1.

Para hacer el cambio en Office XP, hay que buscar la llave HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\HelpViewer


lunes, enero 21

Jorge en Buenos Aires pregunta "Cómo puedo llenar una columna con el último día del mes, comenzando a partir del último día de un mes en particular?. He tratado usando rellenar series, pero si comienzo por ejemplo el 30 de junio de 2002, la siguiente celda será el 30 de julio de 2002, y no el 31 de julio, que es lo que necesito."

Para poder hacer esto se requiere una fórmula. Si la fecha inicial (Junio 30, 2002) está en A2, se puede poner esta fórmula en A3 y arrastrar hacia abajo.

=FECHA(AÑO(A2),MES(A2)+2,0)


sábado, enero 19

John Munoz pregunta "Estoy tratando de calcular la mediana de los salarios usando un código de trabajo específico usando una gran cantidad de datos...algo parecido a SUMAR.SI pero que calcule la Mediana. Hay alguna forma de hacerlo?"

Para resolver este problema se debe usar una de las caracterísitcas más podersas de Excel: Las fórmulas matriciales (O fórmulas CSE, como nos referimos a ellas aquí en MrExcel.com, para aprender más acerca de estas fórmulas, visiteeste tip).

Asumiendo que los códigos de trabajo están en A2:A100 y los Salarios en B2:B100 la siguiente fórmula calcularía el resultado esperado:

=MEDIANA(SI(A2:A100="Código",B2:B100))

Es importante recordar que esta es una fórmula CSE, para ingresarla se debe presionar al tiempo Control Shift Enter, en vez de sólo Enter como con las demás fórmulas.


viernes, enero 18

Kate pregunta "Hay alguna forma de redondear los números a la próxima decena ?"

Esta función redondea el número que haya en A2 a la próxima decena.

=MULTIPLO.SUPERIOR(A2,10)


jueves, enero 17

Una pregunta común es "Cómo puedo hacer para que BUSCARV devuelva 0 o "" en vez de #N/A! ?"

Asumiendo que la fórmula actual es

=BUSCARV(A1,$B$2:$D$100,3,Falso)o más corto=BUSCARV(A1,$B$2:$D$100,3,0)

el método usual es:

=IF(ESNOD(BUSCARV(A1,$B$2:$D$100,3,0)),””, BUSCARV(A1,$B$2:$D$100,3,0))

pero esto obliga a Excel a calcular DOBLEMENTE el BUSCARV, que es una fórmula "costosa" en términos de eficiencia.

Una solución mejorada es:

=SI(CONTAR.SI($B$2:$B$100,A1), BUSCARV(A1,$B$2:$D$100,3,0),””)

De esta forma el BUSCARV sólo se calcula si el valor de A1 existe en B2:B100, y por lo tanto BUSCARV no devolverá #N/A!



Buenos días, a partir de hoy encontrarán aquí trucos, fórmulas, funciones, o soluciones a preguntas específicas usando Excel.

Javi pregunta "Hola. Tengo una macro que abre un archivo, copia datos y le cierra. ¿Que le pongo para que no se vean las cosas que hace la macro?, es decir como se desactiva el eco?? Saludos y gracias."

Al principio del macro (O antes de realizar las operaciones a "ocultar") se pone

Application.ScreenUpdating = False

y al final, para restaurar el valor:

Application.ScreenUpdating = True


TRUCOS MACROS EXEL.docx

Documents

Transcript of TRUCOS MACROS EXEL.docx