Empieza el 2015 con Excel y Macros.

Aumenta tu productividad... última oportunidad!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Enero de 2015.

Un momento para repasar los hitos conseguidos en este año 2014 que acaba.
Sin duda lo que más me ha honrado es obtener el título de MVP Excel otorgado por Microsoft...
también, a título personal, obtuve el certificado de Experto en Excel (igualmente firmado por Microsoft).

A nivel del blog, he alcanzado las 632 entradas publicadas!!!. Una media de 10 post por mes, un gran esfuerzo por mi parte.
Más de 500 seguidores en G+, 2000 'readers', 270 seguidores Google Friend, + 550 en LinkedIn, +150 facebook, +200 twitter...
Otro hecho importante es haber superado los 4.150.000 de páginas vistas en el total del blog!!
Por todo ello, no puedo más que daros las gracias.

A nivel profesional, mantengo los servicios de formación elearning o presencial para empresas y los servicios de consultoría a empresas y particulares, con gran aceptación por vuestra parte.
Todos estos número me animan a continuar ofreciendo mis servicios, en particular, los cursos de Excel y Macros abiertos para este mes de Enero son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Enero de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Búsqueda doble sobre cualquier parte de una tabla de Excel.

En una entrada anterior hablamos sobre cómo conseguir localizar un valor buscado en cualquier parte de una tabla (ver). Lo que solucionamos con una matricial.
En el día de hoy veremos el código de una sencilla función personalizada (UDF) en VBA que nos realice la misma operación y resultado.
Mantenemos las condiciones iniciales en las que no debe haber duplicados, basada en el método .Find

Insertamos en un módulo estándar de nuestro proyecto de VBA el siguiente procedimiento Function:

Function FxBusquedaDoble(ValorBuscado As Variant, rng As Range, Campo As String)
Dim fila As Long, col As Long

Application.Volatile

'buscamos en el rango dado el ValorBuscado
With rng
    Set c = .Find(ValorBuscado, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            'una vez localizado nos quedamos con su número de fila
            fila = c.Row
        Loop While Not c Is Nothing And c.Address <> firstAddress
    Else
        'en caso de no encontrar nada como Valores
        'buscamos como fórmulas (valores numéricos, etc...)
        Set c = .Find(ValorBuscado, LookIn:=xlFormulas)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                'una vez localizado nos quedamos con su número de fila
                fila = c.Row
            Loop While Not c Is Nothing And c.Address <> firstAddress
        Else
            'si no encontramos como valores o como fórmulas
            'devolvemos 0 y salimos de la función
            FxBusquedaDoble = 0
            Exit Function
        End If
    End If
End With

'localizamos sobre el encabezado del rango de datos seleccionado
'el número de columna que corresponde al Campo a retornar el valor
Set rng1 = rng.Offset(-1, 0).Resize(1, rng.Columns.Count)
'con la función MATCH/COINCIDIR tenemos el número de columna (de izqd a dcha)
'contando desde la columna A
col = Application.WorksheetFunction.Match(Campo, rng1, 1)

'devolvemos valor a la función
FxBusquedaDoble = Cells(fila, col).Value

End Function

El resultado es el esperado, equivalente a nuestra matricial:

haz clic en la imagen

Nuestra función UDF en la celda J7:
=FxBusquedaDoble(I6;A7:F10;H6)
donde indicamos que el 'ValorBuscado' está en I6
el rango donde buscar (sin cabecera) es A7:F10
y el campo del que queremos retornar el valor está definido en H6.

En este ejemplo es importante que el rango donde buscar comience en la columna A, y que no se seleccione el encabezado en el mismo.

Seguir leyendo...

Formato condicional múltiple.

Esto del formato condicional es un tema recurrente (y a su vez socorrido), obviamente por la gran variedad de aplicaciones que podemos darle.
En la entrada de hoy veremos cómo podemos aplicar a una tabla u origen de datos un formato condicional, en concreto el mismo formato a diferentes elementos a la vez, en definitiva un formato condicional múltiple.

Partimos de estos datos A1:B13, sabiendo queremos aplicar el mismo formato a aquellos elementos (comerciales) coincidentes con los que aparecen en las celdas E2:F2.

Así entonces comenzamos seleccionando el rango A2:B13, asegurándonos tenemos como celda activa A2, y accedemos a la herramienta Formato condicional tipo fórmula:

Introduciendo como fórmula:
=O($A2=$E$2:$F$2)
OJO!! para que resulte el formato múltiple debemos incorporar la función lógica O.. en caso contrario, sólo marcaría el primero de los elementos...
El resultado, entonces, queda como esperábamos:

La ventaja de este truco es que no hace falta generar diferentes reglas de formato... basta una única.
La explicación es que empleando la función O la fórmula trabaja de forma matricial, comparando el valor de A2 con cada elemento del rango indicado E2:F2

Seguir leyendo...

La función financiera Valor Final - VF y VF.PLAN.

Sin duda un tema bastante específico el del tema de hoy... pero siempre viene bien conocer herramientas y funciones realmente útiles en la valoración de inversiones (por ejemplo).
En el día de hoy veremos cómo Excel calcula fácilmente el valor de unos flujos de tesorería en el momento final de la inversión...

Plantearemos dos hipótesis de trabajo:
1-Una primera para el caso de tener unos flujos constantes,
2-Una segunda cuando los flujos sean variables

La idea fundamental de este ejercicio es trasladar al momento final de la inversión los diferentes flujos de tesorería, sean cuales sean.
Para verificar el resultado emplearemos las reglas financieras de actualización:
Monto x (1 + tasa)^periodos

Veamos en la imagen siguiente los cálculos empleados, y el detalle de las funciones VF, VF.PLAN, VA y VNA:

haz clic en la imagen

Comencemos con el caso en que los flujos son constantes, donde tenemos tres periodos con un flujo de 1.000 eur por periodo; entenderemos además, que el desembolso se realiza al inicio del periodo (importante ya que se define en el argumento Tipo con valor 1).
Sobre los flujos de las celdas C4:C6 y el tipo de interés/tasa en C2 obtenemos el valor final de éstos como:
=VF(C2;3;-1000;;1)
1- en el cual indicamos que la tasa está en C2 (en el ejemplo 10%)
2- que tenemos tres periodos
3- que los tres pagos anteriores son constantes de 1.000
4- y que el tipo es 1, esto es, pagos al inicio del periodo.
El resultado para este ejercicio es que el valor final al cabo de los tres periodos es de 3.641,00.

Como vemos en F4:F7, tras calcular por la fórmula financiera la actualización de cada 1.000 eur al 10% los periodos correspondientes, obviamente, llegamos al mismo resultado.

Vamos con el segundo caso. Ahora vamos a 'jugar' un poco con las leyes financieras...
En primer lugar descontaremos al momento cero los diferentes flujos, lo haremos con la función VNA, y sobre esa cantidad aplicaremos la función VF.PLAN para llevarla al momento final... en definitiva homogeneizo los diferentes valores/flujos, y ya con un único valor, actualizamos al término de la inversión.

En este caso empleo VF.PLAN ya que nos conviene al haber descontado todo al momento cero; además nos permitiría jugar en el caso que hubiera diferentes tasas asociadas a cada periodo.

Observamos como en C16 he aplicado sobre los flujos de C12:C14 la tas de C10
=VNA($C$10;C12:C14)
obteniendo el valor descontado a cero de los tres importes (se verifica en C20:C23).
Sobre esa cantidad es donde aplicamos la función VF.PLAN en la celda C15:
=VF.PLAN(C16;{0,1;0,1;0,1})
donde indicamos que vamos a actualizar la cantidad equivalente de los tres importes en el momento cero, durante tres periodos con una tasa en cada periodo igual al 0,1 (10%).
El resultado es de 1.965 (comprobable en F12:F15, donde hemos calculado lo mismo con la fórmula matemática).
Fijémonos como en este segundo caso entendemos que los pagos de los periodos se realizan al final de éstos!!

El equivalente al cálculo realizado con VF.PLAN, con VF sería lo que vemos en C17:
=VF($C$10;3;;-C16;1)
donde
1- con una tasa del 10% en cada periodo
2- en tres periodos de duración
3- aplicado al valor en el momento inicial/cero (el calculado previamente con VNA)
4- entendiéndolo como al inicio de ese momento cero (tipo 1).

Nótese el matiz entre VF y VF.PLAN en el ejercicio planteado.. y en el argumento tipo 1 de VF (inicio del periodo), corregido automáticamente al emplear VNA actualizado con VF.PLAN...
y es que entendemos que la inversión VNA comienza un período antes de la fecha del flujo de caja de valor1 y termina con el último flujo de caja de la lista (valor3)... es importante recordad que el cálculo VNA se basa en flujos de caja futuros, y que si el primer flujo de caja se produce al principio del primer período, el primer valor se debe agregar al resultado VNA, que no se incluye en los argumentos valores.

Seguir leyendo...

Personalizar la cinta de opciones (Ribbon) en Excel-sin macros.

Aprenderemos hoy lo sencillo que es personalizar nuestra Cinta de opciones (o Ribbon en inglés) de nuestras versiones de Excel 2010 y superiores, en nuestro equipo de trabajo.
Para ello usaremos simplemente la funcionalidad Personalizar la Cinta de opciones..., a la que accederemos de forma muy fácil haciendo clic derecho sobre la misma cinta:

Esto nos abrirá la venta de Opciones de Excel, desde la cual, presionando los botones habilitados:
- Nueva Pestaña
- Nuevo Grupo
podremos personalizar nuestra Cinta o Ribbon:

Supongamos tenemos unas cuantas macros personales, o simplemente queremos agrupar en un lugar los botones más frecuentemente empleados.
Para el ejemplo he creado nueve sencillas macros que lanzan un cuadro de mensaje:

Sub MacroNum01()
MsgBox "Esta es la macro 1"
End Sub
Sub MacroNum02()
MsgBox "Esta es la macro 2"
End Sub
Sub MacroNum03()
MsgBox "Esta es la macro 3"
End Sub
Sub MacroNum04()
MsgBox "Esta es la macro 4"
End Sub
Sub MacroNum05()
MsgBox "Esta es la macro 5"
End Sub
Sub MacroNum06()
MsgBox "Esta es la macro 6"
End Sub
Sub MacroNum07()
MsgBox "Esta es la macro 7"
End Sub
Sub MacroNum08()
MsgBox "Esta es la macro 8"
End Sub
Sub MacroNum09()
MsgBox "Esta es la macro 9"
End Sub

Insisto que simplemente podríamos aglutinar en nuestra Pestaña personalizada un conjunto de botones estándar recurrentes en nuestras rutinas diarias.

El objetivo es disponer de lo siguiente, de manera fácilmente accesible:

En este ejemplo he añadido/agregado a cada botón una de las macros existentes...

Pero veamos cómo se generan las Pestaña y los grupos dentro de ellas.
En primer lugar desde las Opciones de Excel, nos situamos en la posición que deseemos insertar la nueva pestaña, y presionamos Nueva pestaña, automáticamente se creará la Nueva Pestaña y dentro de ella (en un segundo nivel) un Nuevo Grupo:

Dentro de la Nueva pestaña presionaremos el botón Nuevo Grupo tantas veces como grupos necesitemos, en nuestro ejemplo hasta tres grupos..

Seguir leyendo...

VBA: Listar los iconos que corresponden a la propiedad FaceId.

Al respecto de la entrada anterior, veremos hoy cómo visualizar los diferentes Id de los iconos que podemos emplear en Excel...
En la imagen siguiente muestro sólo los 1000 primeros:

haz clic en la imagen

Insertamos y ejecutamos el siguiente procedimiento:

Sub MuestraLosFaceIDs()
Dim NewToolbar As CommandBar
Dim TopPos As Integer, LeftPos As Integer
Dim i As Integer, NumImgs As Integer

'Definimos las constantes que representa los Id de cada FaceId
'en este caso sólo del 1 al 100
Const IdInicio As Integer = 1
Const IdFinal As Integer = 100
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Eliminamos Nuestra Barra personalizada.. si existiera
On Error Resume Next
Application.CommandBars("VistaFaceIds").Delete
On Error GoTo 0

'Limpiamos nuestra hoja de cálculo de imágenes
ActiveSheet.Pictures.Delete

Application.ScreenUpdating = False
'Incorporamos una Barra vacía
Set NewToolbar = Application.CommandBars.Add(Name:="VistaFaceIds")

'Posiciones de Inicio donde pegar los iconos..
TopPos = 5
LeftPos = 5
NumImgs = 0
    
'Recorremos los 100 iconos primeros...
For i = IdInicio To IdFinal
    On Error Resume Next
    NewToolbar.Controls(1).Delete
    With NewToolbar.Controls.Add(Type:=msoControlButton)
        .FaceId = i
        .CopyFace
    End With
    On Error GoTo 0
    
    NumImgs = NumImgs + 1
    ActiveSheet.Paste
    With ActiveSheet.Shapes(NumImgs)
        .Top = TopPos
        .Left = LeftPos
        'nombramos a la imagen con su número!!!
        .Name = "FaceID " & i
        .PictureFormat.TransparentBackground = True
        .PictureFormat.TransparencyColor = RGB(224, 223, 227)
    End With
    
' Actualizamos la posición de cada 'icono'
' en filas de 50 iconos...
   LeftPos = LeftPos + 16
    If NumImgs Mod 50 = 0 Then
        TopPos = TopPos + 16
        LeftPos = 5
    End If
Next i
Application.ScreenUpdating = True

ActiveWindow.RangeSelection.Select
'terminamos eliminando nuestra barra...
Application.CommandBars("VistaFaceIds").Delete
End Sub

Con este índice de iconos creado basta buscar el que más nos guste y seleccionarlo para ver cuál es su Id, por ejemplo el icono de Excel corresponde al Id número 263:

Seguir leyendo...

VBA: Personalizar el menú contextual de la hoja de Excel.

Veremos cómo de sencillo es personalizar nuestra Menú contextual sobre la hoja de cálculo con un sencillo procedimiento.

El sentido o utilidad de esto podría ser facilitar la ejecución de nuestras macros más recurrentes...

Supongamos tenemos en nuestro libro de trabajo dos macros llamadas: 'NuestraMacro01' y 'NuestraMacro02' (da igual qué hagan...).
Son macros importantes para nosotros y las empleamos con asiduidad.. por lo que necesitamos tenerlas a la vista fácilmente.
Así por tanto insertaremos en un módulo general el siguiente procedimiento (y ejecutaremos):

Sub AñadirMenuClicDerechoHoja()
Dim MenuContextual As CommandBar, MySubMenu As CommandBarControl
 
On Error Resume Next
'comenzamos borrando el grupo que tuviéramos...
With Application.CommandBars("Cell")
    Call .Controls("Nuestro Grupo &Excelforo").Delete
End With
On Error GoTo 0

Set MenuContextual = Application.CommandBars("Cell")
'Situamos nuestro grupo en la parte inferior (no usamos el posicionador before:=x)
Set MySubMenu = MenuContextual.Controls.Add(Type:=msoControlPopup)

With MySubMenu
    'Le damos nombre al grupo
    .Caption = "Nuestro Grupo &Excelforo"
    'añadimos un separador al grupo
    .BeginGroup = True
    'Nombre de la etiqueta para referirnos a ella con código si fuera necesario
    .Tag = "ExcelforoGroup"

    'Elemento 1
    'Añadimos  un botón
    With .Controls.Add(Type:=msoControlButton)
        'que ejecuta la macro que le indiquemos (debe existir!!)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "NuestraMacro01"
        'añadimos el icono número 59 (un 'smile')
        .FaceId = 59
        'mostramos el texto
        .Caption = "Eje&cuta la macro 01"
    End With

    'Elemento 2
    'Añadimos  un botón
    With .Controls.Add(Type:=msoControlButton)
    'que ejecuta la macro que le indiquemos (debe existir!!)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "NuestraMacro02"
        'añadimos el icono número 64 (un 'teclado')
        .FaceId = 64
        'mostramos el texto
        .Caption = "E&jecuta la macro 02"
        End With

'etcétera, etcétera...

End With

'emplearíamos esto para resetear el Menú contextual!!!
'Application.CommandBars("Cell").Reset
End Sub

El resultado es el de la imagen del inicio.. tenemos a nuestra disposición haciendo un sencillo clic derecho con el ratón nuestras dos macros.

Notemos que en las propiedades .Caption he incorporado el símbolo & delante de la letra que deseo 'acelerar' (o que sirva de acceso rápido presionándola).

Seguir leyendo...

VBA: OnAction o como asociar un procedimiento

Explicaré algo sobre la propiedad OnAction asociada al objeto Shapes.

...Tengo varias autoformas (casi 100) con un texto escrito en su interior (numeradas del 1 al 100) y me gustaría que al hace click en la autoforma correspondiente su texto apareciera en una casilla concreta de la hoja de excel en el texto es crito ene esta autoforma. Ejemplo, la autoforma tiene el texto 023 al hace click en ella que en la casilla B19 se muestre el texto 023, pero que al hacer click en la autoforma 044 en la b19 muestre 044...

Lo primero que debemos saber es que .OnAction devuelve o establece el nombre de una macro que se ejecuta al hacer clic en el objeto especificado (justo lo que necesitaba el lector).

Generaremos dos sencillas macros en un módulo estándar de nuestro proyecto VBA.

Sub AsginarAccion()
'recorremos todos las autoformas
For i = 1 To Hoja1.Shapes.Count
    'y vamos añadiendo la acción de ejecutar la macro texto
    'al clicar sobre ellos
    Hoja1.Shapes(i).OnAction = "'texto " & i & "'"
Next i
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub texto(num As Long)
'selecciona la autoforma número 'num'
ActiveSheet.Shapes(num).Select
'llevamos a la celda A1 el texto de la autoforma
Range("A1").Value = Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text
End Sub

El primero sirve para asociar a todos nuestros objetos (Autoformas) la acción o macro solicitada...lo que ha quedado definido con la segunda macro llamada 'texto'.

El efecto final se puede ver en la imagen:

Seguir leyendo...