Year, Month, Day of a Date | DateAdd | Current Date and Time | Hour, Minute, Second | TimeValue Learn how to work with dates and times in Excel VBA. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet. […]
Join Strings | Left | Right | Mid | Len | Instr In this chapter, you’ll find the most important functions to manipulate strings in Excel VBA. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet. Join Strings We […]
This chapter teaches you how to deal with macro errors in Excel. First, let’s create some errors. Place a command button on your worksheet and add the following code lines: x = 2 Range(“A1”).Valu = x 1. Click the command button on the sheet. Result: 2. Click OK. The variable x is not defined. Because […]
Single Loop | Double Loop | Triple Loop | Do While Loop Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines. Single Loop You can use a single loop to loop through a one-dimensional range […]
If Then Statement | Else Statement Use the If Then statement in Excel VBA to execute code lines if a specific condition is met. If Then Statement Place a command button on your worksheet and add the following code lines: Dim score As Integer, result As String score = Range(“A1”).Value If score >= 60 Then […]
Integer | String | Double | Boolean This chapter teaches you how to declare, initialize and display a variable in Excel VBA. Letting Excel VBA know you are using a variable is called declaring a variable. Initializing simply means assigning a beginning (initial) value to a variable. Place a command button on your worksheet and […]
Range Examples | Cells | Declare a Range Object | Select | Rows | Columns | Copy/Paste | Clear | Count The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of […]
Object Hierarchy | Collections | Properties and Methods Learn more about the Workbook and Worksheet object in Excel VBA. Object Hierarchy In Excel VBA, an object can contain another object, and that object can contain another object, etc. In other words, Excel VBA programming involves working with an object hierarchy. This probably sounds quite confusing, […]
The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Place a command button on your worksheet and add the following code lines: 1. A simple message. Result when you click the command button on the sheet: 2. A little more advanced message. First, enter a […]
Developer Tab | Command Button | Assign a Macro | Visual Basic Editor With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab. Developer Tab […]
The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click […]
Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems. Load the Solver Add-in To load the solver add-in, execute the following steps. 1. On the […]
Create Different Scenarios | Scenario Summary | Goal Seek What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily. Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price […]
Insert a Table | Sort a Table | Filter a Table | Total Row Tables allow you to analyze your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table. Insert a Table To insert a table, […]
Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table Pivot tables are one of Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 213 records and 6 fields. Order ID, […]
A simple chart in Excel can say more than a sheet full of numbers. As you’ll see, creating charts is very easy. Creating chart in MS Excel Combination Chart Error Bars Chart Sheet Data Series Gantt Chart Gauge Chart Sparklines Trendline Axes Pareto Chart Thermometer Chart Scatter Chart Area Chart Bar Chart Pie Chart Line […]
Highlight Cells Rules | Clear Rules | Top/Bottom Rules Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value. the following are some conditions by which a user can effectively manage his data. Highlight Cells Rules Color Scales Manage Rules Conflicting Rules Shade Alternate Rows New Rule […]
Number and Text Filters | Date Filters | Advanced Filter | Data Form | Remove Duplicates | Outlining Data Filter your Excel data if you only want to display records that meet certain criteria. 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Filter. Arrows in the column […]
One Column | Multiple Columns You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. One Column To sort on one column, execute the following steps. 1. Click any cell in the column you want to sort. 2. To sort in ascending order, on the […]
Without Array Formula | With Array Formula | F9 Key | Count Errors | Count Unique Values | Count with Or Criteria | Sum with Or Criteria | This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. Without Array Formula Without using an array formula, we would execute the […]
