Author: Juan David

Using VBA logical operators AND - OR

VBA Logical Operators in Microsoft Excel

 

 

 

The most common logical operators in VBA are: AND and OR. Each of these operators is very useful for evaluating conditions and making appropriate decisions about the code that will be executed

 

The AND Logical Operator

 

The AND logical operator is the operator that helps us to enforce two conditions. This operator allows to execute a block of code that must meet condition1 AND condition2.

In the following example I have the qualification of two exams. Only if both exams have a grade higher than 3, then the student will be approved, otherwise the grade will be disapproval.

 

VBA Logical Operators Initial Example

 

We are going to use an IF statement to compare the exams qualification with the grade condition. This comparison is that we usually do with the logical operators.

The code that will be executed when the button is pressed will be the following:

 

VBA Logical Operators AND Code

 

When we execute this code we will obtain the “Approved” result, this is because both exams have a rating greater than 3:

 

VBA Logical Operator AND approved example

 

In this way we verify that the logical operator And helps us to force both conditions to be fulfilled. However, if the value of one of the cells is less than 3, then we will have a different result:

 

AND Logical Operator Reprobate Example

 

The logical operator And will return the true value only when both conditions are met and it will suffice that one of them is not met to obtain a negative result.

 

The OR Logical Operator

 

The OR logical operator translates will allow us to know if at least one of the conditions is fulfilled, that is to say, if the condition1 OR condition2 is fulfilled.

 

If we change the previous example a bit and say that it is sufficient that one of the two grades is greater than 3 for the student to be approved, then we can modify the code as follows:

 

Code for OR logical operator example

 

If any of the grades are higher than 3, then the student will be approved:

 

OR logical Operator Approved Example

 

The only way the logical OR operator returns a false value is that none of the conditions are met. In our example, the student will be reprobate only when both grades are less than 3:

 

OR Logical Operator Reprobate Example

 

To conclude in the following table we can see in each case how the logical operators evaluate the condition:

 

Conditions Table for VBA Logical Operators AND - OR

 

Excel Total 

 

 

PRICE Excel Function

What is the PRICE Excel Function Used for?

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

 

PRICE

Returns the price per $100 face value of a security that pays periodic interest.

 

PRICE(settlement; maturity; rate; yld; redemption; frequency; [basis])

 

The PRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Rate Required. The security’s annual coupon rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; frequency; and basis are truncated to integers.
  • If settlement or maturity is not a valid date; PRICE returns the #VALUE! error value.
  • If yld < 0 or if rate < 0; PRICE returns the #NUM! error value.
  • If redemption <= 0; PRICE returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; PRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; PRICE returns the #NUM! error value.
  • If settlement => maturity; PRICE returns the #NUM! error value.
  • PRICE is calculated as follows:where:
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in coupon period in which the settlement date falls.
    • N = number of coupons payable between settlement date and redemption date.
    • A = number of days from beginning of coupon period to settlement date.

 

Interactive Example

Change the white cells data and see the result in the yellow cell.

 

 

 

ODDFPRICE in Microsoft Excel

Using the ODDFPRICE Function in Excel

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

ODDFPRICE

Returns the price per $100 face value of a security having an odd (short or long) first period.

 

ODDFPRICE(settlement; maturity; issue; first_coupon; rate; yld; redemption; frequency; [basis])

 

The ODDFPRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Issue Required. The security’s issue date.
  • First_coupon Required. The security’s first coupon date.
  • Rate Required. The security’s interest rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; issue; first_coupon; and basis are truncated to integers.
  • If settlement; maturity; issue; or first_coupon is not a valid date; ODDFPRICE returns the #VALUE! error value.
  • If rate < 0 or if yld < 0; ODDFPRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ODDFPRICE returns the #NUM! error value.
  • The following date condition must be satisfied; otherwise; ODDFPRICE returns the #NUM! error value:maturity > first_coupon > settlement > issue
  • ODDFPRICE is calculated as follows:Odd short first coupon:

    where:

      • A = number of days from the beginning of the coupon period to the settlement date (accrued days).
      • DSC = number of days from the settlement to the next coupon date.
      • DFC = number of days from the beginning of the odd first coupon to the first coupon date.
      • E = number of days in the coupon period.
      • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)

    Odd long first coupon:

    where:

    • Ai = number of days from the beginning of the ith; or last; quasi-coupon period within odd period.
    • DCi = number of days from dated date (or issue date) to first quasi-coupon (i = 1) or number of days in quasi-coupon (i = 2;…; i = NC).
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in the coupon period.
    • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)
    • NC = number of quasi-coupon periods that fit in odd period. (If this number contains a fraction; it is raised to the next whole number.)
    • NLi = normal length in days of the full ith; or last; quasi-coupon period within odd period.
    • Nq = number of whole quasi-coupon periods between settlement date and first coupon.

 

Interactive Example

 

 

 

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

How to Select Cells by Value in Microsoft Excel

 

 

When you have a lot of data in a table and want only to select a group of cells with a certain value we can use Excel search for quick selection of those cells.

From the following table of data I want to select all the cells that have the value $ 1,000 and once selected place the cell text in bold

Select Cells by Value

Selecting by the Search Method

An alternative is to use the Excel search box to select the cells that meet the condition set. To do that we open the search bog, then we specify the value we’re looking for and finally click the Find All button.

Find All Selected Data by Value

Excel display all the cells that match the specified value, we select only the results for the worksheet cells are also selected Tip: To select all results can click on the first row of results and then press the Shift key and click the last row. Since you have selected all results, close the search window and observe the cells selected in the worksheet ready to apply the text in bold

Formatting Select Data

Using a Conditional Formatting Rule

If we make a selection of cells to make a format modification, then we can use an alternative method Once we have selected the range containing the information (b2:E16) I must press the Condittional Format button on the Home tab and select the Highlight cell rules option and then Equal To button.

Conditional Formatting to Select Cells by Value

In the dialog box I have to specify the quantity that I’m looking for and select the Custom Format option.

Custom Format

Then I’m going to select a Bold style for the fonts to format the cells that comply with this rule

Conditional Formatting Bold Style

Then click OK to close the dialog box and then click OK to apply the conditional formatting rule, we will have the cells of value equal to $1000 with a bold style format. Excel Total
 

 

How to Use Excel Comments

How to Use Excel Comments

 

 
 

Excel allows you to insert comments in any cell of your Worksheet in Microsoft Excel. Those Excel Comments are very useful to make a check, give some instructions or simply give a detailed explanation about the cell content.

Add a comment to a Cell

The first step is to select the cell that you will add the comment and then go to the Review tab and in the group Comments and press the New Comment button (In MAC only New). Clicking on that button, the box necessary to enter the comment is displayed.

Add a Microsoft Excel Comment

Enter your comment in the box and to finish click outside the box. If you need to enlarge the box only positions the mouse over any of the corners and drag the edge to have the desired size. Excel identifies a cell that has a comment through a small triangle in the upper right corner of the cell. To see the comment from a cell only you must position the mouse over that cell and Excel will display the comment.

Modify or Eliminate a Comment

When you select a cell containing a comment Edit and Delete Comment buttons are enabled.

Excel Comment Options

Clicking on the Edit Comment button will show the comment box allowing you to do the editing again. If instead you want to delete the comment is sufficient to press the Delete button. Now if you have an Excel worksheet that contains many comments, you can use the Previous and Next buttons to move between each. Or conversely, if you want to display all comments sheet at the same time you can click Show All Comments button. You can learn more about Microsoft Excel Comments and Review options here: Excel School
 

 

The 3 Time Functions Most Used in Microsoft Excel

The 3 Time Functions Most Used in Microsoft Excel

 

 
Excel provides three functions that help us work with time information in a Worksheet. These functions are useful for extracting information specific about the hour, the minute and the second of a cell containing data with a Number category of Time.

 

Each function, according to its name, extracts a specific part of the data information (hour, minute or second) with only specifying the cell containing the data you want to analyze. For example, suppose that cell B2 has the following information:

Time Type Data in Microsoft Excel

 

1. The HOUR function

To get only the hour number of this cell you can use the HOUR function as follows:

The Hour Function in Microsoft Excel

Just see that Microsoft Excel returns the 17 value because the hours by default is showed with the 24 hours format.

2. The MINUTE function

To get the result of the minutes we use the MINUTE function as follows:

The Minute Function in Microsoft Excel

 

3. The SECOND function

Finally the last one is the SECOND function to get the value of the seconds of the cell information:

The Second Function in Microsoft Excel

We learn in this post that when you need to extract specific information from a cell value related to time, you can use those 3 functions to get the specific value of hour, minute and second that you can use in later calculations. You can learn more about Microsoft Excel Time functions here: Excel School
 

 

Excel Visual Basic (VBA) for Beginners - Use Excel VBA to Cleanse a Dataset

Excel Visual Basic (VBA) for Beginners to Cleanse a Dataset

 

 
This Excel video series helps Excel users just starting out with code in the Visual Basic program, or those who have never coded before, who are curious about it and just heard about it, about its powerful utilisation. In a lot of cases, Excel users find themselves engaged in tricky manual tasks in study subjets or work tasks, that take up time, create stress and lose of time. Data cleansing is one such task.

To explain that, if you have ever gone through a list of data and looked for mistakes (in dates or numbers, for example) then you have done a data cleansing task. With a little coding knowledge, that we are going to see in the following videos, it is possible to dramatically speed up this kind of task, reduce the stress and increase your productivity. You can learn more about Microsoft Excel go on here: Excel School

The 1st Part: Introduction

The 2nd Part: Beginning with VBA

The 3rd Part: Using Messaging Boxes

The 4th Part: Stopping the program

The 5th Part: Finishing and testing the program

 

 

PMT Function Calculating a loan payment

PMT Function Calculating a Loan Payment

 

 

In this video, learn how to calculate the payment for a loan based on constant payments and a constant interest rate. You need to know: the interest rate, the number of periods and the present value of the loan. You can learn more about Microsoft Excel financial functions here: Excel School

 

 

Dependent Data Validation Lists in Microsoft Excel

Dependent Data Validation Lists in Microsoft Excel

This video will walk you through the process of setting up and using dependent data validation lists with Microsoft Excel. You could refer to this as cascading data validation. This is very useful to create templates of databases that you need to be completed by other users. The other users only have a limited option to fill each cell. You can learn more about Microsoft Excel productivity here: Excel School

 

Using Navigation Tools in Microsoft Excel

Using Navigation Tools in Microsoft Excel

Learn about essential training in Microsoft Excel, one of the first tools that you have to begin to use, the navigation. Save a lot of time, learning basic keyboard combinations, or commands with the mouse, to navigate between worksheets, workbooks, cells, columns, rows, etc. And begin to increase of the productivity in the using of Microsoft Excel You can learn more about Microsoft Excel productivity here: Excel School