Using TEXT function in VBA

[Domenick]

I need to read a certain cell containing a date from a specific worksheet and
extract the long month name for my macro.

The Excel function would be =TEXT(Data!C9,"mmmm").

I have figured out how to write it in VBA if I am just interested in getting
cell and not specifying the specific worksheet:

Dim myDate as String
myDate = Application.WorksheetFunction.Text(C9,"mmmm")

How do I specify that I want C9 from a specific worksheet? I have been
successful with the following code:

myDate =
Application.WorksheetFunction.Text(Worksheets("Data").Cells(9,3),"mmmm")

Is there a more eloquent way of doing this? It seems awkward. Thanks for the
help.

 

[Rick Rothstein]

You can do it several ways directly using VB code, depending on your
needs...

MonthName = MonthName(Month(Range("A1").Value))

MonthNameAbbreviated = MonthName(Month(Range("A1").Value), True)

MonthName = Format(Range("A1").Value, "mmmm")

MonthNameAbbreviated = Format(Range("A1").Value, "mmm")

 

[B Lynn B]

Yes. More eloquent ("elegant"?) would be to give it a range name, e.g.
"myRange" and then

myDate = Format(Range("myRange"),"mmmm")

 

[Rick Rothstein]

Of course you would use C9 (to match your posted question) rather than my
example A1 cell reference.

 

[Lars Klintholm]

myDate = Format(Sheets("Data").Cells(1, 1).Value, "mmmm")

Regards
Lars Klintholm

 

[Lars Klintholm]

myDate = Format(Sheets("Data").Cells(1, 1).Value, "mmmm")

Cells(9,3) of course

 

[Domenick]

Thanks. But how would I specify the specific worksheet using your examples?

 

[Rick Rothstein]

Like you would do in any other situation where it is needed to be
specified... qualify the Range property call with it. For example, using
just the first statement I posted as an example (you would do the same for
the other statements if you decided to use them instead)...

MonthName = MonthName(Month(Worksheets("Sheet1").Range("A1").Value))

or, if you are using a With statement (note the dot in front of the Range
keyword for this version)....

With Worksheets("Sheet1")
'
' other statements needing to reference back to Sheet1
'
MonthName = MonthName(Month(.Range("A1").Value))
'
' any other statements needing to reference back to Sheet1
'
End With

 

[Rick Rothstein]

Like you would do in any other situation where it is needed to be
specified... qualify the Range property call with it. For example, using
just the first statement I posted as an example (you would do the same for
the other statements if you decided to use them instead)...

MonthName = MonthName(Month(Worksheets("Sheet1").Range("A1").Value))

or, if you are using a With statement (note the dot in front of the Range
keyword for this version)....

With Worksheets("Sheet1")
'
' other statements needing to reference back to Sheet1
'
MonthName = MonthName(Month(.Range("A1").Value))
'
' any other statements needing to reference back to Sheet1
'
End With