Below we will look at a program in Excel VBA that shows a print preview of all the possible football matches from a list of teams. Situation: 1. First, we declare one Range object and four variables. We call the Range object rng. One String variable we call matchname, and three Integer variables we call […]
This program in Excel VBA uses the Count property, IsNumeric function, IsEmpty function and Intersect method to test a selection. Situation: Place a command button on your worksheet and add the following code lines: 1. First, we declare two Range objects. We call the Range objects rng and cell. Dim rng As Range, cell As […]
The Union method in Excel VBA returns a Range object that represents the union of two or more ranges (borders below for illustration only). Code line: Union(Range(“B2:C7”), Range(“C6:F8”)).Select Result: Note: the Union method doesn’t return the mathematical union (cell C6 and cell C7 are included twice). The Intersect method in Excel VBA returns a Range […]
This example illustrates the End property of the Range object in Excel VBA. We will use this property to select the range from the Active Cell to the last entry in a column. Situation: Some sales figures in column A. Assume that you will be adding more sales figures over time. Place a command button […]
The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range (border below for illustration only). Place a command button on your worksheet and add the following code lines: Dim example As RangeSet example = Range(“A1:A2”)example.Offset(3, 2).Select Result when you click the command […]
This example teaches you how to select entire rows and columns in Excel VBA. Are you ready? Place a command button on your worksheet and add the following code lines: 1. The following code line selects the entire sheet. Cells.Select Note: because we placed our command button on the first worksheet, this code line selects […]
The Resize property in Excel VBA makes a range (border below for illustration only) a specific number of rows and columns larger or smaller. The Resize property always takes the top left cell of a range as the starting point. Code line: Range(“A1:C4”).Resize(3, 2).Select Result: Explanation: this code line resizes Range(“A1:C4”) to 3 rows and […]
Below we will look at a program in Excel VBA that colors the maximum value of a dynamic range. Situation: Each time we add a number and we click the command button, we want Excel VBA to color the maximum value of these numbers. Place a command button on your worksheet and add the following […]
This example illustrates the CurrentRegion property in Excel VBA. The current region is a range bounded by any combination of blank rows and blank columns. Can you find the current region of cell A1? Place a command button on your worksheet and add the following code line: Range(“A1”).CurrentRegion.Select Result when you click the command button […]
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 […]
R Square | Significance F and P-Values | Coefficients | Residuals This example teaches you how to perform a regression analysis in Excel and how to interpret the Summary Output. Below you can find our data. The big question is: is there a relation between Quantity Sold (Output) and Price and Advertising (Input). In other […]
The correlation coefficient (a value between -1 and +1) tells you how strongly two variables are related to each other. We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables. – A correlation coefficient of +1 indicates a perfect positive correlation. As variable X […]
This example teaches you how to apply exponential smoothing to a time series in Excel. Exponential smoothing is used to smooth out irregularities (peaks and valleys) to easily recognize trends. 1. First, let’s take a look at our time series. 2. On the Data tab, in the Analysis group, click Data Analysis. Note: can’t find […]
This example teaches you how to calculate the moving average of a time series in Excel. A moving average is used to smooth out irregularities (peaks and valleys) to easily recognize trends. 1. First, let’s take a look at our time series. 2. On the Data tab, in the Analysis group, click Data Analysis. Note: […]
This example teaches you how to perform a t-Test in Excel. The t-Test is used to test the null hypothesis that the means of two populations are equal. Below you can find the study hours of 6 female students and 5 male students. To perform a t-Test, execute the following steps. 1. First, perform an […]
This example teaches you how to perform an F-Test in Excel. The F-Test is used to test the null hypothesis that the variances of two populations are equal. Below you can find the study hours of 6 female students and 5 male students. To perform an F-Test, execute the following steps. 1. On the Data […]
This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel. A single factor or one-way ANOVA is used to test the null hypothesis that the means of several populations are all equal. Below you can find the salaries of people who have a degree in economics, medicine or history. […]
