UDF issue with date format

The function below creates a dateserial of a date that I need. But in the end instead of showing up in the cell as a serial I want it to be "mmm-yy". I can do that now if I select the cell after the fact and change the date format. When I try to do it at the end of the function I just get value errors. So any help would be appreciated.

Please Login or Register  to view this content.

Thanks for any and all help,

First rule to remember whenever you write a function that you intend to call from a worksheet cell: That function cannot "change the operating environment." In other words, the function cannot change any formats or properties, it can't change a selection or activate anything, and so on. It can read data from the spreadsheet (though it is best to pass values through the argument list rather than reading them at run time) and it can return a value to it's assigned cell.

The code you posted doesn't show how you were trying to code in the format change. I never work with date serials and date formats, so I don't know specifically how to get it to auto format the cell for you. Perhaps if you return a date data type instead of a long? You could probably also use the VBA format function (like at the beginning of the code), but then I'm afraid it would return a string rather than something Excel can immediately recognize as a date.

Thank you, I was trying to use the format function mm=format(mm, "mmm-yy") but kept receiving value errors. I assumed it was reading numbers from the serial and couldn't create text from it. I thought since you can turn date to serial you should be able to do the reverse. Just hadn't found a way.
Thanks again,

Ok, that's a different issue.

it appears to be a simple data type mismatch error. According to VBA help, the VBA format function returns a variant/string. In the first instance the format function, it returns the variant/string "yyyy", which VBA can recognize as a number and, thus, assign it to a Long variable. When you use the additional statement at the end, the format function returns the variant/string "mmm-yy", which VBA can't seem to recognize as a number and, therefore, it can't assign it to a Long variable. The function works just fine for me with the additional statement if I dimension MM as a variant or as a string. This will mean that the function will return a text string rather than a date serial, and I'm not sure if Excel will recognize that the text string can be interpreted as a date. It might.