jueves, 21 de septiembre de 2017

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

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

'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"

'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:

VBA: Los Separadores del Sistema en nuestras macros



Y en un otro paso:

VBA: Los Separadores del Sistema en nuestras macros

martes, 19 de septiembre de 2017

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.[...]

Fórmula Matricial para replicar el operador O.



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:

Fórmula Matricial para replicar el operador O.



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...

jueves, 14 de septiembre de 2017

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:

VBA. GetDetailsOf o como conseguir propiedades de un fichero



Obteniendo una lista de propiedades asociadas al fichero seleccionado.