VBA: Abrir un pdf en una página específica

Puede parecer sencilla la tarea de hoy: abrir un pdf en una hoja específica desde Excel...
Y verás muchos métodos buscando por Internet... pero el truco de hoy es el único que asegura la meta de poder abrir un fichero pdf desde Excel en la página deseada.

Otras formas sí consiguen abrir el pdf sin problemas, pero nunca en la página requerida:
- Usando la herramienta de hipervínculo
- la función HIPERVINCULO
- incluso con programación, creando el objeto IE...

todos ellos llevan a un punto no deseado... la primera página del documento.

Para solucionar la cuestión emplearemos la función SHELL de VBA que ejecutará la aplicación IE (el navegador Internet Explorer), y en éste es donde se abrirá el documento pdf en la página solicitada.

Y ahora la solución... en un módulo estándar incluimos el siguiente y sencillo procedimiento:

Sub AbrirPDFpaginaEspecifica()
'ruta del pdf: X:\carpeta\fichero.pdf
strVinculo = ActiveCell.Value
Dim pagina As Long
pagina = InputBox("Indica la página del pdf donde situarte", "Arir pdf")
'empleando la función Shell para ejecutar IE
'y abrir en él el archivo indicado a modo de ruta en la celda activa
Shell ("C:\Program Files\Internet Explorer\iexplore.exe " & strVinculo & "#page=" & CLng(pagina))
End Sub

Al ejecutar nuestra macro, y completar el dato solicitado en el InputBox, el resultado es el esperado:

Seguir leyendo...

Localizar Último Valor Positivo en un Rango

En el post de hoy lograremos descubrir cuál es el último valor positivo en un rango de celdas en Excel.

La cuestión planteada por el usuario:

[...]necesito la fórmula para guardar-actualizar en otra celda cualquiera el último importe mayor de cero de la columna S, teniendo en cuenta que esta columna sigue aumentado, por eso digo lo de actualizar, a medida que añado mas importes en la columna S que esa otra celda se actualice con el último dato mayor de cero.[...]

En algunas entradas anteriores del blog hemos hablado sobre el tema.
Ver ejemplo1 y ejemplo2

Lo especial en esta ocasión es, que además, se exige un criterio... que sea positivo.
Por aportar algo más a esta entrada mostraremos además el primer valor positivo en el rango elegido.

Veamos nuestro ejemplo.

Estudiemos nuestras fórmulas matriciales!!!.
Para obtener el primer valor positivo existente:
=INDICE(A:A;COINCIDIR(VERDADERO;A:A>0;0))

donde básicamente la función COINCIDIR trabaja matricialmente sobre un conjunto de VERDADEROS y FALSOS determinados por A:A>0, encontrando el primer VERDADERO.
Luego INDICE se encarga de devolvernos el valor correspondiente.

Para conseguir el último valor positivo usamos la siguiente matricial:
=BUSCAR(9,99999999999999E+307; SI($A:$A> 0; $A:$A))

donde la función BUSCAR encuentra entre los valores positivos retornados por la matricial
SI($A:$A> 0; $A:$A)
el localizada en última posición.

Con lo que conseguimos nuestro objetivo.

Seguir leyendo...

VBA: Los Separadores del Sistema en nuestras macros

Vamos a repasar unas propiedades de la aplicación, de Excel, que nos pueden ser útiles en diferentes ocasiones.
Por ejemplo, cuando trabajamos con nuestras macros en entornos internacionales y nuestros compañeros de trabajo emplean una configuración decimal-miles de sistema distinto al nuestro.

Veremos en concreto, y entre otras, las propiedades:
Application.UseSystemSeparators
Application.DecimalSeparator
Application.ThousandsSeparator

La propiedad Application.UseSystemSeparators activa o desactiva el uso de los separadores del sistema.
Mientras que los otros dos:
Application.DecimalSeparator
Application.ThousandsSeparator
permiten personalizar los caracteres a emplear tanto para el separador de miles como para el decimal...

Otra propiedad interesante es la que nos permite cambiar el estio de referencia de celdas entre A1 y F1C1, la propiedad en cuestión es:
Application.ReferenceStyle

En un módulo estándar de nuestro explorador de proyecto VBA insertamos el siguiente código:

Sub SeparadoresDelSistema()
'vemos en un mesnaje el estilo de referencia en este momento
Dim strEstilo As String
If Application.ReferenceStyle = xlA1 Then
    MsgBox "El estilo de referencia actual es A1"
Else
    MsgBox "El estilo de referencia actual es F1C1"
End If
'cambio de estilo de referencia a F1C1
Application.ReferenceStyle = xlR1C1

'componemos texto informativo con los separadores actuales de nuestro equipo
Dim strDecimal As String, strMiles As String
strDecimal = Application.International(xlDecimalSeparator)
strMiles = Application.International(xlThousandsSeparator)

MsgBox "Antes del cambio " & vbCrLf & _
    "El separador decimal actual es: " & strDecimal & vbCrLf & _
    "El separdor de miles actual es: " & strMiles

'Cambiamos los separadores del sistema
With Application
    'deshabilitamos los separadores
    .UseSystemSeparators = False
    'marcamos cuálkes son los separadores deseados
    .DecimalSeparator = "."
    .ThousandsSeparator = ","
End With

'componemos texto informativo con los separadores actuales
'tras el cambio en nuestro equipo
strDecimal = Application.International(xlDecimalSeparator)
strMiles = Application.International(xlThousandsSeparator)

MsgBox "Después del cambio " & vbCrLf & _
    "El separador decimal actual es: " & strDecimal & vbCrLf & _
    "El separdor de miles actual es: " & strMiles & vbCrLf & _
    "El estilo de referencia es: F1C1"

'para activar de nuevo los separadores del Sistema
Application.UseSystemSeparators = True

'retornamos al estilo de referencia a A1
Application.ReferenceStyle = xlA1
End Sub

En mi caso parto de una configuración con:
Separador de miles un punto .
Separador decimal una coma ,

Vemos en dos imágenes el cambio producido al ejecutar nuestra macro:

Y en un otro paso:

Seguir leyendo...

Fórmula Matricial para replicar el operador O.

Un usuario solicitaba ayuda para replicar unos cálculos donde se necesitaba emplear el operador lógico O, i.e., adicionar importes según condiciones:

[...]Tengo que hallar la edad promedio de personas que nacieron en tumbes o puno. La fórmula que estoy utilizando es : =+PROMEDIO.SI.CONJUNTO(G21:G91,E21:E91,"TUMBES") pero esta solo me obtiene la edad promedio de un lugar, lo que necesito es saber que formula tendría que usar para que me salga la edad promedio de las personas que nacieron en tumbes o en puno.[...]

La fórmula matricial concreta que busca el usuario sería (en la celda H12):
=PROMEDIO(SI((F17:F87="TUMBES")+(F17:F87="PUNO");H17:H87;""))
(no olvides ejecutarla presionando Ctrl+Mayusc+Enter!!!).

Para mostrar la forma de trabajar con el operador O simplificaremos el ejercicio:

En este ejemplo disponemos de un campo dividido por categoría (a, b,c y d) y unos importes sobre los que operar.
La primera fórmula matricial, en la celda F2 es:
=PROMEDIO(SI((B2:B10="a")+(B2:B10="b");C2:C10;""))
que nos devolverá el promedio de los importes que correspondan exclusivamente a las categoría a y b (celda amarillas).

Otro cálculo matricial frecuente lo vemos en la celda F3:
=SUMA(SI((B2:B10="a")+(B2:B10="b");1;0))
que nos devolverá un conteo de registros, en concreto de aquellos registros que sean a o b.

Por finalizar, un tercer cálculo matricial en al celda F4:
=SUMA(SI((B2:B10="a")+(B2:B10="b");C2:C10;""))
con el que obtenemos el sumatoria de los importes correspondientes a las categorías a o b.

En respuesta a la cuestión planteada por el usuario, no es posible usar directamente PROMEDIO.SI.CONJUNTO, ya que estas funciones emplean internamente el operador lógico Y...

Seguir leyendo...

VBA. GetDetailsOf o como conseguir propiedades de un fichero

Me preguntaba una lectora por la forma de conseguir la propiedad de 'Dimensiones' de un fichero o imagen..

El asunto a priori no es fácil, pero construyendo una macro podremos recuperar de alguna forma, junto a otras propiedades informativas, dicho dato.

En un módulo estándar incluimos el siguiente procedimiento:

Sub DetallesFichero()
Dim objShell  As Object, objFolder As Object, objFolderItem As Object

Dim strRutaCompleta As String       'para la ruta completa, incluido el nombre del fichero
Dim NombreFichero As String         'solo el nombre del fichero
Dim RutaCarpeta As Variant

Dim strCab As String    'la cabecera del MsgBox
Dim strTxt As String    'para la composicón del texto a devolver en el MsgBox


'Definimos la variable del fichero elegido
'mediante GetOpenFilename
'debe ser una imagen para poder recuperar las Dimenensiones
strRutaCompleta = Application.GetOpenFilename
'si no seleccionamos nada salimos del procedimiento
If strRutaCompleta = "False" Then Exit Sub

On Error GoTo ControlError
'asignamos la Ruta del fichero elegido
RutaCarpeta = Left(strRutaCompleta, Len(strRutaCompleta) - Len(Dir(strRutaCompleta, vbHidden + vbSystem)) - 1)
'y el nombre del fichero
NombreFichero = StrReverse(Left(StrReverse(strRutaCompleta), InStr(StrReverse(strRutaCompleta), "\") - 1))
On Error GoTo 0

'abrimos objeto Shell y definimos la Carpeta
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(RutaCarpeta)

'controlamos la ruta
If (Not objFolder Is Nothing) Then
    'creamos y definimos un objeto como parte de la Carpeta
    '.ParseName convierte en objeto un texto
    Set objFolderItem = objFolder.ParseName(NombreFichero)
    'En caso de que exista...
    If (Not objFolderItem Is Nothing) Then
        '...obtenemos los detalles/propiedades del fichero elegido
        strCab = "Detalles de:= " & NombreFichero
        strTxt = "Carpeta:=" & RutaCarpeta & vbCrLf
        
        'con .GetDetailsOf obtenemos los detalles del fichero
        MsgBox strTxt & objFolder.GetDetailsOf(objFolderItem, -1), vbExclamation, strCab
        'el segundo parametro de .GetDetailsOf devuelve:
        '-1 detalles informativos del elemento.
        '0 nombre del elemento.
        '1 tamaño.
        '2 tipo.
        '3 Fecha y hora última modificación.
        '4 los atributos del elemento.
        
    End If
    
End If

'liberamos memoria
Set objFolderItem = Nothing
Set objFolder = Nothing
Set objShell = Nothing

Exit Sub

ControlError:
'lanzamos mensaje de error en su caso
MsgBox Err.Number & ":" & Err.Description, vbInformation
End Sub

Si probamos la macro y elegimos una imagen cualquiera obtendremos:

Obteniendo una lista de propiedades asociadas al fichero seleccionado.

Seguir leyendo...

VBA: Ordenar una Array Multidimensional

Veremos en el post de hoy cómo conseguir ordenar, empleando una array en VBA para Excel, un rango de celdas de varias columnas.
Como punto especial veremos que sirve para cualquier dimensión de rango (número de filas y columnas) y que es configurable cuál es el campo o columna prioritaria a la hora de la ordenación.
Veamso el punto de partida y el resultado tras la ejecución de la macro:

La función definida personalizada que veremos a continuación: 'OrdenarMatrices' emplea el método de burbuja de ordenación, que ya vimos en esta entrada hace algún tiempo; si bien en esta ocasión en modo función.

Y ahora lo importante.. en un módulo estándar incluimos los siguiente procedimientos:

Function OrdenarMatrices(TempArray() As Variant, NumCol As Long, TotalCol As Long)
Dim situacion As Boolean
Dim elto As Long, col As Long
'definimos la dimensión de una matriz temporal
Dim Temp()
x = Val(TotalCol)
'la redimensionamos
ReDim Temp(1 To x)

'añadimos un bucle para pasar por todos los elementos de la matriz
Do
    situacion = True
    'recorremos cada elemento de la matriz de trabajo cargada
    For elto = LBound(TempArray) To UBound(TempArray) - 1
        'con el < ordenamos de menor a mayor o ascendente (con > en sentido descendente)
        If TempArray(elto, NumCol) < TempArray(elto + 1, NumCol) Then
            situacion = False
            'recorremos cada columna de cada elemento
            For col = 1 To TotalCol
                'hacemos un trasvase de datos de la matriz temporal a la cargada
                Temp(col) = TempArray(elto, col)
                TempArray(elto, col) = TempArray(elto + 1, col)
                TempArray(elto + 1, col) = Temp(col)
            Next
        End If
    Next
'salimos del bucle...
Loop While Not situacion

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Ordenamos()
Dim MiMatriz() As Variant
'cargamos la matriz con datos
MiMatriz = Range("A1:D10")
'ordenamos de menor a mayor según la tercera columna el rango anterior
Call OrdenarMatrices(MiMatriz, 3, UBound(MiMatriz, 2))

'devolvemos la matriz oredenada a la hoja de cálculo
Range("F1:I10") = MiMatriz
    
End Sub

Tras la ejecución de 'Ordenamos' obtenemos el resultado esperado, tal como se veía en la imagen primera....

Seguir leyendo...

Amortizaciones contables de Inmovilizado en Excel

Si estás leyendo este post es porque estás familiarizado con los conceptos contables y fiscales de las amortizaciones de inmovilizado.
La idea de una amortización o depreciación consiste básicamente en controlar cómo pierde valor un activo de nuestra empresa a lo largo del tiempo.
Solo necesitamos conocer algunos conceptos muy simples, que a grandes rasgos serían:
Costo: valor de compra
Valor residual: valor del bien al final de su vida
Fecha compra/adquisición: pues eso mismo
Fecha fin primer periodo: la fecha en que consideramos termina el primer periodo de vida (normalmente final de año).
Tasa o porcentaje de amortización: tasa o tanto por ciento en que se realizará la depreciación del activo (relacionado inversamente con la vida útil del bien: Vida = 1 / tasa)
Normalmente tabuladas en base a normas contables y/o fiscales.
Vida útil: en base a la tasa (tasa = 1 / vida).

Lo habitual es aplicar un método de amortización lineal o directo basado en una sencilla fórmula:
=(Costo - Valor residual) / años vida útil
aunque existen otros muchos métodos, como porcentaje constante, números dígitos, etc...

Hablando en términos de Excel, se nos ofrecen unas cuantas funciones que facilitan el cálculo de todos estos métodos.
Las funciones siguientes responden a un metodología de cálculo, tal como se indica a continuación:
AMORTIZ.LIN - Depreciación lineal prorrateada - (sistema contable francés)
AMORTIZ.PROGRE - Depreciación lineal prorrateada - (sistema contable francés)
SLN -Método directo
SYD - Método de anualidades o dígitos
DB - Método de saldo fijo
DDB - Método doble disminución
DVS-Método doble disminución

Veamos la siguiente imagen donde se despliegan todas las funciones existentes:

haz clic en la imagen

Repasamos rápidamente la sintáxis de cada función empleada.

En la celda C10 hemos empleado la función de AMORTIZACIÓN LINEAL
=AMORTIZ.LIN(costo; fecha_compra; primer_período; costo_residual; período; tasa; [base])

en concreto para nuestro ejemplo:
=AMORTIZ.LIN($C$1;$C$2;$C$3;$C$4;$B10;$C$5;1)

con los resultados que se observan en la imagen anterior.
Se comprueba, como curiosidad del método, que se procede al cálculo sobre el Costo, sin considerar el valor residual, i.e., en el ejemplo para cada periodo se realiza el cálculo:
=Costo/vida útil
=10.000/8 años = 1.250,00
pasando los años, hasta completar el último valor por diferencias y llegar al Valor residual.

La siguiente función que vemos es la AMORTIZACION PROGRESIVA
=AMORTIZ.PROGRE(costo; fecha_compra; primer_período; costo_residual; período; tasa; [base])

en nuestra tabla en la celda D10 tenemos:
=AMORTIZ.PROGRE($C$1;$C$2;$C$3;$C$4;$B10;$C$5;4)

Fijémonos en la particularidad de la suma tras todos los periodos de la amortización acumulada, que excede de los 8.500 esperados.
Esta función devuelve la amortización hasta el último período de vida del bien o hasta que el valor acumulado de dicha amortización sea mayor que el valor inicial de compra del bien menos el valor residual.
Ha tener presente para el cálculo interno:
Vida útil del bien(=1/tasa) //// Coeficiente de amortización
Entre 3 y 4 años //// 1,5
Entre 5 y 6 años //// 2
Más de 6 años ///// 2,5

Esta es sin duda la función más extraña y compleja de entender...

Otra función más, la más simple, es la función SLN que responde al método lineal o directo:
=SLN(costo, valor_residual, vida)

En la celda E10 tenemos:
=SI($B10<(100/$C$5/100);SLN($C$1;$C$4;1/$C$5);0)
he incorporado un condicional para controlar el exceso de la cuota de amortización sobre la vida útil determinada.
Mientra el periodo concreto de estudio (celda B10) sea menor a la vida útil, aplicaremos el cálculo.

La función SYD trabaja empleando el método de anualidades (también conocido como de dígitos)
=SYD(costo, valor_residual, vida, período)

En F10 insertamos:
=SI.ERROR(SYD($C$1;$C$4;1/$C$5;$B10);0)
Se comprueba que existe una caída en las cuotas de amortización según avanzamos en la vida del bien.

Una nueva función sería DB, que utiliza el método de saldo fijo. Su sintáxis:
=DB(costo; valor_residual; vida; período; [mes])

y en nuestro ejemplo, en la celda G10:
=SI.ERROR(DB($C$1;$C$4;1/$C$5;$B10);0)
de forma similar, no igual, a la anterior función SYD, se verifica que las cuotas disminuyen de acuerdo al paso del tiempo.

Las dos últimas funciones DDB y DVS son equivalentes según definamos ciertos arguementos.
Ambas trabajan bajo un entorno de doble disminución... si bien, este factor doble es configurable.
Sus sintáxis:
=DDB(costo, valor_residual, vida, período, [factor])
=DVS(costo, valor_residual, vida, período_inicial, período_final, [factor], [sin_cambios])

El argumento [sin_cambios] puede hacerlas iguales o con un comportamiento distinto, similar a SLN.

En nuestro ejercicio hemos escrito en H10:
=SI.ERROR(DDB($C$1;$C$4;1/$C$5;$B10);0)

y en I10:
=SI.ERROR(DVS($C$1;$C$4;1/$C$5;$B10-1;$B10);0)

En la siguiente entrada del blog replicaremos los cálculos que realizan estas funciones de manera interna para comprender mejor qué estamos obteniendo.
Una futura entrada muy interesante...

Seguir leyendo...

Encontrar palabras de una lista en una celda

En el post de hoy trataremos un caso frecuente: cómo localizar elementos de una lista dentro de una celda.

Todo nace a partir de la duda planteada por un usuario:

[...]Tengo que trabajar sobre una base de datos extensa, y en una columna de DESCRIPCIÓN tengo información que no siempre viene escrita de la misma manera. Lo que necesito es identificar una palabra dentro de un texto, y ser capaz de extraer esa palabra, para luego poder hacer categorías mayores. Lo que necesito es que si aparece en cualquier parte del texto la palabra Azul, la extraiga a la columna GRUPO. [...]

Inicialmente pensé que no se podría realizar con funciones estándar, y que habría que pasar por una función personalizada... pero después de algunas pruebas conseguí el objetivo mediante el empleo de funciones de Excel: INDICE, ESNUMERO, SUMAPRODUCTO, HALLAR o ENCONTRAR y FILA.

Veamos el planteamiento de partida:

La idea a lograr es que en cada una de las celdas de la columna de DESCRIPCIÓN localizar una de las palabras de la 'lista de colores'.

Comenzaremos, para facilitar nuestro trabajo y darle algo de dinamismo, asignándole un nombre definido al campo de la tabla:
Lista =Tabla2[lista colores]

La función que incorporamos a nuestro rango, en la celda B2 es:
=INDICE(Lista;SUMAPRODUCTO(ESNUMERO(HALLAR(Lista;A2))*(FILA(Lista)-1)))

Analizamos la función por partes.
En lo más 'profundo' encontramos
HALLAR(Lista;A2)
(si necesitamos precisión en cuanto a mayúsculas y minúsculas emplearemos la función ENCONTRAR).
Esta función bajo un entorno matricial devuelve un conjunto de números y errores para cada una de las palabras/colores buscados. Por ejemplo en la celda B2 de nuestro ejemplo obtendríamos:
{8;#¡VALOR!;#¡VALOR!}
Al anidarlo dentro de la función ESNUMERO convertimos esa matriz de datos en una matriz lógica de VERDADEROS y FALSOS:
{VERDADERO;FALSO;FALSO}

Por otro lado tenemos una segunda matriz de constantes que corresponderán a las filas en las que se encuentran los colores buscados... llegamos a estos números con lo siguiente:
(FILA(Lista)-1)
que devuelve:
{1;2;3}
Notemos el ajuste de -1 para salvar la posición del encabezado de la Tabla.

La función SUMAPRODUCTO hace el resto, multiplicando ambas matrices elemento a elemento y sumando el resultado de cada multiplicación individual.. retornando un solo valor:
{VERDADERO;FALSO;FALSO} * {1;2;3}
=1+0+0 =1

Ese número retornado, equivale al número de fila del dato buscado dentro del campo de nuestra tabla de colores, se emplea de manera directa con la función de búsqueda INDICE.
Con lo que finalmente conseguimos nuestro objetivo, recuperamos el color que existe en la celda de la 'descripción':

Obviamente esto solo sirve para localizar una sola palabra...

Seguir leyendo...