Excel Macro Training

Excel Macro Training

Trending Questions and Lessons

Follow 1,065 Followers

Feed

Ask a Question

All

Lessons

Discussion

Overview

Lessons 24

Total Shares  

Lesson Posted on 13/06/2017 Advanced C++ IT Courses/Programming Languages/C Language IT Courses/Programming Languages/C++ Language +3 Excel Macro Training Excel VBScript Training Excel VBA Training less

Compiler vs Interpreter

Umasankar N.

Software Professional Trainer and Consultant with 23+ years of software design & development experience...

Compiler Interpreter Convert all the code into binary format and execute. Convert one statement at a time and execute, then Convert next statement and execute. After conversion, source code is not required. Binary is enough to execute the program. As it is converting statement by statement,... read more
Compiler Interpreter
Convert all the code into binary format and execute. Convert one statement at a time and execute, then Convert next statement and execute.
After conversion, source code is not required. Binary is enough to execute the program. As it is converting statement by statement, Always source code is required in order execute the program again and again.
Once created binary file, execution will be fast. As it is converting statement to binary every time, execution will be slow.
Need to recompile if there is a modification in single statement also. In case of huge lines of code, testing the code will take more time, as we need to recompile and execute. Recompiling will take more time. Modification can be done quickly and execute that particular statement immediately after modification.
It required less memory. It required more memory.
Example : C, C++ Example : Perl, VB, VBA

 

read less
Comments
Dislike Bookmark Share

Lesson Posted on 10/06/2017 Excel Macro Training Excel VBA Training Excel VBScript Training +4 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 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

Concatenate Function

Saburudeen Ma

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

Concatenate , one of the text functions, to join two or more text strings into one string. The Excel Concatenate function joins together a series of supplied text strings or other values, into one combined text string. The syntax of the function is: CONCATENATE( text1, , ... ) Where the text arguments... read more

Concatenate , one of the text functions, to join two or more text strings into one string.

The Excel Concatenate function joins together a series of supplied text strings or other values, into one combined text string.

The syntax of the function is:

CONCATENATE( text1, [text2], ... )

Where the text arguments are a set of one or more text strings or other values that you want to join together.

Note that:

  • In current versions of Excel,  you can supply up to 255 text arguments to the Concatenate function, but in Excel 2003, the function can only accept up to 30 text arguments.
  • In Excel 2016, the Concatenate function has been replaced by the Concat function. However, the Concatenate function is still available in Excel 2016, to ensure compatibility with earlier versions of Excel.
read less
Comments
Dislike Bookmark Share

Lesson Posted on 08/06/2017 IT Courses/MS Office Software Training IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel +3 Excel Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

Datedif() Function

Saburudeen Ma

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

Purpose: This function calculates difference between two dates. it can show the result in days, months and years. Syntax: =datedif(start_date,end_date,"unit") start_date This is earliest of the two dates end_date This is the most recent of the two dates Unit The... read more

Purpose:

This function calculates difference between two dates. it can show the result in days, months and years.

Syntax:

=datedif(start_date,end_date,"unit")

start_date

This is earliest of the two dates

end_date

This is the most recent of the two dates

Unit

The type of information that you want returned

 

Unit

Returns

"Y"

The number of complete years in the period.

"M"

The number of complete months in the period.

"D"

The number of days in the period.

"MD"

The difference between the days in start_date and end_date. The months and years of the dates are ignored.

"YM"

The difference between the months in start_date and end_date. The days and years of the dates are ignored

"YD"

The difference between the days of start_date and end_date. The years of the dates are ignored.

               

Example

Start_date End_date Unit Result Notes
21-Aug-15 6-Jul-17 Y 1 Difference in complete years
21-Aug-15 6-Jul-17 M 22 Difference in complete months
21-Aug-15 6-Jul-17 D 685 Difference in days
21-Aug-15 6-Jul-17 MD 15 Difference in days, ignoring months and years
21-Aug-15 6-Jul-17 YM 10 Difference in months, ignoring days and years
21-Aug-15 6-Jul-17 YD 320 Difference in days, ignoring years

 

Remarks:

  • Dates are stored as sequential serial numbers so they can be used in calculations. By default, December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.
  • The DATEDIF function is useful in formulas where you need to calculate an age.
read less
Comments
Dislike Bookmark Share

Top Contributors

Connect with Expert Tutors & Institutes for Excel Macro Training

Lesson Posted on 07/06/2017 IT Courses/Advanced VBScript Excel Macro Training Excel VBA Training +4 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 IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel less

Excel Tip: Conditional Formatting To Highlight First Value Among Duplicates.

Karthik Dale

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

In Column “C”, I have product list and some of them are duplicates. My requirement is to format with background color of green for 1st found value among the duplicates. To solve this, I used formula in Conditional formatting. Check the below formula & screenshot. Formula in Conditional... read more

In Column “C”, I have product list and some of them are duplicates. My requirement is to format with background color of green for 1st found value among the duplicates. To solve this, I used formula in Conditional formatting. Check the below formula & screenshot.

Formula in Conditional Formatting is: =COUNTIFS($C$2:C2,C2)=1

Note: In the above formula if you write 2 instead of 1 then it will highlight 2nd duplicate value.

read less
Comments
Dislike Bookmark Share

Lesson Posted on 06/06/2017 Excel Macro Training Excel VBA Training IT Courses/Advanced VBScript +2 Excel VBScript Training IT Courses/MS Office Software Training/MS Access less

Early Binding vs Late Binding

Umasankar N.

Software Professional Trainer and Consultant with 23+ years of software design & development experience...

Binding is a process of matching function calls written by the programmer to the actual code (internal or external) that implements the function. All functions called in code must be bound before the code can be executed.Early binding is a process of binding matching function calls written by the programmer... read more

Binding is a process of matching function calls written by the programmer to the actual code (internal or external) that implements the function. All functions called in code must be bound before the code can be executed.

Early binding is a process of binding matching function calls written by the programmer to the actual code that implements the function at compile time

Late binding is a process of binding matching function calls written by the programmer to the actual code that implements the function at run time

Early Binding benefits:
    The use of Intellisense,
    The use of constants,
    Use of the Object Browser,
    Help using F1.

Late Binding benefits:
    Does not require declaring reference libraries.
    Does not suffer from versioning issues.

Drawbacks of Late Binding:
    Loss of Intellisense during development.
    No constants.
    Late binding is slower than early binding.

read less
Comments
Dislike Bookmark Share

Lesson Posted on 06/06/2017 Excel Macro Training Excel VBScript Training Excel VBA Training +1 IT Courses/MS Office Software Training/MS Access less

VBA Static Array Vs Dynamic Array

Umasankar N.

Software Professional Trainer and Consultant with 23+ years of software design & development experience...

There are two types of array in VBA, Static array and Dynamic array. Static array: Array is dimenstioned during design time. Array size can not be changed. By using erase function, only array element values in the memory will be emptied, elements memory will not be deleted. Memory is fixed during... read more

There are two types of array in VBA, Static array and Dynamic array.

Static array:

  • Array is dimenstioned during design time.
  • Array size can not be changed.
  • By using erase function, only array element values in the memory will be emptied, elements memory will not be deleted.
  • Memory is fixed during design time.

Dynamic array:

  • Array is dimenstioned during runtime
  • Array size can be changed (Shrink or Enlarge) any number of times.
  • By using erase function, both array element values and elements memory will be deleted.
  • We can utilise the memory efficiently.
read less
Comments
Dislike Bookmark Share

Top Contributors

Connect with Expert Tutors & Institutes for Excel Macro Training

Lesson Posted on 06/06/2017 Excel Macro Training Excel VBA Training Excel VBScript Training +1 IT Courses/MS Office Software Training/MS Access less

VBA Fixed And Variable String

Umasankar N.

Software Professional Trainer and Consultant with 23+ years of software design & development experience...

VBA (Excel VBA, Access VBA, Word VBA, Outllook VBA, Power Point VBA, MS Project VBA) defined two types of string data type, Fixed string and Variable string. Fixed String: Length is fixed, data beyond that limit will be truncated. Memory will be blank, in case of number of character is less than... read more

VBA (Excel VBA, Access VBA, Word VBA, Outllook VBA, Power Point VBA, MS Project VBA) defined two types of string data type, Fixed string and Variable string.

Fixed String:

  • Length is fixed, data beyond that limit will be truncated.
  • Memory will be blank, in case of number of character is less than the maximum length.

Variable String:

  • Length will be varied depends on the data length.
  • Allocated only as much as memory based on the data length.

 

 

read less
Comments
Dislike Bookmark Share

Lesson Posted on 30/05/2017 IT Courses/Advanced VBScript Advanced Computer Course coaching IT Courses/MS Office Software Training/Microsoft Excel Training +4 IT Courses/MS Office Software Training Excel VBA Training Excel Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

Excel Tip: Use Index Or Match Instead Of Vlookup Or Hlookup.

Karthik Dale

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

Hi, In today's Excel topic we will see how Index & Match function is superior than Vlookup/Hlookup. With Index and Match, the return value need not be in the same column as the lookup column, unlike VLOOKUP where the return value has to be in the specified range. How does this matter? With VLOOKUP,... read more

Hi,

In today's Excel topic we will see how Index & Match function is superior than Vlookup/Hlookup.

With Index and Match, the return value need not be in the same column as the lookup column, unlike VLOOKUP where the return value has to be in the specified range. How does this matter? With VLOOKUP, you have to know the column number that contains the return value. While this sounds like no big deal, it can be cumbersome when you have a large table and have to count the number of columns. Also, if you were to add/remove a in your table, you have to recount and update the col_index_num argument. With Index and Match, no counting is required as the lookup column is different from the column that has the return value.

With Index and Match, you can specify either a row or a column in an array or even specify both. This means you can look up values both vertically and horizontally.

Index and Match can be used to lookup values in any column. Unlike VLOOKUP where you can only look up to a value in the first column in a table, Index and Match will work if your lookup value is in the first column, the last, or anywhere in between.

Index and Match offers the flexibility of making dynamic reference to the column which contains the return value. What this means is that you can add columns to your table and INDEX and MATCH will not break. On the other hand, VLOOKUP breaks if you had to add a column to the table as it makes a static reference to the table.

read less
Comments
Dislike Bookmark Share

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

Difference between Find and Search Function in excel

Umasankar N.

Software Professional Trainer and Consultant with 23+ years of software design & development experience...

In excel there are two functions which are taking same arguments and return type. SEARCH() --> Search a text within text, Case insensitive Example: Search("t","Management Team") and Search("T","Management Team") both will return same position i.e. 10 FIND() --> Find a text within text,... read more

In excel there are two functions which are taking same arguments and return type.

SEARCH() --> Search a text within text, Case insensitive

  • Example: Search("t","Management Team")  and Search("T","Management Team") both will return same position i.e. 10

FIND() --> Find a text within text, Case sensitive i.e. Search("t","Management Team")  and Search("T","Management Team") both will return same position ie

  • Example: Search("t","Management Team")  and Search("T","Management Team") both will return differenct position i.e.
  • Find("t","Management Team")  will return 10
  • Find("T","Management Team") will return 12

Same functions can be used in excel vba macro also using WorksheetFunction.Search and WorksheetFunction.Find

 

 

read less
Comments
Dislike Bookmark Share

Top Contributors

Connect with Expert Tutors & Institutes for Excel Macro Training

Lesson Posted on 24/05/2017 Excel Macro Training Excel VBA Training Excel VBScript Training +4 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 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

Excel Tip: VLOOKUP formula limitations

Karthik Dale

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

1. Vlookup is not case-sensitive 2. If Lookup value has duplicates in the lookup column, then it returns 1st found value 3. Vlookup cannot pull values which are on the left side from Lookup column 4. Vlookup gives inaccurate output if the Lookup table range is huge. read more

1. Vlookup is not case-sensitive

2. If Lookup value has duplicates in the lookup column, then it returns 1st found value

3. Vlookup cannot pull values which are on the left side from Lookup column

4. Vlookup gives inaccurate output if the Lookup table range is huge.

read less
Comments
Dislike Bookmark Share

About UrbanPro

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

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more