Excel Text function not?

Question

I enter this Excel VBA line of code and get the following error
A7 =Text (A1,"MM-DD-YY")
"Complie error:
Sub or Function not defined."
I have the Text function available in the function box, and the above line works good .
In Tools >references, I do not have the EXCLLINK.XLA add-in
I recorded the Text function with a Macro, it works, but here is what I get

Range("A7").Select
ActiveCell.FormulaR1C1 = "=TEXT(R[-6]C,""mm-dd-yy"")"

What does this mean? R1C1 R[-6]C ?????
Thank you

Answer 1

It is paired folds of the peritoneum. It is only called the gubernaculum during development and is called other names (depending on the gender) after development of the repro and urinary structures.



It does two things - aids in the descent of the testes and holds the testes in place inside the scrotum once they have descended into their correct position.



It also has a function in females - it suspends the ovary from the pelvic wall and provides support for the ovary and uterus.



A more detailed explanation can be found at the wiki link.

Answer 2

R1C1 is Excel's relative referencing method in VBA. (R)ow and (C)olumn. The [-6] means 6 rows above the active row, which is row 7. So, that is referring to row 1 or A1 since there is no column indicator after the 'C'.



When you record a macro, certain actions are recorded by Excel using the R1C1 style.



Try using this instead:



Range("A7").Select

ActiveCell.Formula = "=TEXT(A1,""mm-dd-yy"")"

Answer 3

The error is thrown because not all Excel functions can be directly referenced in VBA. For many, you must preface the line in one of several ways:



A7 = Application.Text(A1,"mm-dd-yy")



A7= WorkSheetFunction.Text(A1,"mm-dd-yy")



A7 = Application.WorksheetFunction.Text(A1, "MM-DD-YY")



I choose the method with the least keystrokes, i.e. 'Application.Text' in this case.



When you record a macro in Excel, it uses Relative Referencing style when referring to offset cells. That style is indicated by the 'R1C1' suffix at the end of ActiveCell.FormulaR1C1. The 'R' refers to 'Row', and the 'C' refers to 'Column'. Absolute referencing refers to the cell address, i.e. A7.



In your specific example, the R[-6]C indicates minus 6 rows from the active cell (A7) and the current column, simply indicated by 'C'. Thus, it references cell A1. I find relative referencing in VBA to be a complete pain.

Answer 4

Because "mm-dd-yy" is not a recognized text format.



The reason for the R1C1 which is just a straight numerical representation of where the cell is. If A7 is the active cell, it is reference a cell 6 rows to the left of the activecell.



In VBA, in many things you can't use "A7" or "A1". its cells(7,1) or cells(1,1). this allows the use of simple math to move around a spreadsheet.