Pinoy Tutorials, Tips and Tricks

Working with Date and Time VB.Net

<b>VB.NET's DateTime</b> structure represents an instant in time which is commonly expressed as a particular date and time of the day. It's a part of your daily life while creating your applications which makes it easy for you to manipulate system's date and time. In this article I'll be showing you the commonly used <b>properties</b> and <b>methods</b> and example <b>usage</b> of the Date and Time in VB.Net.<br /><br />Use the Date data type to contain date values, time values, or date and time values. The default value of Date is 0:00:00 (midnight) on January 1, 0001. You can get the current date and time from the DateAndTime class.<br /><br /><h2><b>DateTime Properties</b></h2>The table below shows some of the commonly used properties of the DateTime Structure: <table cellpadding="0" cellspacing="0"><tbody><th style="width:25%;">Property</th><th>Description</th><tr><td><b>Date</b></td><td>Gets the date component of this instance.</td></tr><tr><td><b>Day</b></td><td>Gets the day of the month represented by this instance.</td></tr><tr><td><b>DayOfWeek</b></td><td>Gets the day of the week represented by this instance.</td></tr><tr><td><b>DayOfYear</b></td><td>Gets the day of the year represented by this instance.</td></tr><tr><td><b>Hour</b></td><td>Gets the hour component of the date represented by this instance.</td></tr><tr><td><b>Kind</b></td><td>Gets a value that indicates whether the time represented by this instance is based on local time, Coordinated Universal Time (UTC), or neither.</td></tr><tr><td><b>Millisecond</b></td><td>Gets the milliseconds component of the date represented by this instance.</td></tr><tr><td><b>Minute</b></td><td>Gets the minute component of the date represented by this instance.</td></tr><tr><td><b>Month</b></td><td>Gets the month component of the date represented by this instance.</td></tr><tr><td><b>Now</b></td><td>Gets a <b>DateTime</b> object that is set to the current date and time on this computer, expressed as the local time.</td></tr><tr><td><b>Second</b></td><td>Gets the seconds component of the date represented by this instance.</td></tr><tr><td><b>Ticks</b></td><td>Gets the number of ticks that represent the date and time of this instance.</td></tr><tr><td><b>TimeOfDay</b></td><td>Gets the time of day for this instance.</td></tr><tr><td><b>Today</b></td><td>Gets the current date.</td></tr><tr><td><b>UtcNow</b></td><td>Gets a <b>DateTime</b> object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC).</td></tr><tr><td><b>Year</b></td><td>Gets the year component of the date represented by this instance.</td></tr></tbody></table><br/><br/><h2>DateTime Methods</h2>The table below shows the commonly used Methods of the DateTime Structure. <table cellpadding="0" cellspacing="0"><tbody><th>Method Name</th><th>Description</th><tr><td><b>Public Function Add ( value As TimeSpan ) As DateTime</b></td><td>Returns a new DateTime that adds the value of the specified TimeSpan to the value of this instance.</td></tr><tr><td><b>Public Function AddDays ( value As Double ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of days to the value of this instance.</td></tr><tr><td><b>Public Function AddHours ( value As Double ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of hours to the value of this instance.</td></tr><tr><td><b>Public Function AddMinutes ( value As Double ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of minutes to the value of this instance.</td></tr><tr><td><b>Public Function AddMonths ( months As Integer ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of months to the value of this instance.</td></tr><tr><td><b>Public Function AddSeconds ( value As Double ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of seconds to the value of this instance.</td></tr><tr><td><b>Public Function AddYears ( value As Integer ) As DateTime</b></td><td>Returns a new DateTime that adds the specified number of years to the value of this instance.</td></tr><tr><td><b>Public Shared Function Compare ( t1 As DateTime, t2 As DateTime ) As Integer</b></td><td>Compares two instances of DateTime and returns an integer that indicates whether the first instance is earlier than, the same as, or later than the second instance.</td></tr><tr><td><b>Public Function CompareTo ( value As DateTime ) As Integer</b></td><td>Compares the value of this instance to a specified DateTime value and returns an integer that indicates whether this instance is earlier than, the same as, or later than the specified DateTime value.</td></tr><tr><td><b>Public Function Equals ( value As DateTime ) As Boolean</b></td><td>Returns a value indicating whether the value of this instance is equal to the value of the specified DateTime instance.</td></tr><tr><td><b>Public Shared Function Equals ( t1 As DateTime, t2 As DateTime ) As Boolean</b></td><td>Returns a value indicating whether two DateTime instances have the same date and time value.</td></tr><tr><td><b>Public Overrides Function ToString As String</b></td><td>Converts the value of the current DateTime object to its equivalent string representation.</td></tr></tbody></table><br/><br/><h2>DateTime Methods Example</h2>The following snippet shows some of the DateTime Methods used in the DateTime Structure. <pre class="brush: vb"><br />Public Sub DateTimeMethods()<br />        Debug.Print("Today is: " & Now)<br />        Debug.Print("Adds 1 Day to Current date: " & Now.AddDays(1))<br />        Debug.Print("Adds 1 Hour to Current Time: " & Now.AddHours(1))<br />        Debug.Print("Adds 1 Minute to Current Time: " & Now.AddMinutes(1))<br />        Debug.Print("Adds 1 Second to Current Time: " & Now.AddSeconds(1))<br />End Sub<br /></pre><br/><h2>Output</h2>The above snippet should print the following. <pre class="output"><br />Today is: 9/6/2014 11:54:31 AM<br />Adds 1 Day to Current date: 9/7/2014 11:54:31 AM<br />Adds 1 Hour to Current Time: 9/6/2014 12:54:31 PM<br />Adds 1 Minute to Current Time: 9/6/2014 11:55:31 AM<br />Adds 1 Second to Current Time: 9/6/2014 11:54:32 AM<br /></pre><br/><h2>Formatting Date and Time</h2><a href="#" title="Formatting Date and Time Tutorial">See formatting Date and Time tutorial here...</a>

date vb.net dotNet VB.NET Working with Date and Time VB.Net

VB.NET's DateTime structure represents an instant in time which is commonly expressed as a particular date and time of the day. It'...

Commonly Used VBA Statements for Excel VBA Programming

1:23 AM OmelSoft 0

The following table shows a list of commonly used VBA statements that might be useful when creating macros for your Excel projects. To know more information about each statement, consult Excel’s Help system by pressing "F1". <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgcU5Jj1uAwQCyKRQR_VIBvHhP81iLJW8UoLzrNim7XMmJkgKQn_NkWlydLUsEBfkcSmYAEAe_iJF7qb8pqsjbz8C4ZXzRoScOOmFGH6du2zb1-CY4bWjfh036NqYF93YD6Pc51m4laZoR/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="560" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgcU5Jj1uAwQCyKRQR_VIBvHhP81iLJW8UoLzrNim7XMmJkgKQn_NkWlydLUsEBfkcSmYAEAe_iJF7qb8pqsjbz8C4ZXzRoScOOmFGH6du2zb1-CY4bWjfh036NqYF93YD6Pc51m4laZoR/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_1.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidgc0laEJwk7sh0tJTVEyUx57Lveg0gQPXSsC9P0jCNcaj1SgMLRedv2urQA_peAXPJErUftJdIoDwKLHrzDqieIc1LRSZJo67GKqF9zHe49l0-DDP4MARxtZWIEWD_LZe149NTbEd6QBA/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidgc0laEJwk7sh0tJTVEyUx57Lveg0gQPXSsC9P0jCNcaj1SgMLRedv2urQA_peAXPJErUftJdIoDwKLHrzDqieIc1LRSZJo67GKqF9zHe49l0-DDP4MARxtZWIEWD_LZe149NTbEd6QBA/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_2.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivSsKInS9ce2dAl4zh_g0parfHvPpgvKr7iMnxtTu-dNmM34NY8lydOFQ2lSG6CK0Rj6-8iNlkGXaZDfbbO_TSqnEG3VsKlYmZ6ywhJjfeeIB1EpSzNM-epCtsd80eU0Ya7sIr8USTS86V/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivSsKInS9ce2dAl4zh_g0parfHvPpgvKr7iMnxtTu-dNmM34NY8lydOFQ2lSG6CK0Rj6-8iNlkGXaZDfbbO_TSqnEG3VsKlYmZ6ywhJjfeeIB1EpSzNM-epCtsd80eU0Ya7sIr8USTS86V/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_3.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNThxGTWvWN_SvGTKZl684rXEdrBfrFQu-UlipSdTBxF6Sm1r1R6YnfWdEi5adq4jhyPcZp3oU4Pcc6kGA-vdw32StpeEAhCBDP1PGEWvQMuqwvAz2jEkZuaRwyfKEIWv9MO-1lQXiOPr6/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNThxGTWvWN_SvGTKZl684rXEdrBfrFQu-UlipSdTBxF6Sm1r1R6YnfWdEi5adq4jhyPcZp3oU4Pcc6kGA-vdw32StpeEAhCBDP1PGEWvQMuqwvAz2jEkZuaRwyfKEIWv9MO-1lQXiOPr6/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_4.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg3ckyL7AKqyMoljJbxR-WlDtT346iMX8Msx7EDpmpEvB_OBnTNnYEmhL5newulUkW2mkRLEDPUbJ-STcMEwNFntvvrAhcNS_OnDMY1rv7tsa5Q8VUNTfVkniDMbRLbcIN6g4PFzSDXMwj/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="409" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg3ckyL7AKqyMoljJbxR-WlDtT346iMX8Msx7EDpmpEvB_OBnTNnYEmhL5newulUkW2mkRLEDPUbJ-STcMEwNFntvvrAhcNS_OnDMY1rv7tsa5Q8VUNTfVkniDMbRLbcIN6g4PFzSDXMwj/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_5.png" width="640" /></a></div>
<br />
<br />
<br />

excel microsoft excel VBA Commonly Used VBA Statements for Excel VBA Programming

The following table shows a list of commonly used VBA statements that might be useful when creating macros for your Excel projects. To know ...

Remove and Replace All Characters in Microsoft Excel using VBA

11:30 PM OmelSoft 0

excel microsoft excel VBA Remove and Replace All Characters in Microsoft Excel using VBA

The following FindandReplace() function allows you to replace or remove specific text and characters you want in Microsoft Excel. Just copy...

How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

1:59 AM OmelSoft 0

<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" width="200" /></a></div>
The following function below allows you to get the number of visible rows from a filtered sets of rows in Excel using VBA. The function takes two arguments which is the Column and StartRow. Calling the FilterCount() function returns the number of visible rows. Also added an error handler which process the error description to determine if there's a visible row.<br />
<br />
<b>Parameters:</b><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><b><i>Column</i></b>:<span class="Apple-tab-span" style="white-space: pre;"> </span>The column of the data to be filtered. If you have multiple columns being filtered you can just set the first Column or any column in the dataset.  <span class="Apple-tab-span" style="white-space: pre;"> </span><br />
        <b><i>StartRow</i></b>:<span class="Apple-tab-span" style="white-space: pre;"> </span>Start row of the data to be filtered.  <br />
<br />
<pre class="brush: vb">Function FilterCount(ByVal Column As String, ByVal StartRow As Long) As Long

On Error GoTo errHandler
  
    FilterCount = Application.WorksheetFunction.CountA(ActiveSheet.Range(Column & StartRow, Cells(ActiveSheet.UsedRange.Rows.Count, Range(Column & StartRow).Column)).SpecialCells(xlCellTypeVisible))

'Debug.Print FilterCount
  
Exit Function
errHandler:
    If Err.DESCRIPTION = "No cells were found." Then
        'Set count to 0
        FilterCount = 0
    End If
  
    'Debug.Print FilterCount
  
End Function</pre>
<br />
<b>Usage: </b><br />
<br />
<pre class="brush: vb">Sub Count()
    Debug.Print FilterCount "A", 20
End Sub</pre>
<br />
<b>Where: </b><span class="Apple-tab-span" style="white-space: pre;"> </span><br />
          "A" - is the column or first column in the column lists. <span class="Apple-tab-span" style="white-space: pre;"> </span><br />
           20  - is the start row of the data set.  <br />
<div>
</div>
</div>

excel microsoft excel VBA How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

The following function below allows you to get the number of visible rows from a filtered sets of rows in Excel using VBA. The function t...

How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

12:28 AM OmelSoft 0

<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" width="320" /></a></div>
<div style="text-align: justify;">
You might be working on a project where you need to filter sets of data and create a raw data of that filtered sets of data to a new sheet or range.<br />
<br />
By default, Excel copies hidden or filtered cells in addition to visible cells. If some cells, rows, or columns on your worksheet are not displayed, you have the option of copying all cells or only the visible cells. </div>
<br />
The following snippet allows you to automate the process in microseconds.<br />
<br />
<b><span style="color: #cc0000; font-size: large;">[</span><span style="background-color: #cc0000;"><span style="color: #f4cccc;">VBA</span></span><span style="color: #cc0000; font-size: large;">] </span></b><br />
<pre class="brush: vb">Public Function GetFilteredData()
    Dim rawWs As Worksheet            'RAW DATA WORKSHEET
    Dim tarWs As Worksheet            'TARGET WORKSHEET
    
    'Replace this with your actual Worksheets
    Set rawWs = Sheets("Raw Data")    
    Set tarWs = Sheets("Filtered Data Visualizations")
    
    Application.ScreenUpdating = False

'Clear old contents of the Target Worksheet
    tarWs.Range("A2:N" & Rows.Count).ClearContents
    
    '****************************************************
    ' Select Raw Data Sheet and
    ' Copy only the visible rows if filter is applied
    '
    rawWs.Select
    Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, Range("N2").Column)).SpecialCells(xlCellTypeVisible).Copy
    
    '****************************************************
    'Select the Target worksheet and
    'Paste the copied data
    '
    tarWs.Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Range("A2").Select
    Application.ScreenUpdating = True
End Function</pre>
<br />
<div style="text-align: center;">
<b>Output on a new sheet shown below.</b></div>
<div style="text-align: center;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" width="320" /></a></div>
<br />

excel microsoft excel VBA How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

You might be working on a project where you need to filter sets of data and create a raw data of that filtered sets of data to a new shee...

Pinoy Tutorials, Tips and Tricks

Working with Date and Time VB.Net

Commonly Used VBA Statements for Excel VBA Programming

Remove and Replace All Characters in Microsoft Excel using VBA

How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

Popular Posts

TAG CLOUD

Blog Archive