PDA

View Full Version : Solved: UDF to convert long date to "mmmyy" String



xluser2007
07-19-2008, 01:43 AM
Hi All,

I am trying to make a simple UDF that takes a date e.g. 31/12/2007 or 10/12/2007 and converts it to "Dec07" String i.e. "mmmyy" format on the date.

I tried it as follows:

Public Sub Convert_date_to_string(mrng_input_dte As Range)

Dim mdte_input As Date 'This is the input date, whcih we will convert to a string
Dim mStr_output_date As String

' Convert input range value to a date
mdte_input = CDate(mrng_input_dte.Value)

' Convert input date to string e.g 31/12/2007 to "Dec07"
mStr_output_date = CStr(Format(mdte_input, "mmmyy"))

Debug.Print mStr_output_date

End Sub
Then I tried testing as follows:

Sub test()

Call Convert_date_to_string(ThisWorkbook.Range("previous_val_date"))

End Sub
The "test" routine gave an error:

"Compile error: Method or data member not found"

and highlights "Range" in the routine.

Please note that I have defined "previous_val_date" as a global named range ( a date value of 31/12/2007).

Could anyone please explain why the above isn't working?

regards,

Bob Phillips
07-19-2008, 02:12 AM
This worked for me



Sub test()

Call Convert_date_to_string(Range("previous_val_date"))

End Sub

Bob Phillips
07-19-2008, 02:13 AM
BTW, if it really is going to be a UDF, that is used on a worksheet, why not just

=TEXT(previous_val_date,"mmmyy")

Simon Lloyd
07-19-2008, 02:14 AM
I don't think you are creating a UDF but a routine, if you really want a UDF then

Function MyDate(ChgDate As Date)
MyDate = Format(ChgDate, "mmmyy")
End Function
used like this in the cell =MyDate(?) where ? will be the cell address of the date you want converted.

xluser2007
07-19-2008, 02:19 AM
Hi Bob,

Thanks for your replies.

Your response wroked for me in post 2 as well.

Hover, I misphrased, it will be a Public Sub to be called in VBA macros (i.e. not a UDF).

Basically I'm going to be opening workbooks and changing named ranges that end in say "_Dec07" to "_Jun08", when we are working on the Jun08 reports.

This was just to create the Dec07 string from the date 31/12/2007.

I'm a bot concerned that if the Thisworkbook doesn;t work then when opening new workbooks, it may try and look for "previous_val_date" in the activated workbook. Any way to correct for this?

xluser2007
07-19-2008, 02:22 AM
Simon,

Thanks for your reply. Yeah, it is a Sub, please see my previous response for my exact purpose.

I think we replied at the same time. :)

regards,

Bob Phillips
07-19-2008, 02:36 AM
You could use



ThisWorkbook.Worksheets(1).Range("previous_val_date").Value


assuming that the name is referring to a value on worksheets(1).

xluser2007
07-19-2008, 02:41 AM
You could use



ThisWorkbook.Worksheets(1).Range("previous_val_date").Value

assuming that the name is referring to a value on worksheets(1).
Lovely, exactly what was required. Thanks Bob.

Also, this query relates to a bigger named range replace problem. I have given it its own thread here (http://vbaexpress.com/forum/showthread.php?t=20982), as I that thread may help others who are having a similar problem (didn't want to confuse this issue with that one).

If you have the time Bob, would you mind please taking a look, and offering your valued input.

regards