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

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

Read more »

Send Email with Excel VBA via CDO through GMail

<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 <b>SendEmail()</b> Function below will do the task for you.</div><b>Function Definition:</b><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Function SendEmail(ByVal Username As String, _<br />                   ByVal Password As String, _<br />                   ByVal ToAddress As String, _<br />                   ByVal Subject As String, _<br />                   ByVal HTMLMessage As String, _<br />                   ByVal SMTPServer As String, _<br />                   Optional Attachment As Variant = Empty) As Boolean<br /></pre><!--INFOLINKS_ON--><input name="IL_IN_TAG" type="hidden" value="1" /><br /><b>Paramaters: </b><br /><ul><li style="text-align: justify;"><b>Username</b> - is the email address of the sender.</li><li style="text-align: justify;"><b>Password</b> - is the password of the sender.</li><li style="text-align: justify;"><b>ToAddress</b> - 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;"><b>Subject</b> - is the subject of the email.</li><li style="text-align: justify;"><b>HTMLMessage </b>- may contain both plain text and html message. </li><li style="text-align: justify;"><b>SMTPServer </b>- 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;"><b>Attachment </b>- 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 <b>ThisWorkbook.FullName</b>.</li></ul><b>Requirement:</b><br />This function requires you to add a reference to<i> Microsoft CDO for Windows 2000</i>. At Microsoft Visual Basic Interface go to <i>Tools>References...</i><br /><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><br /><b>CONFIG SETUP:</b><br />You may also create another sheet for the configuration setup and assign names to ranges or fields.<br /><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><br /><br /><b>USAGE:</b><br />You can call the function via a click of a button or when a target is changed on a worksheet.<br /><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Sub Send()<br /> Dim Ws As Worksheet<br /> Dim Attachment As String<br /> <br /> Set Ws = ActiveSheet<br /> <br /> With Ws<br /><br /> If Trim(.Range("ATTACHMENT")) = "" Then<br /> ThisWorkbook.Save<br /> ThisWorkbook.ChangeFileAccess xlReadOnly<br /> Attachment = ThisWorkbook.FullName<br /> ThisWorkbook.ChangeFileAccess xlReadWrite<br /> Else<br /> Attachment = .Range("ATTACHMENT")<br /> End If<br /><br /> 'CHECK WHETHER THE FUNCTION RETURNS TRUE OR FALSE<br /> If SendEmail(.Range("SENDER"), .Range("PASS"), .Range("RECIPIENT"), _<br /> .Range("SUBJECT"), .Range("MESSAGE"), .Range("SMTP"), Attachment) = True Then<br /> MsgBox "Email was successfully sent to " & .Range("RECIPIENT") & ".", vbInformation, "Sending Successful"<br /> Else<br /> MsgBox "A problem has occurred while trying to send email.", vbCritical, "Sending Failed"<br /> End If<br /><br /> End With<br /><br />End Sub<br /></pre><input name="IL_IN_TAG" type="hidden" value="1" /> <br /><b>FULL VBA CODE:</b><br /><br /><pre class="brush: vb">Function SendEmail(ByVal Username As String, _<br /> ByVal Password As String, _<br /> ByVal ToAddress As String, _<br /> ByVal Subject As String, _<br /> ByVal HTMLMessage As String, _<br /> ByVal SMTPServer As String, _<br /> Optional Attachment As Variant = Empty) As Boolean<br /><br /> Dim Mail As New Message<br /> Dim Cfg As Configuration<br /> <br /> 'CHECK FOR EMPTY AND INVALID PARAMETER VALUES<br /> If Trim(Username) = "" Or _<br /> InStr(1, Trim(Username), "@") = 0 Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(Password) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(Subject) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> If Trim(SMTPServer) = "" Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br /> <br /> On Error Resume Next<br /> Set Cfg = Mail.Configuration<br /> <br /> 'SETUP MAIL CONFIGURATION FIELDS<br /> Cfg(cdoSendUsingMethod) = cdoSendUsingPort<br /> Cfg(cdoSMTPServer) = SMTPServer<br /> Cfg(cdoSMTPServerPort) = 25<br /> Cfg(cdoSMTPAuthenticate) = cdoBasic<br /> Cfg(cdoSMTPUseSSL) = True<br /> Cfg(cdoSendUserName) = Username<br /> Cfg(cdoSendPassword) = Password<br /> Cfg.Fields.Update<br /> <br /> If err.Number <> 0 Then<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> err.Clear<br /> <br /> On Error GoTo 0<br /> With Mail<br /> .From = Username<br /> .To = ToAddress<br /> .Subject = Subject<br /> .HTMLBody = HTMLMessage<br /><br /> If Attachment <> "" Then<br /> .AddAttachment Attachment<br /> End If<br /> <br /> On Error Resume Next<br /> err.Clear<br /><br /> 'SEND EMAIL<br /> .Send<br /> End With<br /> If err.Number = 0 Then<br /> SendEmail = True<br /> Else<br /> SendEmail = False<br /> Exit Function<br /> End If<br /> <br />End Function<br /></pre><!--INFOLINKS_ON--> <b><br /></b><b>RESULTS:</b><br />Below are the results after running the above code snippet.<br /><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><br /><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><br /></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 ...

Read more »

The Commonly Used Excel Functions

<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.<br /><br /><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 li...

Read more »

Populate Data into ListView Control in VB.Net

<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.<br /><br />Add a ListView Control on your Visual Basic Project and add the following code snippet on Form Load event.<br /><pre class="brush: vb">Public Class Form1<br /><br />Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br /><br />        With ListView1<br /><br />            .View = View.Details<br />            .FullRowSelect = True<br /><br />            'Setup column headers<br />            .Columns.Add("Product", 100)<br />            .Columns.Add("Type", 100)<br />            .Columns.Add("Price", 50)<br /><br />            For i As Integer = 0 To 10<br /><br />                Dim lvi = .Items.Add("Product " & i)<br />                lvi.SubItems.Add("Type " & i)<br />                lvi.SubItems.Add("Price " & i)<br /><br />            Next<br /><br />        End With<br />End Sub<br />End Class<br /></pre><br /><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 Pr...

Read more »

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

<br />The following example shows how to Populate Data to DataGridView Control in VB.Net.<br /><br /><div class="separator" style="clear: both; text-align: center;"></div><br /><pre class="brush: vb">Public Class Form1<br /><br /> Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<br /><br /> With dgv<br /> 'SET COLUMN HEADERS<br /> .Columns.Add("Product Code", "Product Code")<br /> .Columns.Add("Product", "Product")<br /> .Columns.Add("Quantity", "Quantity")<br /> .Columns.Add("Price", "Price")<br /><br /> 'START POPULATING DATA ROWS IN GRIDVIEW <br /> For i As Integer = 0 To 100<br /> .Rows.Add()<br /> .RowHeadersVisible = False<br /> .Item(0, i).Value = "PC121113 " & i<br /> .Item(1, i).Value = "PRODUCT " & i<br /> .Item(2, i).Value = 100 - i<br /> .Item(3, i).Value = i<br /> Next<br /><br /> .SelectionMode = DataGridViewSelectionMode.FullRowSelect<br /> .BorderStyle = BorderStyle.Fixed3D<br /> .BackgroundColor = Color.White<br /> .RowsDefaultCellStyle.BackColor = Color.White<br /> .AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke<br /> .AllowUserToResizeRows = False<br /> End With<br /><br /> End Sub<br /><br />End Class<br /></pre><br /><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><br />

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

Read more »