Excel-VBA : Math Functions – FIX()

Description: The FIX() function in MS excel returns the Integer part of a number

NOTE: If the number is negative, FIX() will return the first negative number greater than equal to the given number.

Read about Excel-VBA : INT() If the number is negative, and you want the first negative number less than equal to the given number.

Format:

VBA Function : FIX (number)

Read moreExcel-VBA : Math Functions – FIX()

Excel-VBA/Formula : Math Functions – INT()

Description: The INT() function in MS excel returns the Integer part of a number

NOTE: If the number is negative, INT() will return the first negative number less than equal to the given number.

Read about Excel-VBA : FIX() If the number is negative, and you want the first negative number greater than equal to the given number.

Format:

VBA Function : INT(number)

Excel Formula : INT(number)

Arguments:

Read moreExcel-VBA/Formula : Math Functions – INT()

Excel-VBA : Math Functions – VAL()

Excel-VBA : Math Functions – VAL()

Description: The VAL function in MS excel returns the number found in the input string

NOTE: This function stops reading the string when it finds first non-numeric charater. Blank spaces are not considered as non-numeric character.

Format:

VBA Function : VAL(string)

Arguments:

Excel-VBA: Math Functions – ROUND()

Description: 

The ROUND function in MS excel returns a rounded number based on the specified number of digits.

Note : ROUND() works slightly diffrent when used as Excel formula, Click here to know about it Formula-ROUND()

Format:

As VBA Function : ROUND(number, [decimal places])

Note : If you don’t provide the decimal places the function will consider it as 0 and returns the Integer.

Arguments:

Read moreExcel-VBA: Math Functions – ROUND()

Excel-VBA: Math Functions – RND()

Description: 

The RND function in MS excel returns the random number generated between two specified ranges.

Format:

As VBA Function : Int((Max – Min + 1) * Rnd + Min)

Arguments:
  • Max and Min
    • Mandatory
    • Type: Numbers
    • Random number gets generated between these two numbers (Max – the highest value the random number can be. Min – The smallest value the random number can be)

    Read moreExcel-VBA: Math Functions – RND()

Excel- VBA/Formula: Math Functions – LOG()

Description:

The LOG function in MS excel returns the logarithm of a number to the given base.

NOTE: LOG function takes different parameters when it is used in WorkSheet and used as a VBA function.

Format:

As VBA Function : LOG(number) , default base is e = 2.71828183

In WorkSheet : LOG(number, [base])

Arguments:

Read moreExcel- VBA/Formula: Math Functions – LOG()

VBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()

WeekDay()

Description:

The WeekDay function takes Date as a parameter and returns a number between 1 and 7, that is the week day of the date provided.

Format:

WeekDay (strDate [Firstdayofweek])

Arguments:

VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

This tutorial will teach you how to get all the working days or weekdays in a specified date range(excluding Satudays and Sundays), simple but very useful when you have come up with a excel where you have to work with all the business working days. Example: If the date range is mentioned between 8-Jan-2014 to … Read more VBA-Excel: Get all the WeekDays or Working days in Specified Date Range, (excluding Satudays and Sundays)

VBA-Excel: User Define Functions

Microsoft Excel has lot of built in formulas like Sin(), Avg() and many more, which we discuss in detail in future articles, but apart from that you can write your own functions which will act as any other built-in formula in excel , Which is one of the powerful  feature of Excel.

Like other formulas, you can apply your function in each cell or range of cells.

Syntax:

Public Function functionName (Arg As dataType,……….) As dataType

or

Private Function functionName (Arg As dataType,……….) As dataType

Arg As dataType -> data type of argument

The second data type is the return type of function.

Public : The function is applicable to the whole project.

Read moreVBA-Excel: User Define Functions