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

[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?

 

[Bernie Deitrick]

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

 

[cate]

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

perfect.
Thanks.