Pinoy Tutorials, Tips and Tricks

How to detect Scroll position of a webpage using jQuery's .scrollTop()

The .scrollTop() function in jQuery allows you to get the current vertical position of the scroll bar for the first element in the set of matched elements or set the vertical position of the scroll bar for every matched element. 
 
You can extract the scroll position of a certain page or element using the <a href="http://api.jquery.com/scrollTop/" rel="nofollow" target="_blank">jQuery's .scrollTop() method</a>. 
 
 
<pre class="brush: java">$(window).scroll(function (event) {
 var scroll = $(window).scrollTop();
 // Do something
});</pre>
 
You can also determined the percentage and alert the user that he has scrolled certain amount within the document. 
<pre class="brush: java">$(document).scroll(function(e){
 
 // get the scroll value and the document height
 var scrollVal = $(window).scrollTop();
 var docHeight = $(document).height();
 
 // calculate the percentage when the user scrolls down the page
 var percentage = (scrollVal / docHeight) * 100;
 
 if(percentage > 50) {
 // call the function called alertUser
 alertUser(percentage);
 }
 
 function alertUser(scrollPercentage) { 
 // alerts the user that he has scrolled certain amount within the document
 alert("You have scrolled down at " + scrollPercentage + " of this page");
 }
 
});</pre>

jQUERY How to detect Scroll position of a webpage using jQuery's .scrollTop()

VB.Net Print PDF Document

12:27 AM OmelSoft 1

Portable Document Format (PDF) is a file format used to present documents in a manner independent of application software, hardware, and operating systems. Each PDF file encapsulates a complete description of a fixed-layout flat document, including the text, fonts, graphics, and other information needed to display it. 
 
In some applications, web or desktop there are several options where you can convert or Print a PDF version of a form or document you're reading. So, the question is how is it done and is it possible to create your own PDF document from scratch? We'll you're still lucky as I am. 
 
In this tutorial you'll be able to learn on how can VB.Net Print PDF Documents. 
 
<h2>
Requirements</h2>
1.) CodePlex.com provides a freely downloadable version of the <a href="http://pdfsharp.codeplex.com/downloads/get/96263" rel="nofollow" target="_blank">PDFSharp-MigraDocFoundation-Assemblies</a>. 
2.) Extract the downloaded .zip file to your project folder. The file name if not updated is PDFsharp-MigraDocFoundation-Assemblies-1_31.zip. 
 
 
<h2>
Creating the Project</h2>
1.) Start you Microsoft Visual Basic 2010 Express. 
2.) Create a new Windows Application Project. 
3.) In the Solution Explorer Panel, Right Click on your Project's Name. Then choose Add Reference. 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmmMnzOnPFvb8SwsKVl5B1O9P3sH10ePE5K59gysKPX0OYfETOGCbs9QarI6yWw17cRFzIheaxL2aCMQQR6RLWFL12UpuF8ij4IiW6QGPpB-K-_p7Zr6kalewBf9PZ_RrAGsyOcY-mM_VJ/s1600/Vb.Net+Print+PDF+Document+-+Solution+Explorer.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmmMnzOnPFvb8SwsKVl5B1O9P3sH10ePE5K59gysKPX0OYfETOGCbs9QarI6yWw17cRFzIheaxL2aCMQQR6RLWFL12UpuF8ij4IiW6QGPpB-K-_p7Zr6kalewBf9PZ_RrAGsyOcY-mM_VJ/s1600/Vb.Net+Print+PDF+Document+-+Solution+Explorer.png" width="320" /></a></div>
 
4.) In the Add Reference dialog window, Click the Browse Tab. Locate the folder where you extracted the PDFSharp Assemblies. 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdpVRT32eMomr-l23_UZlxo-MNZITxL0ZEtTxv3yA57FnN0www_rrOwJQpu5RmZiZLTi3i6MhM-FC_cisOEczIkQz6tG3EM3ZeWJygrGk8a6DQj7UOFMKIeuO1rLxL_II1m6PDfYcuTBGq/s1600/VB.Net+Print+PDF+Document+-+Add+Reference.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="269" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdpVRT32eMomr-l23_UZlxo-MNZITxL0ZEtTxv3yA57FnN0www_rrOwJQpu5RmZiZLTi3i6MhM-FC_cisOEczIkQz6tG3EM3ZeWJygrGk8a6DQj7UOFMKIeuO1rLxL_II1m6PDfYcuTBGq/s1600/VB.Net+Print+PDF+Document+-+Add+Reference.png" width="320" /></a></div>
5.)Select All of the .dll files then Click OK. 
 
Once the done, Click the small arrow to expand the references in Solution Explorer Panel and it will most likely look as the image below. 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPY-3RgBdlt2WSy4HAcGGrSYoZB8ypEHG_xepOkTUHlbAAD3UUgOhkgvcYsy9YGyQXxhB8FSd3l3rjiYUxTRO8h19IZVZ1XgQc1o7mLW0aYZo1YPT5vsilGL4XK34cXVConGZtZMI8B50H/s1600/Vb.Net+Print+PDF+Document+-+References+Added.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPY-3RgBdlt2WSy4HAcGGrSYoZB8ypEHG_xepOkTUHlbAAD3UUgOhkgvcYsy9YGyQXxhB8FSd3l3rjiYUxTRO8h19IZVZ1XgQc1o7mLW0aYZo1YPT5vsilGL4XK34cXVConGZtZMI8B50H/s1600/Vb.Net+Print+PDF+Document+-+References+Added.png" width="276" /></a></div>
 
 
<h2>
Design Mode</h2>
Add a Button Control on the form. 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4EWSY0U5nQqsnGium6y_2hjnPNoLO0L_u1a_-EIPPVuXssMGcM7EsK044VMGPY-0lQk6yGCfZQ2m2FpRpGPAE544j3vKE_5T9Y8wH9zqwfzLgykG9037x6dlESbbtc-7DR5pZ9RLjjvsU/s1600/Vb.Net+Print+PDF+Document+-+Add+Button.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4EWSY0U5nQqsnGium6y_2hjnPNoLO0L_u1a_-EIPPVuXssMGcM7EsK044VMGPY-0lQk6yGCfZQ2m2FpRpGPAE544j3vKE_5T9Y8wH9zqwfzLgykG9037x6dlESbbtc-7DR5pZ9RLjjvsU/s1600/Vb.Net+Print+PDF+Document+-+Add+Button.png" /></a></div>
<h2>
Coding your Project</h2>
Double Click the button control then Copy and paste the following codes in your Code Editor. 
<pre class="brush: vb">Imports PdfSharp
Imports PdfSharp.Drawing
Imports PdfSharp.Pdf

Public Class Form1
 Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 Dim pdf As PdfDocument = New PdfDocument
 pdf.Info.Title = "My First PDF Document"
 Dim pdfPage As PdfPage = pdf.AddPage
 Dim graph As XGraphics = XGraphics.FromPdfPage(pdfPage)
 Dim font As XFont = New XFont("Verdana", 20, XFontStyle.Bold)
 graph.DrawString("You've just created your first PDF document.", font, XBrushes.Black, _
 New XRect(0, 0, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.Center)
 Dim pdfFileName as String = "C:\MyFirstPdfDocument.pdf"
 pdf.Save(pdfFileName)
 Process.Start(pdfFileName)
 End Sub
End Class</pre>
 
<h2>
Explaining each Line of Codes</h2>
Import the required namespaces. 
<pre class="brush: vb"> Imports PdfSharp
 Imports PdfSharp.Drawing
 Imports PdfSharp.Pdf</pre>
Create a PDF document object. 
<pre class="brush: vb"> Dim pdf As PdfDocument = New PdfDocument</pre>
Create a new page. 
<pre class="brush: vb"> Dim pdfPage As PdfPage = pdf.AddPage</pre>
Set the title of the PDF document. 
<pre class="brush: vb"> pdf.Info.Title = "My First PDF Document"</pre>
Create a Graphics Object. 
<pre class="brush: vb"> Dim graph As XGraphics = XGraphics.FromPdfPage(pdfPage)</pre>
Create and Set a font style from XFont. 
<pre class="brush: vb"> Dim font As XFont = New XFont("Tahoma", 24, XFontStyle.Bold)</pre>
Adding content to PDF document and Add it to Center. 
<pre class="brush: vb"> graph.DrawString("Congratulations! You've just created your first PDF document.", font, XBrushes.Black, _
 New XRect(0, 0, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.Center)</pre>
Save the PDF Document at your most preferred location. 
<pre class="brush: vb"> Dim pdfFileName as String = "C:\MyFirstPdfDocument.pdf"
 pdf.Save(pdfFileName)</pre>
Launch the newly create PDF document. 
<pre class="brush: vb"> Process.Start(pdfFileName)</pre>
 
<h2>
PDF Output</h2>
When you click the button, it will create the pdf page and open the pdf file. As you can see below. 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBr6m_Ksqs0_X4V4g3Q8rN2Vttn61IEb6Fs-AcT5U7ZRg66VKmghsXbCj5IgBM3w7C3enfbxZaeJOaS7fXtwdmkwZVxjzM0z_xaLrCXU8_9FxRQyKHvFMELMTkrZD9nYrvSeg1dkKi6BH8/s1600/Vb.Net+Print+PDF+Document+-+My+First+PDF+Document.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBr6m_Ksqs0_X4V4g3Q8rN2Vttn61IEb6Fs-AcT5U7ZRg66VKmghsXbCj5IgBM3w7C3enfbxZaeJOaS7fXtwdmkwZVxjzM0z_xaLrCXU8_9FxRQyKHvFMELMTkrZD9nYrvSeg1dkKi6BH8/s320/Vb.Net+Print+PDF+Document+-+My+First+PDF+Document.png" /></a></div>
 
Now, you've just learned how to create your first pdf document. Download the <a href="https://www.dropbox.com/s/kec5b338bid6p5z/Print%20a%20PDF%20Document.rar?dl=0" rel="nofollow" target="_blank">VB.Net Print PDF Project</a>.

Portable Document Format (PDF) is a file format used to present documents in a manner independent of application software, hardware, and operating systems. Each PDF file encapsulates a complete description of a fixed-layout flat document, including t…

Formatting Date and Time VB.NET

10:07 PM OmelSoft 1

The Date literal must be enclosed within number signs (#date#) and specify the date value in the format M/d/yyyy like #09/06/2014#. Else, the way how your code is interpreted may vary depending on the locale in which your application is deployed. 
 
With this requirement the meaning of your code should never change whether if it has a different date and time format settings. For example, you hard-coded a Date literal of #9/6/2014# which means September 6, 2014. That will compile 9/6/2014 as you want mm/dd/yyyy. However, if you deploy application in several locales using dd/mm/yyyy date format, your hard-coded literal would compile to June 9, 2014. For other locales using yyyy/mm/dd, the literal would be invalid causing compilation error. 
 
To convert a Date literal to the format of your locale or to a custom format, use the Format function of String class, specifying either a predefined or user-defined date format. 
 
<h2>
Predefined Date/Time Formats</h2>
The following table identifies the predefined date and time format names. These may be used by name as the style argument for the Format function: 
<table cellpadding="0" cellspacing="0"> <tbody>
<tr> <th>Format Name </th> <th>Description </th> </tr>
<tr> <td>General Date, or G </td> <td>Displays a date and/or time. For example, 3/12/2008 11:07:31 AM. Date display is determined by your application's current culture value. </td> </tr>
<tr> <td>Long Date, Medium Date, or D </td> <td>Displays a date according to your current culture's long date format. For example, Wednesday, March 12, 2008. </td> </tr>
<tr> <td>Short Date, or d </td> <td>Displays a date using your current culture's short date format. For example, 3/12/2008. The d character displays the day in a user-defined date format. </td> </tr>
<tr> <td>Long Time, Medium Time, or T </td> <td>Displays a time using your current culture's long time format; typically includes hours, minutes, seconds. For example, 11:07:31 AM. </td> </tr>
<tr> <td>Short Time or t </td> <td>Displays a time using your current culture's short time format. For example, 11:07 AM. The t character displays AM or PM values for locales that use a 12-hour clock in a user-defined time format. </td> </tr>
<tr> <td>f </td> <td>Displays the long date and short time according to your current culture's format. For example, Wednesday, March 12, 2008 11:07 AM. </td> </tr>
<tr> <td>F </td> <td>Displays the long date and long time according to your current culture's format. For example, Wednesday, March 12, 2008 11:07:31 AM. </td> </tr>
<tr> <td>g </td> <td>Displays the short date and short time according to your current culture's format. For example, 3/12/2008 11:07 AM. </td> </tr>
<tr> <td>M, m </td> <td>Displays the month and the day of a date. For example, March 12. The M character displays the month in a user-defined date format. The m character displays the minutes in a user-defined time format. </td> </tr>
<tr> <td>R, r </td> <td>Formats the date according to the <a href="http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo.rfc1123pattern.aspx"> RFC1123Pattern</a> property. For example, Wed, 12 Mar 2008 11:07:31 GMT. The formatted date does not adjust the value of the date and time. You must adjust the Date/Time value to GMT before calling the Format function. </td> </tr>
<tr> <td>s </td> <td>Formats the date and time as a sortable index. For example, 2008-03-12T11:07:31. The s character displays the seconds in a user-defined time format. </td> </tr>
<tr> <td>u </td> <td>Formats the date and time as a GMT sortable index. For example, 2008-03-12 11:07:31Z. </td> </tr>
<tr> <td>U </td> <td>Formats the date and time with the long date and long time as GMT. For example, Wednesday, March 12, 2008 6:07:31 PM. </td> </tr>
<tr> <td>Y, y </td> <td>Formats the date as the year and month. For example, March, 2008. The Y and y characters display the year in a user-defined date format. </td> </tr>
</tbody> </table>
 
<h2>
Pre-Defined Format Example</h2>
The following example demonstrates how this can be achived. 
<pre class="brush: vb">
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim SampleDateTime As Date = #9/6/2014 1:14:23 PM#
        Debug.Print("Formatting Date and Time Examples:")
        Debug.Print("Using Format 'D:' " & SampleDateTime.ToString("D"))
        Debug.Print("Using Format 'd:' " & SampleDateTime.ToString("d"))
        Debug.Print("Using Format 'T:' " & SampleDateTime.ToString("T"))
        Debug.Print("Using Format 't:' " & SampleDateTime.ToString("t"))
        Debug.Print("Using Format 'F:' " & SampleDateTime.ToString("F"))
        Debug.Print("Using Format 'f:' " & SampleDateTime.ToString("f"))
        Debug.Print("Using Format 'G:' " & SampleDateTime.ToString("G"))
        Debug.Print("Using Format 'g:' " & SampleDateTime.ToString("g"))
        Debug.Print("Using Format 'M:' " & SampleDateTime.ToString("M"))
        Debug.Print("Using Format 'm:' " & SampleDateTime.ToString("m"))
        Debug.Print("Using Format 'R:' " & SampleDateTime.ToString("R"))
        Debug.Print("Using Format 'r:' " & SampleDateTime.ToString("r"))
        Debug.Print("Using Format 'Y:' " & SampleDateTime.ToString("Y"))
        Debug.Print("Using Format 'y:' " & SampleDateTime.ToString("y"))

End Sub
End Class</pre>
 
<h2>
Pre-Defined Output</h2>
When the project is compiled it will print the following results. 
<pre class="output">
Formatting Date and Time Examples:
Using Format 'D:' Saturday, September 06, 2014
Using Format 'd:' 9/6/2014
Using Format 'T:' 1:14:23 PM
Using Format 't:' 1:14 PM
Using Format 'F:' Saturday, September 06, 2014 1:14:23 PM
Using Format 'f:' Saturday, September 06, 2014 1:14 PM
Using Format 'G:' 9/6/2014 1:14:23 PM
Using Format 'g:' 9/6/2014 1:14 PM
Using Format 'M:' September 06
Using Format 'm:' September 06
Using Format 'R:' Sat, 06 Sep 2014 13:14:23 GMT
Using Format 'r:' Sat, 06 Sep 2014 13:14:23 GMT
Using Format 'Y:' September, 2014
Using Format 'y:' September, 2014</pre>
 
<h2>
User-Defined Date and Time Formats</h2>
The following table shows the characters you can use to create user-defined date/time formats. Unlike in earlier versions of Visual Basic, these format characters are case-sensitive. 
<table cellpadding="0" cellspacing="0"> <tbody>
<tr><th>Character </th> <th>Description </th> </tr>
<tr> <td>(:) </td> <td>Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character that is used as the time separator in formatted output is determined by your application's current culture value. </td> </tr>
<tr> <td>(/) </td> <td>Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character that is used as the date separator in formatted output is determined by your application's current culture. </td> </tr>
<tr> <td>(%) </td> <td>Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a user-defined format. See what follows for additional details. </td> </tr>
<tr> <td>d </td> <td>Displays the day as a number without a leading zero (for example, 1). Use %d if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>dd </td> <td>Displays the day as a number with a leading zero (for example, 01). </td> </tr>
<tr> <td>ddd </td> <td>Displays the day as an abbreviation (for example, Sun). </td> </tr>
<tr> <td>dddd </td> <td>Displays the day as a full name (for example, Sunday). </td> </tr>
<tr> <td>M </td> <td>Displays the month as a number without a leading zero (for example, January is represented as 1). Use %M if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>MM </td> <td>Displays the month as a number with a leading zero (for example, 01/12/01). </td> </tr>
<tr> <td>MMM </td> <td>Displays the month as an abbreviation (for example, Jan). </td> </tr>
<tr> <td>MMMM </td> <td>Displays the month as a full month name (for example, January). </td> </tr>
<tr> <td>gg </td> <td>Displays the period/era string (for example, A.D.). </td> </tr>
<tr> <td>h </td> <td>Displays the hour as a number without leading zeros using the 12-hour clock (for example, 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>hh </td> <td>Displays the hour as a number with leading zeros using the 12-hour clock (for example, 01:15:15 PM). </td> </tr>
<tr> <td>H </td> <td>Displays the hour as a number without leading zeros using the 24-hour clock (for example, 1:15:15). Use %H if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>HH </td> <td>Displays the hour as a number with leading zeros using the 24-hour clock (for example, 01:15:15). </td> </tr>
<tr> <td>m </td> <td>Displays the minute as a number without leading zeros (for example, 12:1:15). Use %m if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>mm </td> <td>Displays the minute as a number with leading zeros (for example, 12:01:15). </td> </tr>
<tr> <td>s </td> <td>Displays the second as a number without leading zeros (for example, 12:15:5). Use %s if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>ss </td> <td>Displays the second as a number with leading zeros (for example, 12:15:05). </td> </tr>
<tr> <td>f </td> <td>Displays fractions of seconds. For example ff displays hundredths of seconds, whereas ffff displays ten-thousandths of seconds. You may use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>t </td> <td>Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>tt </td> <td>For locales that use a 12-hour clock, displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. For locales that use a 24-hour clock, displays nothing. </td> </tr>
<tr> <td>y </td> <td>Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>yy </td> <td>Displays the year in two-digit numeric format with a leading zero, if applicable. </td> </tr>
<tr> <td>yyy </td> <td>Displays the year in four-digit numeric format. </td> </tr>
<tr> <td>yyyy </td> <td>Displays the year in four-digit numeric format. </td> </tr>
<tr> <td>z </td> <td>Displays the timezone offset without a leading zero (for example, -8). Use %z if this is the only character in your user-defined numeric format. </td> </tr>
<tr> <td>zz </td> <td>Displays the timezone offset with a leading zero (for example, -08) </td> </tr>
<tr> <td>zzz </td> <td>Displays the full timezone offset (for example, -08:00) </td> </tr>
</tbody> </table>
 
<h2>
User Defined Date and Time Formatting Examples</h2>
Copy and Paste the following codes into your project then compile. 
<pre class="brush: vb">
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim SampleDateTime As Date = #9/6/2014 1:14:23 PM#
        Debug.Print("User-Defined Date and Time Formatting Examples:")
        Debug.Print("Prints '9/6/14': " & Format(SampleDateTime, "M/d/y"))
        Debug.Print("Prints '6-Sep': " & Format(SampleDateTime, "d-MMM"))
        Debug.Print("Prints '6-Sep-14': " & Format(SampleDateTime, "d-MMM-yy"))
        Debug.Print("Prints '6 September': " & Format(SampleDateTime, "d MMMM"))
        Debug.Print("Prints 'September 14': " & Format(SampleDateTime, "MMMM yy"))
        Debug.Print("Prints '2014-09-06': " & Format(SampleDateTime, "yyyy-MM-dd"))
        Debug.Print("Prints '01:14 PM': " & Format(SampleDateTime, "hh:mm tt"))
        Debug.Print("Prints '1:14:23 P': " & Format(SampleDateTime, "h:mm:ss t"))
        Debug.Print("Prints '13:14': " & Format(SampleDateTime, "H:mm"))
        Debug.Print("Prints '13:14:23': " & Format(SampleDateTime, "H:mm:ss"))
        Debug.Print("Prints '9/6/2014 13:14': " & Format(SampleDateTime, "M/d/yyyy H:mm"))

End Sub
End Class</pre>
 
<h2>
User-Defined Output</h2>
When the project is compile it will print the following results. 
<pre class="output">
User-Defined Date and Time Formatting Examples:
Prints '9/6/14': 9/6/14
Prints '6-Sep': 6-Sep
Prints '6-Sep-14': 6-Sep-14
Prints '6 September': 6 September
Prints 'September 14': September 14
Prints '2014-09-06': 2014-09-06
Prints '01:14 PM': 01:14 PM
Prints '1:14:23 P': 1:14:23 P
Prints '13:14': 13:14
Prints '13:14:23': 13:14:23
Prints '9/6/2014 13:14': 9/6/2014 13:14</pre>

date vb.net dotNet VB.NET Formatting Date and Time VB.NET

The Date literal must be enclosed within number signs (#date#) and specify the date value in the format M/d/yyyy like #09/06/2014#. Else, the way how your code is interpreted may vary depending on the locale in which your application is deployed. Wit…

Working with Date and Time VB.Net

7:53 PM OmelSoft 0

VB.NET's DateTime 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 properties and methods and example usage of the Date and Time in VB.Net. 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. <h2>DateTime Properties</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>Date</td><td>Gets the date component of this instance.</td></tr><tr><td>Day</td><td>Gets the day of the month represented by this instance.</td></tr><tr><td>DayOfWeek</td><td>Gets the day of the week represented by this instance.</td></tr><tr><td>DayOfYear</td><td>Gets the day of the year represented by this instance.</td></tr><tr><td>Hour</td><td>Gets the hour component of the date represented by this instance.</td></tr><tr><td>Kind</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>Millisecond</td><td>Gets the milliseconds component of the date represented by this instance.</td></tr><tr><td>Minute</td><td>Gets the minute component of the date represented by this instance.</td></tr><tr><td>Month</td><td>Gets the month component of the date represented by this instance.</td></tr><tr><td>Now</td><td>Gets a DateTime object that is set to the current date and time on this computer, expressed as the local time.</td></tr><tr><td>Second</td><td>Gets the seconds component of the date represented by this instance.</td></tr><tr><td>Ticks</td><td>Gets the number of ticks that represent the date and time of this instance.</td></tr><tr><td>TimeOfDay</td><td>Gets the time of day for this instance.</td></tr><tr><td>Today</td><td>Gets the current date.</td></tr><tr><td>UtcNow</td><td>Gets a DateTime object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC).</td></tr><tr><td>Year</td><td>Gets the year component of the date represented by this instance.</td></tr></tbody></table> <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>Public Function Add ( value As TimeSpan ) As DateTime</td><td>Returns a new DateTime that adds the value of the specified TimeSpan to the value of this instance.</td></tr><tr><td>Public Function AddDays ( value As Double ) As DateTime</td><td>Returns a new DateTime that adds the specified number of days to the value of this instance.</td></tr><tr><td>Public Function AddHours ( value As Double ) As DateTime</td><td>Returns a new DateTime that adds the specified number of hours to the value of this instance.</td></tr><tr><td>Public Function AddMinutes ( value As Double ) As DateTime</td><td>Returns a new DateTime that adds the specified number of minutes to the value of this instance.</td></tr><tr><td>Public Function AddMonths ( months As Integer ) As DateTime</td><td>Returns a new DateTime that adds the specified number of months to the value of this instance.</td></tr><tr><td>Public Function AddSeconds ( value As Double ) As DateTime</td><td>Returns a new DateTime that adds the specified number of seconds to the value of this instance.</td></tr><tr><td>Public Function AddYears ( value As Integer ) As DateTime</td><td>Returns a new DateTime that adds the specified number of years to the value of this instance.</td></tr><tr><td>Public Shared Function Compare ( t1 As DateTime, t2 As DateTime ) As Integer</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>Public Function CompareTo ( value As DateTime ) As Integer</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>Public Function Equals ( value As DateTime ) As Boolean</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>Public Shared Function Equals ( t1 As DateTime, t2 As DateTime ) As Boolean</td><td>Returns a value indicating whether two DateTime instances have the same date and time value.</td></tr><tr><td>Public Overrides Function ToString As String</td><td>Converts the value of the current DateTime object to its equivalent string representation.</td></tr></tbody></table> <h2>DateTime Methods Example</h2>The following snippet shows some of the DateTime Methods used in the DateTime Structure. <pre class="brush: vb"> Public Sub DateTimeMethods() Debug.Print("Today is: " & Now) Debug.Print("Adds 1 Day to Current date: " & Now.AddDays(1)) Debug.Print("Adds 1 Hour to Current Time: " & Now.AddHours(1)) Debug.Print("Adds 1 Minute to Current Time: " & Now.AddMinutes(1)) Debug.Print("Adds 1 Second to Current Time: " & Now.AddSeconds(1)) End Sub </pre> <h2>Output</h2>The above snippet should print the following. <pre class="output"> Today is: 9/6/2014 11:54:31 AM Adds 1 Day to Current date: 9/7/2014 11:54:31 AM Adds 1 Hour to Current Time: 9/6/2014 12:54:31 PM Adds 1 Minute to Current Time: 9/6/2014 11:55:31 AM Adds 1 Second to Current Time: 9/6/2014 11:54:32 AM </pre> <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's a part of your daily life while creating your applications which makes it easy for you to manipulate system's date an…

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". 
 
<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>

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

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 the code below on your VBA code class or module. Sub FindandReplace(FindWhat as String, ReplaceWith as String…

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. 
 
Parameters: 
 Column: 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. 
        StartRow: Start row of the data to be filtered. 
 
<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>
 
Usage:  
 
<pre class="brush: vb">Sub Count()
 Debug.Print FilterCount "A", 20
End Sub</pre>
 
Where: 
          "A" - is the column or first column in the column lists. 
           20  - is the start row of the data set. 
<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 takes two arguments which is the Column and StartRow. Calling the FilterCount() function returns the number of vi…

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. 
 
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>
 
The following snippet allows you to automate the process in microseconds. 
 
[VBA] 
<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>
 
<div style="text-align: center;">
Output on a new sheet shown below.</div>
<div style="text-align: center;">
 </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>

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 sheet or range. By default, Excel copies hidden or filtered cells in addition to visible cells. If some cells, rows,…

How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

12:47 AM OmelSoft 0

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA. [VBA] <pre class="brush: vb">Dim FilteredRows as Variant Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean) Dim Rng As Range, rngF As Range, rngVal As Range 'Ranges Dim val As Variant 'Range Value Dim i As Integer 'Counter Dim lRow as long 'Last Row Application.ScreenUpdating = False Sheets("Raw Data").Select lRow = WorksheetFunction.CountA(Range("A:A")) 'Set the range of all visible cells of the filtered data Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _ Range("A2").Column)).SpecialCells(xlCellTypeVisible) For Each Rng In Range("$A2:$A$" & lRow) If Not Intersect(Rng, rngF) Is Nothing Then If rngVal Is Nothing Then Set rngVal = Rng Else Set rngVal = Union(rngVal, Rng) End If If rngVal.Cells.Count = lRow Then Exit For End If Next Rng 'Resize array variable ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant For Each val In rngVal If RowPrefixed = True Then FilteredRows(i) = val.Address Else FilteredRows(i) = Split(val.Address, "$")(2) End If Debug.Print val.Address & " - " & Split(val.Address, "$")(2) i = i + 1 Next val Debug.Print rngVal.Address Applicaiton.ScreenUpdating = True End Function </pre> To use the above function, you can assigned the following macro to a button or shape. <pre class="brush: vb">Sub SetFilter() Call GetFilteredRows(True) End Sub </pre> And you can see the output in the Immediate window as shown below. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" width="400" /></a></div>

excel microsoft excel VBA How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.[VBA]Dim FilteredRows as VariantPublic Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean) Dim Rng As Range, rngF…

How to Suppress Warning and Alerts in Excel using VBA

7:55 PM OmelSoft 0

In Microsoft Excel, you can suppress the message warning and alerts especially when you try to create a copy of a worksheet from a workbook containing macros to a new workbook which you wanted to save it as .xls or .xlsx extensions. By that, when you save the document it will prompt you to whether save the work as macro-enabled or macro-free workbook. 
<input name="IL_IN_TAG" type="hidden" value="1" /> 
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" width="320" /></a></div>
You can do that by setting DisplayAlerts to False with VBA code. 
 
 
<pre class="brush: vb">Application.DisplayAlerts = False

'DO SOME TASKS HERE

Application.DisplayAlerts = True</pre>

excel microsoft excel VBA How to Suppress Warning and Alerts in Excel using VBA

How to Create a Custom Checkbox using only the Cells in Microsoft Excel

11:02 PM OmelSoft 0

<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" width="320" /></a></div><div style="text-align: justify;">You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose their option. </div> <div style="text-align: justify;">We'll, if you want to have another look aside from the default check box control. You can still create and customize your check box controls with a little VBA code to get it done.</div> <div style="text-align: justify;">In this example, I have here choices for the reason of a leave application form. I make use of the Microsoft Excel's Cell as a check box. It's just a matter of resizing the rows and columns to make the size of the cell equal. </div> 1.) Now, let's create first the list of choices, as you can see below I've resized the cells and add borders on the cells which we'll set as a check-cell box control. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" width="320" /></a></div> 2.) Right Click on the Worksheet, then Click "View Code". 3.) Then Copy and Paste the following codes below into the code editor window. The following code will set the Target Range of when the Check-Cell box will be triggered. I set the columns to G & H or it's numeric equivalent 7 & 8. Then create case statement of what rows it will run. The columns and rows may vary depending on your actual template. So you can set it to match your template. <pre class="brush: vb">Private Sub Worksheet_SelectionChange(ByVal Target As Range)     With Target               'SET THE TARGET COLUMN TO COLUMNS 7 AND 8         If Target.Column >= 7 And Target.Column <= 8 Then             'SELECT CASE OF WHAT ROW WILL BE SELECTED             Select Case Target.Row                 Case 19 'SICK LEAVE                     CheckSelected 19, 7                 Case 21 'VACATION LEAVE                     CheckSelected 21, 7                 Case 23 'PERSONAL                     CheckSelected 23, 7                 Case 25 'EMERGENCY                     CheckSelected 25, 7                 Case 27 'APPOINTMENT                     CheckSelected 27, 7                 Case 29 'OTHERS                     CheckSelected 29, 7                 Case Default             End Select         End If     End With End Sub 'CHECK IF THE REASON IS CURRENTLY SELECTED Sub CheckSelected(Row As Integer, Col As Integer)     With Cells(Row, Col)         If .Value = "+" Then             .Value = ""             ResetSelectedColor Row, Col         Else             .Value = "+"             ApplySelectedColor Row, Col         End If     End With End Sub 'CHANGE THE BACKGROUND OF THE SELECTION Sub ApplySelectedColor(Row As Integer, Col As Integer)     With Cells(Row, Col).Interior         .Pattern = xlSolid         .PatternColorIndex = xlAutomatic         .ThemeColor = xlThemeColorAccent1         .TintAndShade = 0.599993896298105         .PatternTintAndShade = 0     End With End Sub 'REMOVE THE BACKGROUND OF THE SELECTION Sub ResetSelectedColor(Row As Integer, Col As Integer)     With Cells(Row, Col).Interior         .Pattern = xlNone         .TintAndShade = 0         .PatternTintAndShade = 0     End With End Sub </pre> 4.) Now, if you select the Cells it should change the background with the "+" value. You may also change the value to whatever you want. <div> </div>

excel microsoft excel How to Create a Custom Checkbox using only the Cells in Microsoft Excel

You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose their option. We'll, if you want to have another look aside from the default check box control. You can still cr…

Using Named Ranges in Microsoft Excel

12:19 AM OmelSoft 0

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" width="320" /></a></div> You can view the complete tutorial here <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">How to Use Named Ranges in Microsoft Excel </a>. When using Named Ranges there are also set of rules which you need to know like the scope where you can use the specified named range. If it's within only a single worksheet or the entire workbook. Check this out <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">Named Range Rules</a> and be totally aware of the do's and don'ts when using this functions. Managing Your Named Ranges There'll come a time when you want to edit or delete a Named Range. To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"> </div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" width="320" /></a></div> From here you can Edit and Delete your Named Ranges, or even create new ones. Remember, once you delete a name you cannot undo that action. Different uses for Named Ranges in Excel: 1) Formula References: Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas 2) Multiple Print Areas on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges. Note: Excel 2007 onward allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area. 3) Reduce worksheet clutter with Named Constants. Sounds complicated but it's not. A Named Constant is just a fancy name given to values you might use repeatedly in your formulas. For example, let's say you're planning to have a year-end inventory sale and wanted to give a 10% discount on selected products, you could give the value "10%" a name like "Discount" and then you could: <ul><li>Write your formulas like this =C1-(C1*Discount). This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.</li><li>Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.</li><li>Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you've got many of these key figures your spreadsheets can become crowded.</li></ul> To create a Named Constant See--<a href="http://excel-name-ranges.blogspot.com/2014/03/how-to-setup-named-constant-in-excel.html" target="_blank">How to Setup a Named Constant in Microsoft Excel</a>.

excel microsoft excel Using Named Ranges in Microsoft Excel

Scrape Website Data into Excel using VBA

2:14 PM OmelSoft 3

<div dir="ltr" style="text-align: left;" trbidi="on">I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(dot)renewableuk(dot)com. Please also read the privacy policy of the website before mining data. <input name="IL_IN_TAG" type="hidden" value="1" /> <div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="129" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" width="320" /></a> Goal: Get all data under all column headings which can be found on this <a href="http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm" rel="nofollow" target="_blank">website</a> i.e. Wind Project, Region, ..., Type of Project Requirements: <div style="text-align: justify;">You need to add a reference, Microsoft HTML Object Library on your VBA project.</div> Usage: <div style="text-align: justify;">You can call the ProcessWeb() sub directly by pressing F5 on the Microsoft Visual Basic Window.</div><div style="text-align: justify;">Or you can add a button on your excel worksheet then assign ProcessWeb() as the macro.</div> VBA CODE:  <pre class="brush: vb">Function ScrapeWebPage(ByVal URL As String) Dim HTMLDoc As New HTMLDocument Dim tmpDoc As New HTMLDocument Dim i As Integer, row As Integer Dim WS As Worksheet Set WS = Sheets("DATA") 'create new XMLHTTP Object Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP") XMLHttpRequest.Open "GET", URL, False XMLHttpRequest.send While XMLHttpRequest.readyState <> 4 DoEvents Wend With HTMLDoc.body 'Set HTML Document .innerHTML = XMLHttpRequest.responseText 'Get only Order List Tag of HTML Document Set orderedlists = .getElementsByTagName("ol") 'Reset the Document to the HTML of the second ordered list element 'where we only need to extract the data .innerHTML = orderedlists(1).innerHTML 'Now, we'll get the list items Set ListItems = .getElementsByTagName("li") 'Declare data variable for p values Dim iData As Integer row = lastRow + 1 'Let's process each data of the list items For Each li In ListItems 'Start's at 1st column iData = 1 With tmpDoc.body 'Set the temp doc .innerHTML = li.innerHTML 'There are about 10 columns, so there are 10 p's Set ps = .getElementsByTagName("p") For Each p In ps 'Put the value of p to each cells define below WS.Cells(row, iData).Value = p.innerText 'increment it by 1 which starts at column 1 iData = iData + 1 Next End With row = row + 1 Next End With End Function 'Get the total number pages we need to scrape Function totalPage() As Integer Dim HTMLDoc As New HTMLDocument Dim tmpDoc As New HTMLDocument Dim html As String Dim mask As String Dim URL As String URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm" Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP") XMLHttpRequest.Open "GET", URL, False XMLHttpRequest.send html = XMLHttpRequest.responseText With HTMLDoc.body .innerHTML = Mid(html, InStr(1, html, ""), 300) mask = Mid(.innerHTML, InStr(1, LCase(.innerHTML), "") - 2, 2) End With totalPage = mask End Function Function lastRow() As Long lastRow = Range("A65536").End(xlUp).row End Function Sub ProcessWeb() Dim URL As String Dim i As Integer Range("2:2", Selection.End(xlDown)).ClearContents Range("A2").Select Application.ScreenUpdating = False Application.Cursor = xlWait URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm/page/" For i = 1 To totalPage ScrapeWebPage URL & i Application.StatusBar = "Please wait while processing page " & i & " of " & totalPage & "..." Next i Application.ScreenUpdating = True Application.Cursor = xlDefault Application.StatusBar = "" MsgBox "Data Extraction is Done!" End Sub </pre></div> </div>

excel hot microsoft excel Scrape Website Data into Excel using VBA

I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(dot)renewableuk(dot)com. Please also read the privacy policy of the website before mining data. Goal:Get all data und…

How to use Do While Loop and a For Loop Counter in VB.Net

7:03 AM OmelSoft 1

<div dir="ltr" style="text-align: left;" trbidi="on">In this tutorial, we'll be working on a real example on how to use Do...While Loop and a For...Loop Counter in VB.Net. Example Description: 1. A customer will be asked to enter the prices of 4 items purchased using an input box (create a counter loop). 2. A tax rate of 7% will be a constant. 3. Subtotal, tax rate, and total will be displayed in labels. 4. An accumulator inside the loop will be used to calculate the subtotal. 5. A pre-test post-test do while loop shall be used to control the limit of entering price. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbCNCtrt5jKvMDvalzF1gVtusnCQ2ivYUKkSwDMZaSB_McMB8ezM8DpRqRa0HpSuQm0jqPMSv4yzJa_aWuDbKCYutH0LaiC0HAY0Cg9qQ5eVwfWKSn1YlRVxf8yBzLQ2EiZwCqGWNe0wR7/s1600/Do+While+For+Loop+Counter.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbCNCtrt5jKvMDvalzF1gVtusnCQ2ivYUKkSwDMZaSB_McMB8ezM8DpRqRa0HpSuQm0jqPMSv4yzJa_aWuDbKCYutH0LaiC0HAY0Cg9qQ5eVwfWKSn1YlRVxf8yBzLQ2EiZwCqGWNe0wR7/s1600/Do+While+For+Loop+Counter.gif" width="200" /></a></div> CODE: <pre class="brush: vb">Public Class Form1 Private Sub btnEnterPrices_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnterPrices.Click Dim Price As String Dim SubTotal As Double, Tax As Double, Total As Double Dim limit As String, newLimit As String Dim i As Integer, n As Integer, response As Integer 'Prompts the user the input the number of prices to be calculated limit = InputBox("Please enter the number of prices to calculate") 'Check user input for a valid number Do While IsNumeric(limit) = False limit = InputBox("Please enter the number of prices to calculate") Loop 'Start the counter up to the limit For i = 1 To limit 'Prompts for the price Price = InputBox("Please enter Price #" & i & ":", "Price # " & i) 'Check if the Price entered is valid If IsNumeric(Price) = False Then 'Display an error message until the user enters a valid price Do While IsNumeric(Price) = False MsgBox("Please input another a valid Price", vbExclamation, "Invalid Price") Price = InputBox("Please enter Price #" & i & ":", "Price # " & i) Loop Else 'Calculate sub-total SubTotal = SubTotal + Price End If If i = limit Then 'If the limit is reached, 'prompt the user whether to enter additional prices to calculate response = MsgBox("Do you want to enter additional Price?", vbYesNo) 'If the user clicked Yes If response = vbYes Then 'Prompt a new limit newLimit = InputBox("Please enter number of additional Price to Calculate") 'Validate user input Do While IsNumeric(newLimit) = False newLimit = InputBox("Please enter number of additional Price to Calculate") Loop 'Start a new counter for the new limit For n = 1 To newLimit 'Prompt for the Price Price = InputBox("Please enter Price #" & i + n & ":", "Price # " & i + n) 'Validate User Input If IsNumeric(Price) = False Then 'Display a message while the price is not a valid number Do While IsNumeric(Price) = False MsgBox("Please input another a valid Price", vbExclamation, "Invalid Price") Price = InputBox("Please enter Price #" & i + n & ":", "Price # " & i + n) Loop Else 'Calculate Subtotal SubTotal = SubTotal + Price End If Next n End If End If Next i 'Calculate Tax and the Total Price Tax = Format(SubTotal * 0.07, "###.##") Total = Format(SubTotal + Tax, "###.##") 'Display results inside labels lblSubTotal.Text = "$" & SubTotal lblTax.Text = "$" & Tax lblTotal.Text = "$" & Total End Sub Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click Me.Close() End Sub End Class </pre></div>

dotNet VB.NET How to use Do While Loop and a For Loop Counter in VB.Net

In this tutorial, we'll be working on a real example on how to use Do...While Loop and a For...Loop Counter in VB.Net.Example Description:1. A customer will be asked to enter the prices of 4 items purchased using an input box (create a counter loop).…

Send Email with Excel VBA via CDO through GMail

11:17 AM OmelSoft 1

<div dir="ltr" style="text-align: left;" trbidi="on"><div style="text-align: justify;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifrtiqOdy-PFBKU75R4cuFu-nZfvgu3aTnaPkpDVs_qgdlaTEd6QZLGuZ7O5gdW0Hd96oy6zDMlSKcKjuhUNpQ7Y2AYMQ8C4tS9BjesSbOdAD_hITUlexpuhtJWVpNU6hp7gfPlZ391xsR/s1600/Excel+Reports.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="Send Email with Excel VBA" border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifrtiqOdy-PFBKU75R4cuFu-nZfvgu3aTnaPkpDVs_qgdlaTEd6QZLGuZ7O5gdW0Hd96oy6zDMlSKcKjuhUNpQ7Y2AYMQ8C4tS9BjesSbOdAD_hITUlexpuhtJWVpNU6hp7gfPlZ391xsR/s1600/Excel+Reports.png" title="Send Email with Excel VBA" width="200" /></a>If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consuming task!</div><div style="text-align: justify;">What we wanted to do is automate the tasks from within the Excel Workbook you're working with. The SendEmail() Function below will do the task for you.</div>Function Definition:  <pre class="brush: vb">Function SendEmail(ByVal Username As String, _                    ByVal Password As String, _                    ByVal ToAddress As String, _                    ByVal Subject As String, _                    ByVal HTMLMessage As String, _                    ByVal SMTPServer As String, _                    Optional Attachment As Variant = Empty) As Boolean </pre><input name="IL_IN_TAG" type="hidden" value="1" /> Paramaters: <ul><li style="text-align: justify;">Username - is the email address of the sender.</li><li style="text-align: justify;">Password - is the password of the sender.</li><li style="text-align: justify;">ToAddress - is the recipient of email to which the email be sent. Multiple email addresses can be separated with semi-colons.</li><li style="text-align: justify;">Subject - is the subject of the email.</li><li style="text-align: justify;">HTMLMessage - may contain both plain text and html message. </li><li style="text-align: justify;">SMTPServer - is the name of the outgoing email server. If you're connected within a company's intranet you can use your company's outgoing email server. In this tutorial we'll be using gmail's smtp server.</li><li style="text-align: justify;">Attachment - is the file name that will be attached to the message. If you're going to send the workbook that you're working with as an attachment, you can just put ThisWorkbook.FullName.</li></ul>Requirement: This function requires you to add a reference to Microsoft CDO for Windows 2000. At Microsoft Visual Basic Interface go to Tools>References... <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgutoP6cEtsjoswO32DS1cgQON9I0FzjuXskWYyyUsVDxUEGunrfYnvpUbizv_2EMo3j42puGlk8ov_nleer1BVYaZo1peVToOvIT5KwS0Iq-0Gv5vUoKCEnicRx06fyPRkj6z8T36pAHPX/s1600/Microsoft+CDO+for+Windows+2000+Library.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgutoP6cEtsjoswO32DS1cgQON9I0FzjuXskWYyyUsVDxUEGunrfYnvpUbizv_2EMo3j42puGlk8ov_nleer1BVYaZo1peVToOvIT5KwS0Iq-0Gv5vUoKCEnicRx06fyPRkj6z8T36pAHPX/s1600/Microsoft+CDO+for+Windows+2000+Library.png" width="400" /></a></div> CONFIG SETUP: You may also create another sheet for the configuration setup and assign names to ranges or fields. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHOEgsA3epgUjqm_ijCvvi4WaIKM9xu89uR2ykg7xenoJyjhltvurQDPryzGeCKp7hifOkFR0DHMl1poWG5VHsI9iDiljnsoaOF40pz8gfVVItSE49i2QZWo1CoxDFTO3O2RRQkePLDnAu/s1600/Send+Email+with+Excel+VBA+Configuration+Setup.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="181" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgHOEgsA3epgUjqm_ijCvvi4WaIKM9xu89uR2ykg7xenoJyjhltvurQDPryzGeCKp7hifOkFR0DHMl1poWG5VHsI9iDiljnsoaOF40pz8gfVVItSE49i2QZWo1CoxDFTO3O2RRQkePLDnAu/s1600/Send+Email+with+Excel+VBA+Configuration+Setup.png" width="400" /></a></div> USAGE: You can call the function via a click of a button or when a target is changed on a worksheet.  <pre class="brush: vb">Sub Send() Dim Ws As Worksheet Dim Attachment As String Set Ws = ActiveSheet With Ws If Trim(.Range("ATTACHMENT")) = "" Then ThisWorkbook.Save ThisWorkbook.ChangeFileAccess xlReadOnly Attachment = ThisWorkbook.FullName ThisWorkbook.ChangeFileAccess xlReadWrite Else Attachment = .Range("ATTACHMENT") End If 'CHECK WHETHER THE FUNCTION RETURNS TRUE OR FALSE If SendEmail(.Range("SENDER"), .Range("PASS"), .Range("RECIPIENT"), _ .Range("SUBJECT"), .Range("MESSAGE"), .Range("SMTP"), Attachment) = True Then MsgBox "Email was successfully sent to " & .Range("RECIPIENT") & ".", vbInformation, "Sending Successful" Else MsgBox "A problem has occurred while trying to send email.", vbCritical, "Sending Failed" End If End With End Sub </pre><input name="IL_IN_TAG" type="hidden" value="1" /> FULL VBA CODE: <pre class="brush: vb">Function SendEmail(ByVal Username As String, _ ByVal Password As String, _ ByVal ToAddress As String, _ ByVal Subject As String, _ ByVal HTMLMessage As String, _ ByVal SMTPServer As String, _ Optional Attachment As Variant = Empty) As Boolean Dim Mail As New Message Dim Cfg As Configuration 'CHECK FOR EMPTY AND INVALID PARAMETER VALUES If Trim(Username) = "" Or _ InStr(1, Trim(Username), "@") = 0 Then SendEmail = False Exit Function End If If Trim(Password) = "" Then SendEmail = False Exit Function End If If Trim(Subject) = "" Then SendEmail = False Exit Function End If If Trim(SMTPServer) = "" Then SendEmail = False Exit Function End If On Error Resume Next Set Cfg = Mail.Configuration 'SETUP MAIL CONFIGURATION FIELDS Cfg(cdoSendUsingMethod) = cdoSendUsingPort Cfg(cdoSMTPServer) = SMTPServer Cfg(cdoSMTPServerPort) = 25 Cfg(cdoSMTPAuthenticate) = cdoBasic Cfg(cdoSMTPUseSSL) = True Cfg(cdoSendUserName) = Username Cfg(cdoSendPassword) = Password Cfg.Fields.Update If err.Number <> 0 Then SendEmail = False Exit Function End If err.Clear On Error GoTo 0 With Mail .From = Username .To = ToAddress .Subject = Subject .HTMLBody = HTMLMessage If Attachment <> "" Then .AddAttachment Attachment End If On Error Resume Next err.Clear 'SEND EMAIL .Send End With If err.Number = 0 Then SendEmail = True Else SendEmail = False Exit Function End If End Function </pre> RESULTS: Below are the results after running the above code snippet. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQuqf1XwdBlFv6zOe_1IGCKo2d-67LWQS3bC2HHfw7-_lR38YFiwWCZgSEl8VlaIhGYQFXM3r8i0HEgou-eeSaffL7OFcCXHfQttQH7R9rg6um3fQ7PXjuzsb_Uc4sPvn0YY0_pllbz_G4/s1600/Sending+Email+Successful.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="182" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQuqf1XwdBlFv6zOe_1IGCKo2d-67LWQS3bC2HHfw7-_lR38YFiwWCZgSEl8VlaIhGYQFXM3r8i0HEgou-eeSaffL7OFcCXHfQttQH7R9rg6um3fQ7PXjuzsb_Uc4sPvn0YY0_pllbz_G4/s1600/Sending+Email+Successful.png" width="400" /></a></div> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhASR31__rfAPBn3se3GkJ3bd2tVLynkSpP-f0m0Lv7ijxTfJfXS-g3CShyf-C-YMl3t4XwIRQ3jG4xQ59TLI-9-rFZ6cAVcMNls_fIGpEcJyauttyAWr8oDzNUhRvkUd_x2U6ADB5ZeTJd/s1600/Sending+Email+Successful+Gmail.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhASR31__rfAPBn3se3GkJ3bd2tVLynkSpP-f0m0Lv7ijxTfJfXS-g3CShyf-C-YMl3t4XwIRQ3jG4xQ59TLI-9-rFZ6cAVcMNls_fIGpEcJyauttyAWr8oDzNUhRvkUd_x2U6ADB5ZeTJd/s1600/Sending+Email+Successful+Gmail.png" width="400" /></a></div> </div>

excel microsoft excel VBA Send Email with Excel VBA via CDO through GMail

If you're working on a project or having a numerous reports in excel to be sent out to your boss or clients. And what you usually do is save the workbook, compose a new email, copy the contents or attach it on your email client. That's a time consumi…

The Commonly Used Excel Functions

6:03 AM OmelSoft 0

<div dir="ltr" style="text-align: left;" trbidi="on">Some Excel functions may apply to specific subject areas, while others are general and can be applied to all needs. The following shows a list of Excel Functions commonly used by everyone. <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 555px;"><colgroup><col style="mso-width-alt: 3620; mso-width-source: userset; width: 74pt;" width="99"></col><col style="mso-width-alt: 16676; mso-width-source: userset; width: 342pt;" width="456"></col></colgroup><tbody></tbody></table><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWHa-4RMnzFTTWFzTLegj9ki52xZHHzuy0R1Joj-_PB_LWBfqSkxS0lNalNTVmIx-pBO8wflMzBpX1GZnVNvCm540SR7unOtrBG91tb2qW0BpyvsIsPvw2poUI-IBowzENXItibsN0dyig/s1600/Most+Commonly+Used+Excel+Functions.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWHa-4RMnzFTTWFzTLegj9ki52xZHHzuy0R1Joj-_PB_LWBfqSkxS0lNalNTVmIx-pBO8wflMzBpX1GZnVNvCm540SR7unOtrBG91tb2qW0BpyvsIsPvw2poUI-IBowzENXItibsN0dyig/s1600/Most+Commonly+Used+Excel+Functions.png" width="400" /></a></div></div>

excel microsoft excel The Commonly Used Excel Functions

Some Excel functions may apply to specific subject areas, while others are general and can be applied to all needs. The following shows a list of Excel Functions commonly used by everyone.…

Populate Data into ListView Control in VB.Net

10:28 AM OmelSoft 0

<div dir="ltr" style="text-align: left;" trbidi="on">The following example Populates Data into ListView Control in VB.Net when the form is loaded. Add a ListView Control on your Visual Basic Project and add the following code snippet on Form Load event. <pre class="brush: vb">Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load         With ListView1             .View = View.Details             .FullRowSelect = True             'Setup column headers             .Columns.Add("Product", 100)             .Columns.Add("Type", 100)             .Columns.Add("Price", 50)             For i As Integer = 0 To 10                 Dim lvi = .Items.Add("Product " & i)                 lvi.SubItems.Add("Type " & i)                 lvi.SubItems.Add("Price " & i)             Next         End With End Sub End Class </pre> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWrBaJvoL3-ikJmGKi2d_47OZoKos_iFKn30AZSg4bD3ie4OBkCX3hKZf4TFojcS6wMcXcCnW8pRKHoIaLZvgnbu5gtAk6xJ-USJ8gvrv6MH04Yx8St5dUOu9qL9aDwFFWP0cg1lYHugod/s1600/Populate+data+to+listview+control.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="162" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWrBaJvoL3-ikJmGKi2d_47OZoKos_iFKn30AZSg4bD3ie4OBkCX3hKZf4TFojcS6wMcXcCnW8pRKHoIaLZvgnbu5gtAk6xJ-USJ8gvrv6MH04Yx8St5dUOu9qL9aDwFFWP0cg1lYHugod/s320/Populate+data+to+listview+control.png" width="200" /></a> </div></div>

dotNet VB.NET Populate Data into ListView Control in VB.Net

The following example Populates Data into ListView Control in VB.Net when the form is loaded.Add a ListView Control on your Visual Basic Project and add the following code snippet on Form Load event.Public Class Form1Private Sub Form1_Load(ByVal send…

Populate Data into DataGridView Control using For-Loop in VB.Net

9:34 AM OmelSoft 0

The following example shows how to Populate Data to DataGridView Control in VB.Net. <div class="separator" style="clear: both; text-align: center;"></div> <pre class="brush: vb">Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load With dgv 'SET COLUMN HEADERS .Columns.Add("Product Code", "Product Code") .Columns.Add("Product", "Product") .Columns.Add("Quantity", "Quantity") .Columns.Add("Price", "Price") 'START POPULATING DATA ROWS IN GRIDVIEW For i As Integer = 0 To 100 .Rows.Add() .RowHeadersVisible = False .Item(0, i).Value = "PC121113 " & i .Item(1, i).Value = "PRODUCT " & i .Item(2, i).Value = 100 - i .Item(3, i).Value = i Next .SelectionMode = DataGridViewSelectionMode.FullRowSelect .BorderStyle = BorderStyle.Fixed3D .BackgroundColor = Color.White .RowsDefaultCellStyle.BackColor = Color.White .AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke .AllowUserToResizeRows = False End With End Sub End Class </pre> <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidtukAKr1OAkQ78Xc5_3d0bssKpbyairk5dXWsu_uq3A1n-_O4gUJNgiMatAZg8wF8YonstiWYqLrOBIDYpJB36dD6IoimBqTHtPhXMW6VJo7mTrlRZ7NYrCiqkkMalqkf08gVOJgm0G8V/s1600/Populate+Data+to+DataGridView.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidtukAKr1OAkQ78Xc5_3d0bssKpbyairk5dXWsu_uq3A1n-_O4gUJNgiMatAZg8wF8YonstiWYqLrOBIDYpJB36dD6IoimBqTHtPhXMW6VJo7mTrlRZ7NYrCiqkkMalqkf08gVOJgm0G8V/s640/Populate+Data+to+DataGridView.gif" width="184" /></a></div>

dotNet VB.NET Populate Data into DataGridView Control using For-Loop in VB.Net

The following example shows how to Populate Data to DataGridView Control in VB.Net.Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load With dgv 'SET COLUMN HEA…

Change/Remove the Password of a Password-Protected VBA Project in Microsoft Excel

7:28 AM OmelSoft 0

Some Excel VBA developers are protecting their codes and modules with password in order not to let others view the content of their project. It's okay to open these files if you really knew the purpose of the project. But, what if you're in a company having bundles of confidential information that you're not allowed to divulge to any other parties and the Macro runs Malicious Codes to obtain such information. Or in worst cases, can also be used to spread viruses over the network you're connected with. One day, you might have received an email from a friend with an excel file attachment containing macros in it. And you are told to open it because He/She has a data that needs to show to you. And when you open it, suddenly you're system got compromised. So if you have the basic understanding with the programming language (VBA), then you might want to know what are the codes and applications are running when you open the excel file. But that doesn't end there! <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLKgEcFqbByekEUe-1Mw9pwxK5nbV_ZHAJxYgh86qdZ_Pga1klHHyFltIvoPySx-4VIrrDWzGIn8FGRG6lPSXBZMQxJCmd6Nexa0_uP4MSa5S1nIcZ4Hk1mrgunQmveN9rvK_X_4FgAMC1/s1600/VBAProject+Password.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="135" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLKgEcFqbByekEUe-1Mw9pwxK5nbV_ZHAJxYgh86qdZ_Pga1klHHyFltIvoPySx-4VIrrDWzGIn8FGRG6lPSXBZMQxJCmd6Nexa0_uP4MSa5S1nIcZ4Hk1mrgunQmveN9rvK_X_4FgAMC1/s320/VBAProject+Password.png" width="320" /></a></div>When you try to open and view the VBA Project it prompts you to input a password in which you don't have an idea unless if He/She gave it to you. And if you're good at guessing then guess the PASSWORD! We'll, I guess that's not a good idea. But the good news, is that you can set the VBA Project's Password to a password of your own. <input name="IL_IN_TAG" type="hidden" value="1" /> Changing/Removing the Password: So what do you need? 1.) You need to create a new Excel File with a known VBA Project Password.      a. Open Microsoft Excel, Press Alt+F11 to open the Microsoft Visual Basic window.      b. Set the VBA Project Password to '1234'. See <a href="http://tutorpinoy.blogspot.com/2014/01/how-to-set-password-on-your-excel-vba.html" target="_blank">How to Set Password on Excel VBA Project</a>.      c. Save the file as "password.xls" on your desktop or to location you can easily access.      d. Close the file. 2.) You will need any HEX Editor. You can download a free and portable HEX Editor <a href="http://www.pendriveapps.com/goto/HxD_Portable_Hex_Editor/695/2" rel="nofollow" target="_blank">here</a>.      a. After Downloading, extract the compressed package and open the<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzPmZORjIRzQ5m3_vYeYmXigsChkLtmdQE_jeJ-yt_8z8EWem0pTRhHeqzX3h7_db0W4yElnGnL9j9comYQAzjnj5hyqt7aM-gpPvb06MeQRqAZXpheRFPPabxrwKeV6CwivuoAohEFW_z/s1600/HxD.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="HxD.exe" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzPmZORjIRzQ5m3_vYeYmXigsChkLtmdQE_jeJ-yt_8z8EWem0pTRhHeqzX3h7_db0W4yElnGnL9j9comYQAzjnj5hyqt7aM-gpPvb06MeQRqAZXpheRFPPabxrwKeV6CwivuoAohEFW_z/s1600/HxD.png" title="HxD" /></a> on the output folder.      b. Browse the Excel File that you want to change/remove the password and the Excel file you've just created with a known password.      c. Drag and Drop the files on the HEX Editor's Window. As you can see below. <div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrjYbxyk2OLhVgI-6-jSsd9AV6C8yv_DZ55t_XT1w47QEV3ixauHEMdpGOClHsMMZ0Fubyqj5jIj1wolx5zpvPI3JY3dIWWgsH0WelljmjqJ_ICraz-yveTg74V6gw6dT-dLsBvsEjHq8k/s1600/HxD+Files+Opened.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="HEX Editor" border="0" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrjYbxyk2OLhVgI-6-jSsd9AV6C8yv_DZ55t_XT1w47QEV3ixauHEMdpGOClHsMMZ0Fubyqj5jIj1wolx5zpvPI3JY3dIWWgsH0WelljmjqJ_ICraz-yveTg74V6gw6dT-dLsBvsEjHq8k/s400/HxD+Files+Opened.png" title="HEX Editor" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"> </div>     d. Now on the HEX Editor's Window, Select the password.xls tab.      e. Press Ctrl+F, type "cmg" without the "" then press enter. CMG will now be highlighted.      f. Now, Select CMG and DPB values as you can see below and Press Ctrl+C to copy. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_5_FOA8feTnQckJ-KszD2gcq63dGIF1_wrQuB5goxmyY7KHSEKxlL25ScU66WX7wbxDHP1QPjij6JTFxuW8_C2Lvw8LnD3tpYVdGHuFao0HMSToZU7-JT-NA1OtjkkW710HqTyzYDhymT/s1600/CMG+Found.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="85" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_5_FOA8feTnQckJ-KszD2gcq63dGIF1_wrQuB5goxmyY7KHSEKxlL25ScU66WX7wbxDHP1QPjij6JTFxuW8_C2Lvw8LnD3tpYVdGHuFao0HMSToZU7-JT-NA1OtjkkW710HqTyzYDhymT/s400/CMG+Found.png" width="400" /></a></div>         g. Next, select the PasswordProtectedVBAProject.xls tab and repeat steps e & f.      h. Now that you've selected the CMG and DPB values of the Password Protect VBA Project. Press Ctrl+V to paste the ANSI data from the password.xls file.      i. Save the PasswordProtectedVBAProject.xls and close the HEX Editor Window. You can finally open the VBA Project! When you open the Password-Protected VBA Project, you'll now be able to open and view the modules and codes using the password '1234'. And then you found out that the macro is only saying "HELLO WORLD!" =:)

excel microsoft excel Change/Remove the Password of a Password-Protected VBA Project in Microsoft Excel

How to set a Password on your Excel VBA Project

6:08 AM OmelSoft 0

Now, for those who don't know yet How to Set a Password on your VBA Project in Excel. The guide below might be of help to you. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikkSLJB9PF79BpH_zh9oWoeXsWoWOxHabj6tP2TkVU2vQzhVxPoYqnDbgVYHbCuL4dLryCRDBpYNILt4_xTz_9i61a5cN5o7qoTNwMRN_3vZvz8Ipk3nJ26YTtZ4F8zRNxZevfxhRTgDQF/s1600/VBA+Project.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikkSLJB9PF79BpH_zh9oWoeXsWoWOxHabj6tP2TkVU2vQzhVxPoYqnDbgVYHbCuL4dLryCRDBpYNILt4_xTz_9i61a5cN5o7qoTNwMRN_3vZvz8Ipk3nJ26YTtZ4F8zRNxZevfxhRTgDQF/s1600/VBA+Project.png" /></a></div>1. On the VBA Project Explorer. Right Click on the VBAProject(Filename.xls) on the uppermost part of the explorer. 2. On the Menu, Click VBAProject Properties. And the Project Properties window will show up. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif1SvIzCGOSkcjpkxqaydynFIlJ8UkQ6cRmj_ebhemo4SA4tF1WPAOh7LgTD9k6PssbBGOqjLUdmX7VC1DBUA_mymXYDO_DjbBlenn-x96d_jBBxOMhy8vBlpIygnfI1D2Fiolx8jQWKTd/s1600/VBA+Project+Properties.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="286" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif1SvIzCGOSkcjpkxqaydynFIlJ8UkQ6cRmj_ebhemo4SA4tF1WPAOh7LgTD9k6PssbBGOqjLUdmX7VC1DBUA_mymXYDO_DjbBlenn-x96d_jBBxOMhy8vBlpIygnfI1D2Fiolx8jQWKTd/s320/VBA+Project+Properties.png" width="320" /></a></div>3. On the Project Properties, Check the box Lock project for viewing. And Type and Confirm your password. Then Click OK. 4. Finally, Save and close the project as well as the workbook. When you re-open the project it should prompt you to enter the password.

excel microsoft excel How to set a Password on your Excel VBA Project

Now, for those who don't know yet How to Set a Password on your VBA Project in Excel. The guide below might be of help to you.1. On the VBA Project Explorer. Right Click on the VBAProject(Filename.xls) on the uppermost part of the explorer.2. On the …