VBA does not assign values to cells / ranges correctly

Question

I have a rather simple macro which resets the current worksheet to a predefined starting state. How this is achieved is that certain cell values are first stored in local variables:

Dim callingsheet As String
callingsheet = ActiveSheet.Name

Dim millidtemp As String
Dim eqmtidtemp As String

If callingsheet <> LoadMeasurementTemplateSheetName Then

    millidtemp = millidinputrng.Value
    eqmtidtemp = eqmtidinputrng.Value

End If

Here millidinputrng and eqmtidinputrng are global-scope range variables, both referring only to a single cell. After this, the sheet is completely wiped clean of any values, functions, formatting, etc.

Then the global variables are reset (including millidinputrng and eqmtidinputrng), and after this the sheet is re-initialized based on the predefined start state of that sheet, where millidinputrng and eqmtidinputrng are again assigned their proper ranges.

Finally, I try to reinsert the previously copied temporary values back in the correct ranges:

If callingsheet <> LoadMeasurementTemplateSheetName Then

    millidinputrng.Value = millidtemp
    eqmtidinputrng.Value = eqmtidtemp

End If

However, the assignment somehow fails. The local variable eqmtidtemp clearly shows that the value stored in it is "753362.001", which is correct. But after assigning that value back to the range, the copied value in the cell is "753362,001" - i.e. the dot changed to a comma. The same happens if I rerun the macro: again, the value is copied correctly to the local variable, but during reassignment, it then changes to "753362001", dropping the comma altogether.

Am I missing something about assigning values from variables to ranges / cells here? Why aren't the values in local variables copied to the ranges / cells exactly as they are? I tried forcing the format of the cells to "General", but with no luck.

EDIT --

As FaneDuru and Ron Rosenfeld pointed out, my assumption that the number format of the receiving range would be determined implicitly by the type of the temporary variables - string in this case - was wrong. Excel interpreted these values as numeric and stored the values in my local numeric format, with comma as decimal separator. This fixed the problem:

If callingsheet <> LoadMeasurementTemplateSheetName Then

    millidinputrng.NumberFormat = "@"
    eqmtidinputrng.NumberFormat = "@"

    millidinputrng.Value = CStr(millidtemp)
    eqmtidinputrng.Value = CStr(eqmtidtemp)

End If

What is your setting for decimal separator? Perhaps try millidtemp = millidinputrng.text. — SJR
Try, please: millidinputrng.Value = CStr(millidtemp). What format would you like to be in the cell? Text or General? — FaneDuru
@FaneDuru: I'm trying to force the values to be strings. I was hoping that declaring the temporary variables as strings would implicitly cause them to be also stored as strings during the reassignment. Could the case be that since the IDs here just happen to be numeric in nature, that during assigning the values to the ranges, VBA somehow auto-converts the string values in the temporary variable into a number format, which then changes to my local decimal display format? — Veriticus
Even if it is a string, if the format of that string matches a known number format on your worksheet, Excel will convert it to a number. If you want it to be placed as a string, you must format the target cell as Text (or @) before you paste the value. — Ron Rosenfeld
Then, you must preliminary change the cell format millidinputrng.NumberFormat = "@", followed by your code millidinputrng.Value = CStr(millidtemp). It should work in this way. — FaneDuru

NautMeg NautMeg · Accepted Answer · 2020-03-03T10:13:34

It sounds as though the number format of the cell is what is changing. What I would do is a debug.print before and after variable assignment. If the value in the console is the same before and after, then the change can only be visual. Try this:

debug.print(millidtemp)
millidinputrng.Value = millidtemp
debug.print(millidinputrng.Value)

And then check the console to see if the two values are both "753362.001".

VBA does not assign values to cells / ranges correctly

1 Answers