Front Range Data Management Services, Inc.

International Issues in VBA Code

International Issues

  Home
  Microsoft Excel
  Microsoft Access
  Custom Reports
  SQL Server
  Visual Basic
When dealing with data formatted in a different style than the user's local settings, or where the data is unknown, string parsing and use of the International object is required.

To determine what format data is in, string parsing is necessary. Currency and numbers can be in European format, where the period is used as a thousands separator and the comma is used as the decimal point, or in US format where the reverse is true. A European number might look like 1.234,56, where Americans would format this number as 1,234.56. When retrieving data from the clipboard, either by using the Form object or by pasting it in cells, a simple test will tell you which format you are dealing with.

When pasting data into cells, a method to successfully deal with international formats is to format the entire worksheet as text (set a range object to include all cells on the worksheet, and set the range's NumberFormat property to "@"). Pasting the data into a blank, unformatted worksheet will result in Excel interpreting what numbers it can (usually incorrectly), and treating the rest as text. Once the data is pasted, locate a column with values in it. Find a value by walking down the column, preferably looking for a value that has both a thousands separator and a decimal point. By using the Instr function, it is easy to determine which format is presented:

if Instr(strValueIn, ",") < instr(strValueIn, ".") then
    ' must be European
    (do some stuff here)
    Else
    ' must be US
    (do some stuff here)
End if

The next task is to determine what format Excel is expecting. This can be accomplished by querying the Excel International object. The international object will return a number of settings; the setting of interest here is the xlDecimalSeparator value. Acquire this by setting a variable to hold the separator, then retrieve it:

Dim strDelim as string

strDelim = Application.International(xlDecimalSeparator)

Now that the separator is known, the VBA Replace function will work. First, strip out the thousands separator (if the code above determined that it was European, remove all periods from the incoming strings, otherwise remove all commas from the incomming strings). Then replace the remaining character with the decimal character stored in the variable strDelim. Finally, clear the formatting
from the cell where the data was retrieved, and set the cell contents to the reformatted number. The full code might look like this:

Dim wks as Worksheet

dim lngRow as long
dim lngCol as long
 
dim rngR as Range

dim strDelim as String
dim strValue as String

dim boolEur as Boolean    'format flag
dim boolCont as Boolean  'loop flag

' Set a reference for the worksheet

Set wks = Worksheets(1)

'set the Delim character

strDelim = Application.International(xlDecimalSeparator)

' use supplemental logic to find a numeric column
lngCol = (whatever logic you use)

'set the row to the first row of data
lngRow = 2

' Determine if European or US
if Instr(strValueIn, ",") < instr(strValueIn, ".") then
' European
boolEur = true
Else
' US
boolEur = false
End If

' now walk down the column, removing the thousands separator,
' replacing the decimal separator with the known decimal separator,
' then clear the formatting from the cell, and place the value in it.

boolCont = True

do while boolCont = true
    strValue = trim(wks.cells(lngRow, lngCol))
    if boolEur then
        strValue = replace(strValue, ".", "")
        strValue = replace(strValue, ",", strDelim)
        else
        strValue = replace(strValue, ",", "")
        strValue = replace(strValue, ".", strDelim)
    end if
    set rngR = wks.range(wks.cells(lngRow, lngCol).address)
    rngR.ClearFormat
    wks.cells(lngRow, lngCol) = strValue
    lngRow = lngRow + 1
 
   ' test for blank cell
   If trim(wks.cells(lngRow, lngCol)) = "" then
        boolCont = false
    End If

loop

For help with International issues or other Excel topics, call (303) 403-0386 or E-mail us.


Copyright 2012 Front Range Data Management Services