✕

Find the best tutors and institutes for Microsoft Excel Training

Find Best Microsoft Excel Training classes

✕

Search for topics

Lesson Posted on 07 Apr IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Why Option Explicit is Required in the VBA Editor

Kumar S

I have 5 Years of experience in MNC's and I worked on real time projects in VBA, Python, MSBI and SQL.

Hi Folks,Do you know Excel VBA Editor has functionality where you don't need to declare a variable in memory (DIM).But what will happen if you write a program without declaring a variable nothing happens?Then why do we need to declare a variable and what is Option Explicit? Yes here comes the answer.When... read more

Hi Folks,

Do you know Excel VBA Editor has functionality where you don't need to declare a variable in memory (DIM).

But what will happen if you write a program without declaring a variable nothing happens?

Then why do we need to declare a variable and what is Option Explicit? Yes here comes the answer.

When you write option explicit at the top of your VBA Editor VBA will force you to declare a variable it's a standard practice to declare a variable.

One more point when you are not declaring a variable in VBA by default VBA Program considers that as a variant data type which consumes more space, Also it increases programs running time.

So next time when you write a program don't forget to declare a variable.

Happy learning!!

Like 0

Comments Asked on 29 Mar IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training Exam Coaching/Foreign Education Exam Coaching/Advanced Placement Tests Coaching/Computer Science

How do I transfer AutoCorrect from one computer to another computer?

Answer

Lesson Posted on 24 Feb IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training

MS Excel: Formulas and Functions: Listed by Category

Siddhartha Mahapatra

I am working in IBM as a Lead Finance and Administration. I have gained exposure on financial reporting...

MS Excel: Formulas and Functions: Listed by Category Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual... read more

MS Excel: Formulas and Functions: Listed by Category

Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).

Below is a list of Excel formulas sorted by category.

(Enter a value in the field above to quickly find functions in the list below)

A. Lookup/Ref Functions

- ADDRESS (WS): Returns a text representation of a cell address
- AREAS (WS): Returns the number of ranges in a reference
- CHOOSE (WS, VBA): Returns a value from a list of values based on a given position
- COLUMN (WS): Returns the column number of a cell reference
- COLUMNS (WS): Returns the number of columns in a cell reference
- HLOOKUP (WS): Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number
- HYPERLINK (WS): Creates a shortcut to a file or Internet address
- INDEX (WS): Returns either the value or the reference to a value from a table or range
- INDIRECT (WS): Returns the reference to a cell based on its string representation
- LOOKUP (WS): Returns a value from a range (one row or one column) or from an array
- MATCH (WS): Searches for a value in an array and returns the relative position of that item
- OFFSET (WS): Returns a reference to a range that is offset a number of rows and columns
- ROW (WS): Returns the row number of a cell reference
- ROWS (WS): Returns the number of rows in a cell reference
- TRANSPOSE (WS): Returns a transposed range of cells
- VLOOKUP (WS): Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position

B. String/Text Functions:

- ASC (VBA): Returns ASCII value of a character
- CHAR (WS): Returns the character based on the ASCII value
- CHR (VBA): Returns the character based on the ASCII value
- CLEAN (WS): Removes all nonprintable characters from a string
- CODE (WS): Returns the ASCII value of a character or the first character in a cell
- CONCAT (WS): Used to join 2 or more strings together
- CONCATENATE (WS): Used to join 2 or more strings together (replaced by CONCAT Function)
- CONCATENATE with & (WS, VBA): Used to join 2 or more strings together using the & operator
- DOLLAR (WS): Converts a number to text, using a currency format
- EXACT (WS): Compares two strings and returns TRUE if both values are the same
- FIND (WS): Returns the location of a substring in a string (case-sensitive)
- FIXED (WS): Returns a text representation of a number rounded to a specified number of decimal places
- FORMAT STRINGS (VBA): Takes a string expression and returns it as a formatted string
- INSTR (VBA): Returns the position of the first occurrence of a substring in a string
- INSTRREV (VBA): Returns the position of the first occurrence of a string in another string, starting from the end of the string
- LCASE (VBA): Converts a string to lowercase
- LEFT (WS, VBA): Extract a substring from a string, starting from the left-most character
- LEN (WS, VBA): Returns the length of the specified string
- LOWER (WS): Converts all letters in the specified string to lowercase
- LTRIM (VBA): Removes leading spaces from a string
- MID (WS, VBA): Extracts a substring from a string (starting at any position)
- PROPER (WS): Sets the first character in each word to uppercase and the rest to lowercase
- REPLACE (WS): Replaces a sequence of characters in a string with another set of characters
- REPLACE (VBA): Replaces a sequence of characters in a string with another set of characters
- REPT (WS): Returns a repeated text value a specified number of times
- RIGHT (WS, VBA): Extracts a substring from a string starting from the right-most character
- RTRIM (VBA): Removes trailing spaces from a string
- SEARCH (WS): Returns the location of a substring in a string
- SPACE (VBA): Returns a string with a specified number of spaces
- SPLIT (VBA): Used to split a string into substrings based on a delimiter
- STR (VBA): Returns a string representation of a number
- STRCOMP (VBA): Returns an integer value representing the result of a string comparison
- STRCONV (VBA): Returns a string converted to uppercase, lowercase, proper case or Unicode
- STRREVERSE (VBA): Returns a string whose characters are in reverse order
- SUBSTITUTE (WS): Replaces a set of characters with another
- T (WS): Returns the text referred to by a value
- TEXT (WS): Returns a value converted to text with a specified format
- TEXTJOIN (WS): Used to join 2 or more strings together separated by a delimiter
- TRIM (WS, VBA): Returns a text value with the leading and trailing spaces removed
- UCASE (VBA): Converts a string to all uppercase
- UPPER (WS): Convert text to all uppercase
- VAL (VBA): Returns the numbers found in a string
- VALUE (WS): Converts a text value that represents a number to a number

C. Date/Time Functions:

- DATE (WS): Returns the serial date value for a date
- DATE (VBA): Returns the current system date
- DATEADD (VBA): Returns a date after which a certain time/date interval has been added
- DATEDIF (WS): Returns the difference between two date values, based on the interval specified
- DATEDIFF (VBA): Returns the difference between two date values, based on the interval specified
- DATEPART (VBA): Returns a specified part of a given date
- DATESERIAL (VBA): Returns a date given a year, month, and day value
- DATEVALUE (WS, VBA): Returns the serial number of a date
- DAY (WS, VBA): Returns the day of the month (a number from 1 to 31) given a date value
- DAYS (WS): Returns the number of days between 2 dates
- DAYS360 (WS): Returns the number of days between two dates based on a 360-day year
- EDATE (WS): Adds a specified number of months to a date and returns the result as a serial date
- EOMONTH (WS): Calculates the last day of the month after adding a specified number of months to a date
- FORMAT DATES (VBA): Takes a date expression and returns it as a formatted string
- HOUR (WS, VBA): Returns the hours (a number from 0 to 23) from a time value
- ISOWEEKNUM (WS): Returns the ISO week number for a date
- MINUTE (WS, VBA): Returns the minutes (a number from 0 to 59) from a time value
- MONTH (WS, VBA): Returns the month (a number from 1 to 12) given a date value
- MONTHNAME (VBA): Returns a string representing the month given a number from 1 to 12
- NETWORKDAYS (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NETWORKDAYS.INTL (WS): Returns the number of work days between 2 dates, excluding weekends and holidays
- NOW (WS, VBA): Returns the current system date and time
- SECOND (WS): Returns the seconds (a number from 0 to 59) from a time value
- TIME (WS): Returns a decimal number given an hour, minute and second value
- TIMESERIAL (VBA): Returns a time given an hour, minute, and second value
- TIMEVALUE (WS, VBA): Returns the serial number of a time
- TODAY (WS): Returns the current system date
- WEEKDAY (WS, VBA): Returns a number representing the day of the week, given a date value
- WEEKDAYNAME (VBA): Returns a string representing the day of the week given a number from 1 to 7
- WEEKNUM (WS): Returns the week number for a date
- WORKDAY (WS): Adds a specified number of work days to a date and returns the result as a serial date
- WORKDAY.INTL (WS): Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends)
- YEAR (WS, VBA): Returns a four-digit year (a number from 1900 to 9999) given a date value
- YEARFRAC (WS): Returns the number of days between 2 dates as a year fraction

D. Math/Trig Functions:

- ABS (WS, VBA): Returns the absolute value of a number
- ACOS (WS): Returns the arccosine (in radians) of a number
- ACOSH (WS): Returns the inverse hyperbolic cosine of a number
- AGGREGATE (WS): Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows
- ASIN (WS): Returns the arcsine (in radians) of a number
- ASINH (WS): Returns the inverse hyperbolic sine of a number
- ATAN (WS): Returns the arctangent (in radians) of a number
- ATAN2 (WS): Returns the arctangent (in radians) of (x,y) coordinates
- ATANH (WS): Returns the inverse hyperbolic tangent of a number
- ATN (VBA): Returns the arctangent of a number
- CEILING (WS): Returns a number rounded up based on a multiple of significance
- CEILING.PRECISE (WS): Returns a number rounded up to the nearest integer or to the nearest multiple of significance
- COMBIN (WS): Returns the number of combinations for a specified number of items
- COMBINA (WS): Returns the number of combinations for a specified number of items and includes repetitions
- COS (WS, VBA): Returns the cosine of an angle
- COSH (WS): Returns the hyperbolic cosine of a number
- DEGREES (WS): Converts radians into degrees
- EVEN (WS): Rounds a number up to the nearest even integer
- EXP (WS, VBA): Returns e raised to the nth power
- FACT (WS): Returns the factorial of a number
- FIX (VBA): Returns the integer portion of a number
- FLOOR (WS): Returns a number rounded down based on a multiple of significance
- FORMAT NUMBERS (VBA): Takes a numeric expression and returns it as a formatted string
- INT (WS, VBA): Returns the integer portion of a number
- LN (WS): Returns the natural logarithm of a number
- LOG (WS, VBA): Returns the logarithm of a number to a specified base
- LOG10 (WS): Returns the base-10 logarithm of a number
- MDETERM (WS): Returns the matrix determinant of an array
- MINVERSE (WS): Returns the inverse matrix for a given matrix
- MMULT (WS): Returns the matrix product of two arrays
- MOD (WS): Returns the remainder after a number is divided by a divisor
- ODD (WS): Rounds a number up to the nearest odd integer
- PI (WS): Returns the mathematical constant called pi
- POWER (WS): Returns the result of a number raised to a given power
- PRODUCT (WS): Multiplies the numbers and returns the product
- RADIANS (WS): Converts degrees into radians
- RAND (WS): Returns a random number that is greater than or equal to 0 and less than 1
- RANDBETWEEN (WS): Returns a random number that is between a bottom and top range
- RANDOMIZE (VBA): Used to change the seed value used by the random number generator for the RND function
- RND (VBA): Used to generate a random number (integer value)
- ROMAN (WS): Converts a number to roman numeral
- ROUND (WS): Returns a number rounded to a specified number of digits
- ROUND (VBA): Returns a number rounded to a specified number of digits
- ROUNDDOWN (WS): Returns a number rounded down to a specified number of digits
- ROUNDUP (WS): Returns a number rounded up to a specified number of digits
- SGN (VBA): Returns the sign of a number
- SIGN (WS): Returns the sign of a number
- SIN (WS, VBA): Returns the sine of an angle
- SINH (WS): Returns the hyperbolic sine of a number
- SQR (VBA): Returns the square root of a number
- SQRT (WS): Returns the square root of a number
- SUBTOTAL (WS): Returns the subtotal of the numbers in a column in a list or database
- SUM (WS): Adds all numbers in a range of cells
- SUMIF (WS): Adds all numbers in a range of cells based on one criteria
- SUMIFS (WS): Adds all numbers in a range of cells, based on a single or multiple criteria
- SUMPRODUCT (WS): Multiplies the corresponding items in the arrays and returns the sum of the results
- SUMSQ (WS): Returns the sum of the squares of a series of values
- SUMX2MY2 (WS): Returns the sum of the difference of squares between two arrays
- SUMX2PY2 (WS): Returns the sum of the squares of corresponding items in the arrays
- SUMXMY2 (WS): Returns the sum of the squares of the differences between corresponding items in the arrays
- TAN (WS, VBA): Returns the tangent of an angle
- TANH (WS): Returns the hyperbolic tangent of a number
- TRUNC (WS): Returns a number truncated to a specified number of digits

E. Statistical Functions:

- AVEDEV (WS): Returns the average of the absolute deviations of the numbers provided
- AVERAGE (WS): Returns the average of the numbers provided
- AVERAGEA (WS): Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0
- AVERAGEIF (WS): Returns the average of all numbers in a range of cells, based on a given criteria
- AVERAGEIFS (WS): Returns the average of all numbers in a range of cells, based on multiple criteria
- BETA.DIST (WS): Returns the beta distribution
- BETA.INV (WS): Returns the inverse of the cumulative beta probability density function
- BETADIST (WS): Returns the cumulative beta probability density function
- BETAINV (WS): Returns the inverse of the cumulative beta probability density function
- BINOM.DIST (WS): Returns the individual term binomial distribution probability
- BINOM.INV (WS): Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion
- BINOMDIST (WS): Returns the individual term binomial distribution probability
- CHIDIST (WS): Returns the one-tailed probability of the chi-squared distribution
- CHIINV (WS): Returns the inverse of the one-tailed probability of the chi-squared distribution
- CHITEST (WS): Returns the value from the chi-squared distribution
- COUNT (WS): Counts the number of cells that contain numbers as well as the number of arguments that contain numbers
- COUNTA (WS): Counts the number of cells that are not empty as well as the number of value arguments provided
- COUNTBLANK (WS): Counts the number of empty cells in a range
- COUNTIF (WS): Counts the number of cells in a range, that meets a given criteria
- COUNTIFS (WS): Counts the number of cells in a range, that meets a single or multiple criteria
- COVAR (WS): Returns the covariance, the average of the products of deviations for two data sets
- FORECAST (WS): Returns a prediction of a future value based on existing values provided
- FREQUENCY (WS): Returns how often values occur within a set of data. It returns a vertical array of numbers
- GROWTH (WS): Returns the predicted exponential growth based on existing values provided
- INTERCEPT (WS): Returns the y-axis intersection point of a line using x-axis values and y-axis values
- LARGE (WS): Returns the nth largest value from a set of values
- LINEST (WS): Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line
- MAX (WS): Returns the largest value from the numbers provided
- MAXA (WS): Returns the largest value from the values provided (numbers, text and logical values)
- MEDIAN (WS): Returns the median of the numbers provided
- MIN (WS): Returns the smallest read less

Like 0

Comments Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Lesson Posted on 05/09/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Variant Datatype: Excel/Access/Office VBA

Punit kumar

I have a total of six years plus of work experience.I have worked on multiple projects of different sizes....

The Variant data type is the data type for all variables that are not explicitly declared as some other type. The Variant data type has notype-declaration character. A Variant is a special data type that can contain any kind of data except fixed-length String data. A Variant can also contain the special... read more

The Variant data type is the data type for all variables that are not explicitly declared as some other type. The Variant data type has notype-declaration character.

A Variant is a special data type that can contain any kind of data except fixed-length String data. A Variant can also contain the special valuesEmpty, Error, Nothing, andNull. We can determine how the data in a Variant is treated using the VarType function or TypeName function.

We can assign any value at any time within a single module/program. If we don't declare the data type of any variable then it automatically declare as variant data type:

Sub modVariant1()

Dim VData as variant

VData =1234

Msgbox vData

vData="Aman"

Msgbox vData

End Sub

Sub modVariant2()

'In this example, data type is not declared

Dim VData

VData =1234

Msgbox vData

vData="Aman"

Msgbox vData

End Sub

Like 1

Comments Answered on 02/11/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

What is an Excel Macro?

Excel Training Excel Schooling.com

Excel Macro also called as VBA (Visual Basic Application). If you are good in Visual basic you can do more automation process in Excel. If you are not good in VB , you can try Record macro feature in Excel. Where it create programme based on your activities.

Like 2

Answers 1 Comments 3 Asked on 03/08/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Hello, I am 25 years old. Due to some financial problem. I didn t complete my study on time. Right now,... read more

Hello, I am 25 years old. Due to some financial problem. I didn?t complete my study on time. Right now, I am a student of B.Com final year from SOL Delhi University. Right now, I am also doing a job. I have a experience of 4 years as a Back end Executive but I don?t know what kind of skills should a back end guy have? In my company, My work is only CRM. I am very afraid now. What will happen in my future? Please provide me some guidance for my bright future. Please suggest me. read less

Answer

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Lesson Posted on 25/07/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

VBA Tip: Print Random Numbers In A Selected Range

Karthik Dale

II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...

Let’s say you want to print some random numbers in a selected range then you can use below Macro. Sub Print_Randomnumbers() Dim myrng As Range For Each myrng In Selection myrng.Value = Application.WorksheetFunction.Int(Rnd * 1000) Next myrng End Sub How it works? It's a for each loop... read more

Let’s say you want to print some random numbers in a selected range then you can use below Macro.

Sub Print_Randomnumbers()

Dim myrng As Range

For Each myrng In Selection

myrng.Value = Application.WorksheetFunction.Int(Rnd * 1000)

Next myrng

End Sub

How it works?

- It's a for each loop which loops through each cell in the selection.
- VBA function Rnd populate random number from 0 to 1.
- If you want to print numbers up to the range of 1000 then multiply Rnd with 1000.
- . Int function pulls the integer value from Rnd*1000.

read less

Like 0

Comments Lesson Posted on 30/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel

Saburudeen Ma

I started my teaching carrier since i was doing my UG, as part time faculty in a computer institute....

Purpose: Get a random number between 0 and 1. Return Value: Any decimal number between 0 and 1. Syntax: =RAND() Usage: RAND() function will returns a number between 0 and 1. For example, = RAND() will generate a number like 0.554533. RAND() calculates a new value each time the worksheet is calculated.... read more

Get a random number between 0 and 1.

Return Value:

Any decimal number between 0 and 1.

Syntax:

=RAND()

Usage:

RAND() function will returns a number between 0 and 1. For example, = RAND() will generate a number like 0.554533.

RAND() calculates a new value each time the worksheet is calculated. To stop random numbers from being updated, copy the cells that contain RAND to the clipboard, then use ** Paste Special -> Values** to convert to text.

To get a random number that doesn’t change when the worksheet is calculated, enter = RAND() in the formulas bar and then press F9 to convert the formula in to its result.

To generate a set of random numbers in multiple cells, select the cells, enter RAND() and * press ctrl+enter*.

To generate a random number between a and b, use this formula : **rand()*(b-a)+a**

Like 0

Comments Answered on 14/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Edulitix

Hi, You can check our course - Analyse and Visualize with Excel. Excel is the most popular tools used by analyst and MIS manager for getting insights from data. Create powerful Dashboards to enable data driven decision making.

Like 2

Answers 32 Comments Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

Lesson Posted on 26/03/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

How to install Analysis ToolPak in Excel

Dheeraj Kushwaha

I am having Sound knowledge of Adobe Creative cloud CC 2018. I deals in many creative domain such as...

The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. Click on the green File tab. The File tab in Excel 2010 or later replaces the Office Button... read more

The **Analysis ToolPak** is an **Excel add-in** program that provides data analysis tools for financial, statistical and engineering data analysis.

To load the Analysis ToolPak add-in, execute the following steps.

1. Click on the green File tab. The File tab in Excel 2010 or later replaces the Office Button (or File Menu) in previous versions of Excel.

2. Click on Options.

3. Under Add-ins, select Analysis ToolPak and click on the Go button.

4. Check Analysis ToolPak and click on OK.

5. On the Data tab, you can now click on **Data Analysis**.

The following dialog box below appears.

6. For example, select Histogram and click OK to create a Histogram in Excel.

read less Like 1

Comments UrbanPro.com helps you to connect with the best Microsoft Excel Training classes in India. Post Your Requirement today and get connected.

x

Ask a Question