How to write a value to a cell with vba code without automatic type conversion?

Question

This problem seems very simple, but I just can’t find a solution (I’m already losing my mind about it :))

OK, so I just want to put a specific value in an excel cell using vba code, as simple as this:

Cells(1,1).Value2 = "123,456" 

Problem: this is a string (intentionally), but excel always converts it to a number and puts that number in the cell instead of the string I wanted.

How can I get excel not to convert it and just insert exactly what I want in the cell (row)?

Thanks,

Answer 1

 Cells(1,1).Value2 = "'123,456" 

Pay attention to the only apostrophe in front of the number - this will mean that everything subsequent should be interpreted as text.

Answer 2

In fact, just as Tim Williams commented, the way to make it work is formatting as text. Thus, to do this through VBA, simply do this:

 Cells(1, 1).NumberFormat = "@" Cells(1, 1).Value = "1234,56" 

Answer 3

It may be too late, but I had a similar problem with the dates I wanted to enter into cells from a text variable. Inevitably, this converted my variable text value to a date. In the end, I had to concatenate a to a string variable, and then put it in a cell as follows:

 prvt_rng_WrkSht.Cells(prvt_rng_WrkSht.Rows.Count, cnst_int_Col_Start_Date).Formula = "'" & _ param_cls_shift.Start_Date (string property of my class)