CODEWITHSUNDEEP
excelformattinguserformvba
Felício
Felício

Reputation: 163

Global VBA date format and decimal separator

Is there a way to change VBA settings globally on PC to accept dates and number on a specified format? (on my case dd/mm/yyyy and comma)

Changing Excel settings doesn't solve it for me.

As an small time VBA developer, I'm mostly creating userforms for data input and validation. Alongside with some basic access privileges, It keeps users (mostly an client's hired arms) from nosing on the database and corrupting it.

But, on form's submitting, the textbox values are saved temporally on spreadsheet's cells. Somehow on this step dates get scrambled and in some cases an 3 decimal places numeric gets multiplied by a thousand (e.g. 1/2/2000 turn to 2/1/2000 and 1,234 turn 1234). It defeats the whole purpose of those applications - data gets corrupted.

I've been able to workaround these using the Format(expression, format) function, but that must be applied every time an date or some precision number is added, or even used on some auxiliary task.

This is an recurrent problem for me because, as an Brazilian, dates are formatted as dd/mm/yyyy and decimal separator is ","(comma) on practically 100% of my local users.

Anybody had similar problems?

TIA

Upvotes: 1

Views: 4547

Answers (4)

Nikita Kalganov
Nikita Kalganov

Reputation: 631

For somebody who still faces similar problems with VBA code, I would like to add some details that were apparently missed out in this thread before.

First of all, VBA actually has a universal date format that Excel can read regardless of the local regional settings configured in the user's Windows environment - it's the U.S. date format structured as MM/DD/YYYY (you can read more about it here).

Thus whenever you need to accept and use dates notwithstanding the user's regional settings, I suggest that you should:

  1. get the input date in the user's regional format as a string;
  2. re-structure the date-string to match the U.S. date format specified above;
  3. then CDate the result and use this output in your macro/workbook for any further date-specific action.

This way, you can do basically anything VBA can do with a date in spite of the format the user actually has, and if you write the U.S. formatted dates into cells they will be automatically presented to the user in their regional format without any extra effort on your behalf.

As for the separators, there are several Excel properties that you can use to override the user's default separator based on their regional settings and identify which separators you want your macro to use:

 Application.DecimalSeparator = "-" 
 Application.ThousandsSeparator = "-" 
 Application.UseSystemSeparators = False

UPD to my previous answer regarding decimal separators: Apparently Application.DecimalSeparator etc. properties impact only the UI (how the number is shown in a cell) rather than how the decimals are treated by VBA calculations. Therefore for correct calculations it's probably better to replace commas with dots (or vice versa) before the actual calculation.

In my case where the input Price data in rngPrice Range has comma decimal separators (e.g. 9,005), I've decided to replace comma with a dot before conversion to Double like this:

Price = CDbl(Replace(rngPrice.Value2, ",", "."))
'Further calculations involving Price
'Then you can output Price to a cell as it is, and it should adapt to the regional settings in the UI automatically as dot is a US decimal separator.

Upvotes: 0

Gregg Burns
Gregg Burns

Reputation: 333

Here I write a function called gdate that accepts a string with a date in a specific format. Then I parse the string, then call cdate based on the users date settings.

All I have to do is find/replace cdate with gdate throughout my code. Now I have a way to handle all date formats by always expecting an exact one gdate("mm/dd/yyyy"). Adjust the parsing if you want to expect different format. Building off built-in objects and functions is how we make things work. 

Function gdate(ByVal dstring As String)
    '  0 = month-day-year;   1 = day-month-year;   2 = year-month-day
    d = Mid(dstring, InStr(1, dstring, "/") + 1, Len(dstring) - 5 - InStr(1, dstring, "/"))
    m = Left(dstring, InStr(1, dstring, "/") - 1)
    y = Right(dstring, 4)
    dtype = Application.International(xlDateOrder)

    Select Case dtype
        Case 0: gdate = CDate(m & "/" & d & "/" & y)
        Case 1: gdate = CDate(d & "/" & m & "/" & y)
        Case 2: gdate = CDate(y & "/" & m & "/" & d)
    End Select

End Function

Upvotes: 0

user6432984
user6432984

Reputation:

Excel doesn't have a default date format. Excel uses the Window System Date format settings. You can change you system setting by go to Control Panel -> Change date, time and number formats.

Change Date Format in Windows 7, 8.1 and Windows 10 to dd-mm-yyyy

After adjusting the Windows System Settings to dd-mm-yyyy, CDate will expect strings to be in the dd-mm-yyyy.

Range("A1").Value = CDate( "11/01/2016" )

Result: Monday, January 11, 2016

Upvotes: 1

Felício
Felício

Reputation: 163

Summary of comments for those to lazy to read.

Alright, as Thomas Inzina pointed, the strait answer to my question is NO, you can't because there isn't such thing in VBA as this Global setting.

As Rory pointed out, the CDate function should solve (indeed it does) this issue, at least as to the date. Again, Thomas answer didn't include it but it points to the windows conf that would be used by the CDate function.

Datetimepicker, suggested by cyboashu, would solve this issue too, but it requires some tweaking on the user's PC to be available. Too much work for me. Although, this approach has the "pretty" advantage, adds value to your project.

Still looking for the comma/dot bad conversion problem. I'll keep editing this answer while none better exists.

Upvotes: 0

Related Questions