Excel VBA Named Range Properties

This Excel VBA tutorial explains some common Properties of Name Object (Named Range), and demonstrate how to replace named Range with formula. You may also want to read: Excel find all external links and broken links in workbook Excel VBA Named Range Properties In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) In the below screenshot, I have defined a Named Range called testNameRng, which refers to Cell A1 in Worksheet1. Currently A1 conta...
More

Excel VBA Application WorksheetFunction Property

This Excel tutorial explains how to use Application WorksheetFunction Property in Excel VBA. Excel VBA Application WorksheetFunction Property Excel VBA Functions and Excel Worksheet Functions are slightly different. Sometimes even though the Function names and syntax are the same, the results are different. There are many nifty Worksheets Functions but unfortunately they are not present in VBA, and vice versa. In order to fully utilize all Functions under any environment, we can do the f...
More

Excel VBA AskToUpdateLinks Property

This Excel tutorial explains how to enable and disable Application AskToUpdateLinks Property (Ask to update automatic links in Excel Options). You may also want to read Differences among Function, Sub, Method, Property Excel Options - Ask to Update automatic links When you link external data source outside your workbook, such as another workbook or Access database, you will be asked whether to update the data automatically when you open the workbook. This workbook contains links to one ...
More

Excel VBA usedRange Property and reset usedRange

This tutorial explains how to use Excel VBA usedRange Property to find the last used row and column number, and reset usedRange. You may also want to read: Excel VBA custom Function last row and last column Excel delete blank rows and blank cells before import into Access Excel VBA UsedRange Property Excel VBA UsedRange is a worksheet Property, it returns the area Range bounded by first used cell and last used cell. "Used Cell" is defined as Cell containing formula, formatting, value th...
More

Excel VBA Application Calculation Property

This tutorial explains Excel Application Calculation Property and difference among xlCalculationAutomatic, xlCalculationManual, xlCalculationsemiautomatic Excel VBA Application Calculation Property In Excel worksheet, there are three options under Workbook Calculation. The options in Workbook Calculation uses Calculation Property in Excel VBA, which means if you execute the corresponding VBA code, the Excel options change accordingly. Excel Option Description VBA Automatic...
More

VBA Excel ColorIndex Property

This tutorial explains how to use VBA Excel colorIndex Property to set color and get color from Cell or Shape, meaning of -4142, -4105, xlNone, xlAutomatic What does VBA Excel ColorIndex Property do? VBA Excel ColorIndex Property is to set color or get color for Objects like Cell color and Shape color. ColorIndex offers 56 basic colors plus the following special numbers. ColorIndex Value Explanation -4142 / xlColorIndexNone / xlNone No fill -4105 / xlColorIndexAutomatic / xl...
More

VBA Excel RGB Property and get RGB Color

What does VBA Excel RGB Property do? VBA Excel RGB Property is a color Property of Objects, commonly used for Cell color or Shape color. "RGB" stands for Red Green Blue, which are known as three additive primary colors, which can be combined to produce other colors. For example, purple is a mix of blue and red.  In RGB Property, you need to give a value (from 0 to 255) for each color in order to mix a new color. For example, Red = 255 means the brightness of color red is 100%. If you ...
More

Excel VBA Formula Property of Range

This Excel VBA tutorial explains how to use Formula Property in Excel VBA. Formula Property in Excel VBA There are two functions for Formula Property: 1. To set formula in a Range, like the way you type a formula in worksheet 2. To retrieve formula from a Range Syntax of  Formula Property To set a formula: Range.Formula = "=your formula" To retrieve a formula: variableName = Range.Formula Remarks You can also set a formula using Value Property Range.Value = "=your formula" ...
More

Excel VBA FormulaR1C1 Property of Range

What is FormulaR1C1 Property in Excel VBA? In R1C1, "R" refers to row, while "C" refers to column. Formula R1C1 allows you set or retrieve formula for a cell, using relative row and column. Why do you need FormulaR1C1? For example, you want to set a formula in C2 to sum A2 and B2, you can type Range("C2").formula= "=A2+B2" or Range("C2").Value = "=A2+B2" Instead of hard code "A2+B2", you can also tell Excel to sum the two cells on the left of the formula cell using FormulaR1C1. "A2+B2...
More

Excel VBA OFFSET Property for Range

What is Excel VBA OFFSET Property? Excel VBA OFFSET property returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to the right. (shift 1 column to the right and shift 0 row). The advantage of using OFFSET property is that even when the actual column change, yo...
More