Search This Blog

Sunday, 10 December 2017

WORKING WITH DATES IN VBA


This post is dedicated on handling the dates in VBA by using some in-built functions. I have explained some functions in short, used to work with dates.

Assigning a datevalue to a variable.

MyDate = "10-12-2017"                       'Directly passing the date
MyDate = DateSerial(2017, 12, 10)      'DateSerial function to assign a date
MyDate = Date                                      'Passing today's date using "date" function

Adding an interval to a date like years, months, quarters etc. We have an in-built function DATEADD for it which takes three arguments Interval, Number, Date.

Example:
DateAdd("yyyy", 1, "10-12-2017")     'Adding one year to a date; new date is 10-12-2018
DateAdd("m", 2, "10-12-2017")          'Adding 2 months to a date; new date is 10-02-2018
DateAdd("d", 3, "10-12-2017")           'Adding 3 days to a date; new date is 13-12-2017
DateAdd("ww", 1, "10-12-2017")       'Adding a week to a date; new date is 17-12-2017
DateAdd("q", 1, "10-12-2017")           'Adding a quarter to a date; new date is 10-03-2018

Similarly, we have other intervals to add hours, minutes and seconds to a date. Strings used to specifiy the others intervals:
"h" - hours, "n" - minutes, "s" - seconds

Fetching or extracting a part of the date. We have DATEPART function in VBA to do the job. It takes four arguments Interval, Date, FirstDayofWeek, FirstWeekofYear. Last two arguments are optional.

FirstDayofWeek - Specifies the weekday that should be used as the first day of the week. Default is vbSunday.
FirstWeekofYear - Specifies the week that should be used as the first week of the year. Default is vbFirstJan1.

Example:
DatePart("d", "10-12-2017")          'Returns day of month (1-31) i.e. 10
DatePart("m", "10-12-2017")         'Returns month i.e. 12
DatePart("yyyy", "10-12-2017")    'Returns year i.e. 2017
DatePart("q", "10-12-2017")          'Returns quarter i.e. 4
DatePart("ww", "10-12-2017")      'Returns week of year (1-53) i.e. 50
DatePart("w", "10-12-2017")         'Returns day of week (1-7) i.e. 1

Likewise DATEADD function, we have other intervals in DATEPART function also. Strings used to specify other intevals:
"h" - hours, "n" - minutes, "s" - seconds, "y" - day of year (1-366)

We also have MONTH and MONTHNAME function to return the month number and name of the month respectively. MONTH function takes a date only as an argument. MONTHNAME function takes two arguments Month, Abbreviation. Second argument is optional which takes boolean value TRUE or FALSE. TRUE to abbreviate the month name like Jan, Feb, Mar etc.

Example:
Month("10-12-2017")                                            'Returns 12
MonthName(Month("10-12-2017"), False)           'Returns "December "
MonthName(Month("10-12-2017"), True)            'Returns "Dec"

Likewise MONTH and MONTHNAME function, we have WEEKDAY and WEEKDAYNAME function to return the day of the week and name of the weekday respectively. WEEKDAY takes two arguments Date and FirstDayofWeek. Second argument is optional which specifies the weekday that should be used as first day of the week. Default is vbSunday if nothing specifies.

WEEKDAYNAME takes three arguments Weekday, Abbreviation and FirstDayofWeek. Last two arguments are optional. Abbreviation takes boolean value TRUE or FALSE. TRUE to abbreviate weekday name like Sun, Mon etc. FirstDayofWeek specifies the weekday that should be used as first day of the week. Default is vbSunday if nothing specifies.

Example:
Weekday("10-12-2017", vbMonday)    'Returns the day of the week i.e. 7
WeekdayName(Weekday("10-12-2017", vbSunday), False, vbSunday)    'Returns "Sunday"
WeekdayName(Weekday("10-12-2017", vbMonday), True, vbSunday)    'Returns "Sat"

Formatting dates. VBA has an in-built function FORMATDATETIME to assign a format to the given date. This function takes two arguments Expression, NameFormat. Second argument is optional.

Example:
FormatDateTime("10-12-2017")                                         'Returns 10-12-2017
FormatDateTime("10-12-2017", vbLongDate)                   'Returns 10 December 2017
FormatDateTime("10-12-2017", vbShortDate)                   'Returns 10-12-2017
FormatDateTime("10-12-2017 09:30:00", vbLongTime)   'Returns 09:30:00
FormatDateTime("10-12-2017 09:30:00", vbShortTime)   'Returns 09:30

Finally, wrapping up this post with ISDATE function used to check whether the passed value is a valid date or not. It takes only one arguement i.e. Expression. It returns TRUE if the passed value is a proper date, time or or a text representation of date or time and FALSE for all non-date strings and numbers.

IsDate("10-12-2017")               'Returns TRUE
IsDate(43079)                           'Returns FALSE
IsDate("Excel VBA Tips")       'Returns FALSE
IsDate(#9:30:00 AM#)             'Returns TRUE

Is this post helpful?

Kindly post your valuable comments or suggestions.

Thanks!

Thursday, 26 October 2017

ENABLE OR DISABLE CHECKBOX ON CLICK OF ANOTHER CHECKBOX



1) Write the below code on the initialize event of  your userform

Private Sub UserForm_Initialize()
Me.ChildBox1.Enabled = False
Me.ChildBox2.Enabled = False
End Sub

It makes the Child-1 & Child-2 disabled which means only the Parent checkbox will be enabled when the userform initializes.

2) Write the below code on Click event of the Parent checkbox.

Private Sub parentbox_Click()
If Me.parentbox.Value = True Then
Me.ChildBox1.Enabled = True
Me.ChildBox2.Enabled = True
ElseIf Me.parentbox.Value = False Then
Me.ChildBox1.Enabled = False
Me.ChildBox2.Enabled = False
End If
End Sub

It makes the Child-1 and Child-2 enabled when Parent is checked and disabled when Parent is unchecked.

Is the post useful? 

Please put your comments in the comment section and subscribe the blog:) Thank you.



Friday, 15 September 2017

FETCHING DATA FROM A TABLE BASED ON MORE THAN ONE LOOKUP VALUE


Here I want to retrieve values for  Data1, Data2, Data3 & Data4 from the given dataset (A1:F10) based on two lookup values that is EmpCode and Dept.

Formula used in range C13:F13

=VLOOKUP(A13&B13,CHOOSE({1,2,3,4,5},A1:A10&B1:B10,C1:C10,D1:D10,E1:E10,F1:F10),{2,3,4,5},0) with CSE

This can also be done using the Index/Match function. Below is the formula:

=INDEX($A$1:$F$10,MATCH(1,IF($A$1:$A$10=$A$13,IF($B$1:$B$10=$B$13,1)),0),{3,4,5,6}) with CSE

Is the post useful?

Kindly put your comment in the comment section and subscribe the blog:)


Friday, 1 September 2017

FORMULA TO ASSIGN RANK (RANK FUNCTION ALTERNATIVE)



We have a in-built RANK function in excel to assign rank to our data but the issue with this function is it skips ranking(s) if there is a tie. In above case, it assigns 3 to 40 and 5 to 38 and skips ranking 4. But the formula that has been used in column C is a perfect substitute for it and returns the correct rankings even if there are duplicate values in our data. It can be used in place of excel RANK function.

Formula used in C2:C11

=SUM(0+(FREQUENCY(IF($A$2:$A$11>A2,$A$2:$A$11),$A$2:$A$11)>0))+1 with CSE

You can use "<" in place of  ">" if you want to assign the ranking in ascending order.

Thanks for reading it:)

Kindly put your valuable comment in the comment box and follow my blog.



Thursday, 15 September 2016

FORMULA TO REVERSE DIGITS IN A CELL


I explored a lot on internet for a customized formula which can reverse the digits in a cell and found many solutions using VBA user defined function or by installing add-in but couldn't find any help with excel formula. So here is the formula for it that I developed after 3 hours of struggle:)

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,POWER(10,ROW(INDIRECT("1:"&LEN(A1)))-1)) 

Please post in comment section if you have some other short and simple way to do it.

Thanks!

Tuesday, 16 August 2016

MAKE NEXT CONTROL VISIBLE ONCE THE FOCUS LOSES FROM FIRST CONTROL (KEYDOWN EVENT)


Below is the code to make next textbox visible once you finish typing in first textbox and press enter key or tab.

Controls on my form:

4 labels:                     lblCode, lblName, lblDept and lblDesig
4 textboxes:               txtCode, txtName, txtDept and txtDesig
1 CommandButton:   cmdSubmit

First I have made all the controls invisible except first textbox and label (lblcode & txtcode in my case) while loading the userform.

Private Sub UserForm_Initialize()
Me.txtCode.SetFocus
Me.txtName.Visible = False
Me.txtDept.Visible = False
Me.txtDesig.Visible = False
Me.lblName.Visible = False
Me.lblDept.Visible = False
Me.lblDesig.Visible = False
Me.cmdSubmit.Visible = False
End Sub

Only the first textbox and label will be visible when userform loads.


Below code on KeyDown Event of txtCode will make next set of controls (lblName & txtName) visible when you press enter key or tab key after typing in first textbox (txtCode) and set the focus on txtName.

Private Sub txtCode_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
        If txtCode.Text <> vbNullString Then
            txtName.Visible = True
            lblName.Visible = True
            txtName.SetFocus
        End If
End If
End Sub

Below code on KeyDown Event of txtName will make next set of controls (lblDept & txtDept) visible when you press enter key or tab key after typing in second textbox (txtName) and set the focus on txtDept.

Private Sub txtName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
    If txtName <> vbNullString Then
        txtDept.Visible = True
        lblDept.Visible = True
        txtDept.SetFocus
    End If
End If
End Sub

Below code on KeyDown Event of txtDept will make next set of controls (lblDesig & txtDesig) visible when you press enter key or tab key after typing in third textbox (txtDept) and set the focus on txtDesig.

Private Sub txtDept_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
    If txtDept <> vbNullString Then
        txtDesig.Visible = True
        lblDesig.Visible = True
        txtDesig.SetFocus
    End If
End If
End Sub

Below code on KeyDown Event of txtDesig will make next control (cmdSubmit) visible when you press enter key or tab key after typing in last textbox (txtDesig) and set the focus on cmdSubmit.

Private Sub txtDesig_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
    If txtDesig <> vbNullString Then
        cmdSubmit.Visible = True
        cmdSubmit.SetFocus
    End If
End If
End Sub

vbKeyReturn - Enter key on your keyboard
vbKeyTab - Tab key on your keyboard

For more Key Code Constants you can refer to Microsoft site using this link.

Thanks.

Monday, 15 August 2016

CALCULATE TOTAL NUMBER OF A WEEKDAY IN A MONTH



Cell D2 : Data validation for Month Name
Cell E2: Data validation for Weekday Name

Formula in F2:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(D2),MONTH(D2),1)&":"&EOMONTH(D2,0))))=MATCH(E2,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)))

Note: Make sure the month name list (A2:A13 in my case) that you are using for data validation, is in date format, not in text format.

Is the post helpful?

Please post your valuable comment. Thanks!