If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar including the equal sign. If you set the value or formula of a cell to a date, Microsoft Excel checks to see whether that cell is already formatted with one of the date or time number formats. If not, the number format is changed to the default short date number format.
If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array. Have questions or feedback about Office VBA or this documentation? This applies regardless of whether you're using the A1 or the R1C1-style notation.
These 3 types of cell references, as explained at office. If you're interested in learning more about relative, absolute and mixed cell references when using the A1-style notation, the following resources may be of interest to you:. Since the focus of this VBA tutorial is the R1C1-style notation, let's take a look at how you create relative, absolute and mixed references with it:. At its most basic level, the way in which you build relative references using the R1C1-style notation which I explain in this section is the main reason why R1C1-style references allow you to make your VBA code more efficient.
So, even though the R1C1-style reference examples I provide below may look relatively simple, be patient. The sample Sub procedure I provide below, shows how you can use the R1C1-style notation and the Range. FormulaR1C1 property to efficiently set formulas in Excel. Note also that, despite the usefulness of the R1C1-style notation, there are some issues you should keep in mind when creating macros that rely on such notation.
I provide a thorough explanation of this topic towards the end of this blog post. R1C1-style relative references have square brackets [ ] around the numbers of the rows and columns. However, relative R1C1-style references are subject to additional rules. To understand most of this, it may help if you think of relative references in the R1C1-style notation being built in the following 3 steps :.
You specify the row of the cell you're referring to by moving a certain number of rows up or down. For these purposes you count from the cell you're working on the current active cell. More precisely:. The rules for specifying the column to which you're referring to are a reflection of the rules I explain above to refer to the row number.
If you're working on the first cell of the worksheet R1C1, or A1 in A1-style notation and want to refer to cell R8C5 E8 in A1-style notation, you move:. To build absolute references with R1C1-style notation, simply do the opposite of what you do when building relative references.
That is, omit the square brackets [ ]. To build mixed references using the R1C1-style notation, include the square brackets [ ] around the number of the item row or column you want to make relative. In other words:. Let's go back to example 1 of a relative reference using the R1C1-style notation to see how a relative R1C1-style reference looks like:. In that situation, the active cell is R1C1 A1 in A1-style notation. You're referring to cell R8C5 E8 in A1-style notation.
The relative reference in such a situation is R[7]C[4]. To create a reference to all the cells within a particular row or column, simply omit the other item row or column of the reference. What I mean is the following:. The rules regarding relative and absolute references, and the respective use of square brackets [ ] continues to be the same as I explain in the previous sections.
Similarly, if the active cell is R1C1, C[4] refers to the full column located 4 columns to the right or R1C1 column 5. This is a relative reference. One of the topics of focus of this VBA tutorial is the Range. Let's take a look at its main characteristics:.
Therefore, you can both i read the property or ii modify it. The main purpose of the FormulaR1C1 property depends on whether you're reading or modifying the property. The Range. FormulaR1C1 property uses the language of the macro. Therefore, the returned formula when reading or the set formula when writing are both in that language. If you're interested in dealing with R1C1-style formulas in the language of the user, you may be interested in learning about the Range.
FormulaR1C1Local property which I explain below. When you use the Range. FormulaR1C1 property for purposes of returning the formula of a particular range, the exact behavior of the property varies slightly depending on the contents of the relevant range.
More precisely, as explained at the Microsoft Dev Center :. If you use the Range. FormulaR1C1 property to set the formula of a multiple-cell range, Excel fills all the cells of the relevant range with the formula.
Generally, as I show below, Excel adjusts the cell references automatically. You can see how the FormulaR1C1 property works with multi-cell ranges in practice by taking a look at the practical examples below. FormulaR1C1 property to set the value or formula of a particular cell to a date, Excel proceeds as follows:.
In general, you need not be concerned with the language in which you write your VBA code. The reason for this, as explained by Kusleika and Alexander, is that Excel uses 2 object libraries:.
Excel always sets the English version of both libraries as the default. This is the case regardless of which language you use in Excel. Working with formulas is, generally, one of the exceptions to the above rule. In other words, if you're working in a particular VBA application whose users may have different language settings, language compatibility may be an issue you should consider. As a general matter, Excel functions are localized. Let me explain what I mean:.
These, however, are the names used by the English version of Excel. The names of the functions change depending on the particular language you're using Excel in. The following table shows the names of these particular functions in 10 different languages. I prepared the translation using the Excel-Translator. Excel MVP Mourad Louha is an expert in this topic and has invested a substantial amount of time and resources in the development of the Excel-Translator. Mourad explains the challenge when working with Excel functions in a multilingual setting quite clearly as follows:.
If you send your Excel file to someone using a different language for Excel than you, the functions and formulas used in the workbook are automatically translated by Excel when opening the file. However, the automatic translation usually does not work, if you directly insert foreign language formulas into your worksheet. When working with the FormulaR1C1 property, you fall within the second scenario in bold.
If you're reading or setting a formula by using the FormulaR1C1 VBA property, the functions are usually not automatically translated. The easiest way to deal with language compatibility concerns when working with the FormulaR1C1 property is to use the local version of the FormulaR1C1 property :. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar including the equal sign.
If you set the value or formula of a cell to a date, Microsoft Word verifies whether that cell is already formatted with one of the date or time number formats. If not, the number format is changed to the default short date number format. If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions.
Similarly, you can put the formula into a Visual Basic array. Have questions or feedback about Office VBA or this documentation?
0コメント