cell format, custom type: mmm-yy; working with

  • Thread starter Thread starter cate
  • Start date Start date
C

cate

A cell is referenced in an equation. That cells format contains a
custom type: mmm-yy. How does a vba function work with that
generally? Is it a string to be chopped up and worked with, or can
you use some object property to get at the value or month and year?
 
Cate,

If you want to use the formatted value of the cell, you need to use the
..Text property of the range object

For example:

Sub Test()
Dim myC As Range
Set myC = ActiveCell
With myC
.Value = Now
MsgBox CDbl(.Value) & " is the cell's underlying number value"
.NumberFormat = "mmm-yy"
MsgBox .Value & " is the cell's date/time value"
MsgBox .Text & " is the cell's display"
MsgBox Month(.Value) & " is the cell's month number"
MsgBox Format(.Value, "mmm") & " is the cell's month - short"
MsgBox Format(.Value, "mmmm") & " is the cell's month - long"
End With
End Sub


HTH,
Bernie
MS Excel MVP
 
On Nov 7, 8:10 am, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

perfect.
Thanks.
 
Back
Top