Using TEXT function in VBA

  • Thread starter Thread starter Domenick
  • Start date Start date
D

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.
 
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")
 
Yes. More eloquent ("elegant"?) would be to give it a range name, e.g.
"myRange" and then

myDate = Format(Range("myRange"),"mmmm")
 
Of course you would use C9 (to match your posted question) rather than my
example A1 cell reference.
 
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
 
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
 
Back
Top