2

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?

Improve this question
4
  • When you say "numerical values", do you mean string representations of numbers? Otherwise it doesn't make much sense trying to replace a character in a numeric value. It would be helpful to show your definition of Country and a sample value that you put into the array. Commented May 28, 2015 at 9:12
  • It is decimal values, and my problem is that regarding Regional Setting these values are either numerical (good match between decimal separator and regional settings) or string (no match). But my problem is that these loops don't have any effect on the actual array values. The replace work just fine. Commented May 28, 2015 at 9:20
  • So your array is of type Variant? Commented May 28, 2015 at 9:22
  • Yup, generally I simply declare it like this Dim Country() and here, I tried both. Commented May 28, 2015 at 9:25

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In situation, I will give an array in function argument with different dimensions and elements count. I need using For Each in this case and validate each element of array.
0

I had a similar problem to this. I needed to change the value of a Collection in VBA while looping through.

Trying to modify the Collection using a for loop brought an error. Then like R3uK’s question shows, when I modified the Collection in a foreach loop the value in the Collection didn’t change.

What I did was use another Collection variable for my final answer. So I would add each value to the new Collection as I looped through. Then I had a Collection of the data I wanted.

So if “Country" was a Collection, my VBA answer would look like this: 

Dim Country As New Collection
Dim finalCountry As New Collection

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

Comments

0

For Each is faster than for loop and can be seen when using large arrays or nested loops. Unfortunately, with For Each there is not a built-in element giving the iteration number so you have to place a counter. It seems as though this defeats the simplicity of the For Each but the speed savings is still realized. With that here is code that will work and retain the speed benefits of the For Each statement:

i = 0
If Application.International(xlCountrySetting) <> 33 Then
    For Each x In Country
        i = i + 1
        Country(i) = Replace(x, ",", ".")
    Next x
Else
    For Each x In Country
        i = i + 1
        Country(i) = Replace(x, ".", ",")
    Next x
End If
Improve this answer

2 Comments

Answers claiming speed improvements should cite or provide evidence
The speed advantage of for each over the for loop in collections is generally known and has previously been addressed several times on stackoverflow as well as other sites.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.