Using TEXT function in VBA
 

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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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")

--
Rick (MVP - Excel)


"Domenick" wrote in message
...
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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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

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

"Domenick" wrote:

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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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")

--
Rick (MVP - Excel)


"Domenick" wrote in message
...
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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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

Regards
Lars Klintholm

On 17-12-2009 Domenick wrote:

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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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

Cells(9,3) of course
--
Regards
Lars Klintholm

Using TEXT function in VBA
 

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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")

--
Rick (MVP - Excel)


"Domenick" wrote in message
...
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("Dat a").Cells(9,3),"mmmm")

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


.

Using TEXT function in VBA
 

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").V alue))

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 (MVP - Excel)


"Rick Rothstein" wrote in message
...
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")

--
Rick (MVP - Excel)


"Domenick" wrote in message
...
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("Dat a").Cells(9,3),"mmmm")

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

Using TEXT function in VBA
 

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").V alue))

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 (MVP - Excel)


"Domenick" wrote in message
...
Thanks. But how would I specify the specific worksheet using your
examples?

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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")

--
Rick (MVP - Excel)


"Domenick" wrote in message
...
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("Dat a").Cells(9,3),"mmmm")

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


.