VBA - Update values in an array with For Each statement

I'm trying yo update my arrays full of numerical values, once I know which Regional settings are set on the computer.

I used this code :

If Application.International(xlCountrySetting) <> 33 Then
    For Each x In Country
        x = Replace(x, ",", ".")
    Next x
Else
    For Each x In Country
        x = Replace(x, ".", ",")
    Next x
End If

When I debug, I see that x has been changed, but later on in code, the values in Array haven't been modified.

I know I can use For i = LBound(Country,1) to UBound(Country,1) but I was wondering if there is a way to do this with the For Each statement.

Any idea?

Solution

The problem in the For Each loop is that x is a separate variable to Country (you had to declare it earlier in your code) and is being set to the value of the current element of Country; it is not sharing the same memory, i.e. it is not "by ref".

So when you replace the character in x you are only changing x and not changing the element in Country.

You can see this by adding Country to a Watch window, and if you step thru your code you will see that as you iterate through the loop the elements of Country do not change.

However when you use a For loop, and use Country(i) = Replace(Country(i), ".", ","), then you are modifying the Country element and so the value changes.