address | Returns the text of the first cell in the reference. |
col/ row | Column/row number of reference |
color | EValuates to 1 if the cell has a color format for negative values (related to range.numberFormat?) |
contents | |
filename | The full filename the workbook. |
format | Returns a string that indicates the cell's number format) |
parantheses | |
prefix | |
protect | 0 if the cell is not locked, 1 otherwise. |
type | b if the cell is empty, l if the cell contains text, v for evertyhing else. |
width | An array with two elements: cell width and a boolean value that indicates if this width corresponds to the default width. |
cell("filename", ref)
cell("filename", a1) can be used to find the filename of an Excel
workbook.
option explicit
sub main() ' {
'
' In order for cell("filename", …) to return a value,
' we need to save the current workbook:
'
activeWorkbook.saveAs _
fileName := environ("TEMP") & "\" & "bla.xlsm" , _
fileFormat := xlOpenXMLWorkbookMacroEnabled
'
' The following evaluates to something like
' C:\Users\REN~1\AppData\Local\Temp\[bla.xlsm]Sheet1
'
cells(2, 2).formula = "=cell(""filename"", a1)"
end sub ' }
Getting the path, including trailing backslash, to a
workbook.
=left( cell("filename"; a1) ; find("[" ; cell("filename"; a1) ) -1)
cell("format", ref) returns a string that indicates the cell's
number format).
| Value | Number format |
G | General |
F0 | 0 |
,0 | #,##0 |
F2 | 0.00 |
,2 | #,##0.00 |
C0 | $#,##0_);($#,##0) |
C0- | $#,##0_);[Red]($#,##0) |
C2 | $#,##0.00_);($#,##0.00) |
C2- | $#,##0.00_);[Red]($#,##0.00) |
P0 | 0% |
P2 | 0.00% |
S2 | 0.00E+00 |
G | # ?/? or # ??/?? |
D4 | m/d/yy or m/d/yy h:mm or mm/dd/yy |
D1 | d-mmm-yy or dd-mmm-yy |
D2 | d-mmm or dd-mmm |
D3 | mmm-yy |
D5 | mm/dd |
D7 | h:mm AM/PM |
D6 | h:mm:ss AM/PM |
D9 | h:mm |
D8 | h:mm:ss |
- is appended if the cell is formatted in color for negative values.
() is appended if the cell is formatted with parentheses for positive or all values.