Format function vba changing date

The format function in vba is changing the date. e.g for format("3/12/2009","DD/MM/YYYY"), the function returns 12/03/2009 where "3/12/2009" is what excel vba reads from a cell that has the value 12-Mar-2009 and the format as "dd-mmm-yyyy"

From stackoverflow

• No it's not.

  If a date-as-string is passed to the Format function, it will parse it using current regional settings. Your settings are obviously MM/DD/YYYY which is default for USA. Nothing prevents Excel from displaying a date as DD/MM/YYYY if set manually, but by default it would display MM/DD/YYYY.

  To do: Stop reading dates as strings. Read them as dates.

  dim d as date
  d = activecell.value
  

  barrowc : Or if you must use a String then put it in YYYY-MM-DD format: 2009-03-12 for your example. Still better to use a Date though