Learn Excel VBA Training with Free Lessons & Tips

Hi All,

I want to share my experience here and why I chose to take up classes on Advanced excel. This is not technical lesson but a motivation to improve your skills.

I was leading Data Analyst team in a startup company. We had to recruit four data analyst with Advanced Excel, VBA and any database knowledge.

We got around 45 - 70 profiles shortlisted for interview out of 100s of profiles from HR. We were shocked that candidates were not able to answer any Advanced Excel questions who have work experience in Excel for 3 to 4 years. Candidates with Advanced Excel knowledge are lacking VBA knowledge. When they have both of these, database was problem for them.

If you are working in Excel for atleast one year then you must have all related knowledge to get good salary. Advanced Excel ~ VBA ~ Database (Access/SQL Server) are good combination skills to survive in IT industry for atleast 10 years.

Regards,
Sai Krishna.

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.

Hi,
Most of us know that using Vlookup function we can get the right column values, however by using Choose function in Vlookup table array, we can pull left column values as well. In the below screenshot lookup value column is not left most even though Vlookup is able to get the Product Name or Branch details

Note1: Choose function 1st argument contains flower brackets

Note2: If you change the column index number to 3 then it will populate branch details.

Hope it's a helpful tip. Will catch you in my next tip

RIGHT function in VBA returns the Specified number of characters from the end of a text string

Syntax: Right(String,Length)

Sub RIGHT_1()

Dim mystring As String

mystring = "Narendra Modi"

MsgBox Right(mystring, 4)

'Output: "Modi"

'To get first name dynamically use RIGHT & InStr functions

MsgBox Right(mystring, Len(mystring) - InStr(1, mystring, " "))

'Output: "Modi"

End Sub

If you want to populate weekdays (Monday to Friday) in a range, follow below steps.

Step1: In Cell A1 write any month 1st date. Let’s say 02/01/2017 (Feb 1st 2017)

Step2: In Cell C1 write this formula =WORKDAY.INTL($A$1,IF(OR(WEEKDAY($A$1)=1,WEEKDAY($A$1)=7),ROW(A1),ROW(A1)-1),1)

Step3: Drag Cell C1 formula down…Check the below screenshot

INSTR works similar to FIND function in Excel. INSTR search for specific text in a given text and returns the position number of what you are searching for.

Syntax: InStr( [start_num], string, searchingfor, [compare] )

Sub INSTR_1()

MsgBox InStr(1, "Narendra Modi", " ")

'Output: 9

MsgBox InStr(1, "Narendra Modi", "Modi")

'Output: 10

MsgBox InStr(1, "Narendra Modi", "MODI")

'Output: 0

'To find the 2nd "e" postion number in "excel is awesome"

MsgBox InStr(InStr(1, "excel is awesome", "e") + 1, "excel is awesome", "e")

'Output:4

'Note: INSTR function is a case-sensitive and if it's not found the value then output will be 0

End Sub

The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.

By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

The 1904 Date System

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the serial number 34519.

By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.

The Difference Between the Date Systems

Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers, as follows.

                      Serial number
   Date system        of July 5, 1998
   ----------------------------------

   1900 date system   35981
   1904 date system   34519
 

The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days bigger than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).

How to handle through VBA

Dim lngRow as long 

If this workbook.Date1904 = True Then

For lngRow = 2 To sheet("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

if isdate(sheet("Sheet1")) then

   sheet("sheet1").Cells(lngRow, iCol).Value = sheet("sheet1").Cells(lngRow, iCol).Value + 1462

end if 

Next lngRow

End if 

Using FORMAT Function in VBA we can modify the format of Numbers, Dates, Times & String.

Syntax: FORMAT(expression, format, [firstdayofweek], [firstweekofyear])

1. Formatting "Numbers" using FORMAT function in VBA

SUB Format_Numbers()

MsgBox format(8686836999, "####-###-###")

'Output: '8686-836-999'

MsgBox format(143.5, "#,##.00")

'Output: '143.50'

MsgBox format(1729.4, "Currency")

'Output: '$1,729.40'

MsgBox format(0.9156, "Percent")

'Output: '91.56%'

MsgBox format(1729.4, "Standard")

'Output: '1,729.40'

End Sub

2. Formatting "Dates" using FORMAT function in VBA

Sub FORMAT_Dates()

MsgBox format("3/21/2017", "Short Date")

'Output: '3/21/2017'

MsgBox format("3/21/2017", "Medium Date")

'Output: '21-Mar-17'

MsgBox format("3/21/2017", "Long Date")

'Output: 'Tuesday, March 21, 2017'

MsgBox format("3/21/2017", "mmm dd")

'Output: 'Mar 21'

End Sub

3. Formatting "Time" using FORMAT function in VBA

Sub FORMAT_Time()

MsgBox format("6:30:45", "Short Time")

'Output: '06:30'

MsgBox format("6:30:45", "LONG Time")

'Output: '6:30:45 AM'

MsgBox format("6:30:45", "MEDIUM Time")

'Output: '06:30 AM'

MsgBox format("14:30:45", "HH:MM")

'Output: '14:30'

End Sub

4. Formatting "Text" using FORMAT function in VBA

Sub FORMAT_Text()

MsgBox format("EXCEL IS MAGIC", "<")

'Output: 'excel is magic' (convert text to lower case)

MsgBox format("excel is magic", ">")

'Output: 'EXCEL IS MAGIC' (convert text to upper case)

End Sub