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