Páginas

lunes, 4 de mayo de 2020

VBA: Funcion para formatear un JSON String para la linea de comandos.

Function escapeQuotes(quotedString As String) As String
    'agrega un escape a las comillas para que sea aceptado por la linea de comandos
    'convierte {"key":"value"} en "{\"key\":\"value\"}"
   
    escQuotes = Replace(quotedString, Chr(34), "\" & Chr(34))
    escapeQuotes = Chr(34) & escQuotes & Chr(34)
   
End Function

jueves, 30 de abril de 2020

VBA: Lista de Funciones Predeterminadas

https://www.excelfunctions.net/vba-functions.html


Built-In VBA Functions


VBA provides a large number of built-in functions that can be used in your code. The most popular built-in VBA functions are listed below. Select a function name to go to a full description of the function with examples of use.

VBA Message Functions
InputBoxDisplays a dialog box prompting the user for input.
MsgBoxDisplays a modal message box.
VBA Text Functions
FormatApplies a format to an expression and returns the result as a string.
InStrReturns the position of a substring within a string.
InStrRevReturns the position of a substring within a string, searching from right to left.
LeftReturns a substring from the start of a supplied string.
LenReturns the length of a supplied string.
LCaseConverts a supplied string to lower case text.
LTrimRemoves leading spaces from a supplied string.
MidReturns a substring from the middle of a supplied string.
ReplaceReplaces a substring within a supplied text string.
RightReturns a substring from the end of a supplied string.
RTrimRemoves trailing spaces from a supplied string.
SpaceCreates a string consisting of a specified number of spaces.
StrCompCompares two strings and returns an integer representing the result of the comparison.
StrConvConverts a string into a specified format.
StringCreates a string consisting of a number of repeated characters.
StrReverseReverses a supplied string.
TrimRemoves leading and trailing spaces from a supplied string.
UCaseConverts a supplied string to upper case text.
VBA Information Functions
IsArrayTests if a supplied variable is an array.
IsDateTests if a supplied expression is a date.
IsEmptyTests if a supplied variant is Empty.
IsErrorTests if a supplied expression represents an error.
IsMissingTests if an optional argument to a procedure is missing.
IsNullTests if a supplied expression is Null.
IsNumericTests if a supplied expression is numeric.
IsObjectTests if a supplied variable represents an object variable.
VBA Error Handling Functions
CVErrProduces an Error data type for a supplied error code.
ErrorReturns the error message corresponding to a supplied error code.
VBA Program Flow Functions
ChooseSelects a value from a list of arguments.
IIfEvaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False.
SwitchEvaluates a list of Boolean expressions and returns a value associated with the first true expression.
VBA Conversion Functions
AscReturns an integer representing the code for a supplied character.
CBoolConverts an expression to a Boolean data type.
CByteConverts an expression to a Byte data type.
CCurConverts an expression to a Currency data type.
CDateConverts an expression to a Date data type.
CDblConverts an expression to a Double data type.
CDecConverts an expression to a Decimal data type.
ChrReturns the character corresponding to a supplied character code.
CIntConverts an expression to an Integer data type.
CLngConverts an expression to a Long data type.
CSngConverts an expression to a Single data type.
CStrConverts an expression to a String data type.
CVarConverts an expression to a Variant data type.
FormatCurrencyApplies a currency format to an expression and returns the result as a string.
FormatDateTimeApplies a date/time format to an expression and returns the result as a string.
FormatNumberApplies a number format to an expression and returns the result as a string.
FormatPercentApplies a percentage format to an expression and returns the result as a string.
HexConverts a numeric value to hexadecimal notation and returns the result as a string.
OctConverts a numeric value to octal notation and returns the result as a string.
StrConverts a numeric value to a string.
ValConverts a string to a numeric value.
VBA Date & Time Functions
DateReturns the current date.
DateAddAdds a time interval to a date and/or time.
DateDiffReturns the number of intervals between two dates and/or times.
DatePartReturns a part (day, month, year, etc.) of a supplied date/time.
DateSerialReturns a Date from a supplied year, month and day number.
DateValueReturns a Date from a String representation of a date/time.
DayReturns the day number (from 1 to 31) of a supplied date.
HourReturns the hour component of a supplied time.
MinuteReturns the minute component of a supplied time.
MonthReturns the month number (from 1 to 12) of a supplied date.
MonthNameReturns the month name for a supplied month number (from 1 to 12).
NowReturns the current date and time.
SecondReturns the second component of a supplied time.
TimeReturns the current time.
TimerReturns the number of seconds that have elapsed since midnight.
TimeSerialReturns a Time from a supplied hour, minute and second.
TimeValueReturns a Time from a String representation of a date/time.
WeekdayReturns an integer (from 1 to 7), representing the weekday of a supplied date.
WeekdayNameReturns the weekday name for a supplied integer (from 1 to 7).
YearReturns the year of a supplied date.
VBA Math & Trig Functions
AbsReturns the absolute value of a number.
AtnCalculates the arctangent of a supplied number.
CosCalculates the cosine of a supplied angle.
ExpCalculates the value of ex for a supplied value of x.
FixTruncates a number to an integer (rounding negative numbers towards zero).
IntReturns the integer portion of a number (rounding negative numbers away from zero).
LogCalculates the natural logarithm of a supplied number.
RndGenerates a random number between 0 and 1.
RoundRounds a number to a specified number of decimal places.
SgnReturns an integer representing the arithmetic sign of a number.
SinCalculates the sine of a supplied angle.
TanCalculates the tangent of a supplied angle.
SqrReturns the square root of a number.
VBA Financial Functions
DDBCalculates the depreciation of an asset during a specified period, using the Double Declining Balance Method.
FVCalculates the future value of a loan or investment.
IPmtCalculates the interest part of a payment, during a specific period, for a loan or investment.
IRRCalculates the internal rate of return for a series of periodic cash flows.
MIRRCalculates the modified internal rate of return for a series of periodic cash flows.
NPerCalculates the number of periods for a loan or investment.
NPVCalculates the net present value of an investment.
PmtCalculates the constant periodic payments for a loan or investment.
PPmtCalculates the principal part of a payment, during a specific period, for a loan or investment.
PVCalculates the present value of a loan or investment.
RateCalculates the interest rate per period for a loan or investment.
SLNCalculates the straight line depreciation of an asset for a single period.
SYDCalculates the sum-of-years' digits depreciation for a specified period in the lifetime of an asset.
VBA Array Functions
ArrayCreates an array, containing a supplied set of values.
FilterReturns a subset of a supplied string array, based on supplied criteria.
JoinJoins a number of substrings into a single string.
LBoundReturns the lowest subscript for a dimension of an array.
SplitSplits a Text String into a Number of Substrings.
UBoundReturns the highest subscript for a dimension of an array.
VBA File Management Functions
CurDirReturns the current path, as a string.
DirReturns the first file or directory name that matches a specified pattern and attributes.
FileAttrReturns the mode of a file that has been opened using the Open statement.
FileDateTimeReturns the last modified date and time of a supplied file, directory or folder.
FileLenReturns the length of a supplied file, directory or folder.
GetAttrReturns an integer, representing the attributes of a supplied file, directory or folder.
Related Page
Vlookup in VBAUse the Excel spreadsheet Vlookup function from within VBA

Oulook VBA: Funcion para obtener un mensaje de correo.


Input = Ninguno
Output = Un objeto MailItem (Un mensaje de correo)

El proceso verifica si en la ventana activa se encuentra abierto un mensaje de correo, la funcion devuelve el mensaje para futura manipulación.

Function getMessage()

    If TypeName(Application.ActiveWindow) = "Inspector" Then
        Set getMessage = Application.ActiveWindow.CurrentItem
    Else
        Set getMessage = Nothing
    End If

End Function


Test:


Sub testFunction()

    Set mail = getMessage()
    
    If mail Is Nothing Then
        Debug.Print TypeName(mail)
    Else
        Debug.Print TypeName(mail)
        Debug.Print mail.Subject
        Debug.Print mail.Body
        For Each adjunto In mail.Attachments
            Debug.Print adjunto.fileName
        Next
    End If

End Sub

miércoles, 18 de marzo de 2020

Evolución de casos Coronavirus Ecuador