Forum Discussion
activecell (single word not 2) statement
This is actually a 2 parter:
1st can anybody tell whether activecell.offset(2,2) or activecell.formula or activecell.font or any of the other dot extensions are available for use in CELL FORMULAs?
2nd is there anywhere (in the world or on the web) can identify all of the statements (functions | properties) and where that be used, i.e., StatementName1 can be used in VBA & CELL FORMULAs; StateName2 can only be used in VBA; and StatementName3 can only be used in CELL FORMULAs.
I realize that the 2nd part may not exist but thought it was worth asking since many times i have had to experiment to discover the answer!
ActiveCell is VBA, it cannot be used in cell formulas (except through custom VBA functions).
You can refer to the cell 2 rows below and 2 columns to the right of the cell with the formula: for a formula in B2, use OFFSET(B2, 2, 2). When you copy this to other cells, Excel will automatically adjust the reference to B2.
A cell formula cannot return its own formula, that would be a circular reference.
To return the font of the cell with the formula requires VBA or Excel 4 Macro.
- Thank you for timely (and informative) response. Will consider your input.
TL Norman For the second question, I am not aware of one source. However, they are available separately. For example, Excel functions (alphabetical) and Excel functions (by category) cover the Excel formula side, and Excel object model for Visual Basic for Applications (VBA) covers Excel objects (on the VBA side). You can see most VBA-accessible properties, methods, and events (each is associated with an object) for Excel objects and other objects right from the Object Browser in the VBA Editor (it's under the View menu); you can use objects from other applications/libraries by adding references in your VBA project via the Tools | References dialog. For VBA statements, however (such as Dim, Const, Declare, variable assignments, Call), you will need a different source. I recommend that you browse your local library for a good book, particularly as you should learn object management and error (exception) handling, not just blindly choosing statements with which to experiment.
- Thank you for responding so promptly. I am aware of all those locations for the info. I was just hoping there was a place available that would preclude performing multiple searches.
