How to Assign Value to a Cell via VBA without Changing any Format in Excel Formula

In Excel, each cell has a value and a format. The value is the data that the cell contains, such as a number, a text, a date, or a formula. The format is the way that the value is displayed, such as currency, percentage, date, or custom.

When we assign a value to a cell via VBA, we can use the .Value property of the Range object. For example, Range("A1").Value = 100 will assign the value 100 to cell A1. However, this may also change the format of the cell, depending on the value we assign and the existing format of the cell. For example, if the cell A1 has a currency format, and we assign the value 100, the cell will display $100.00. If we assign the value “1234,56”, the cell will display $1,234.56, because Excel will interpret the comma as a decimal separator.

To avoid changing the format of the cell, we need to pre-format the cell as text before assigning the value. This way, Excel will treat the value as a literal text, and not try to convert it to a number or a date. To pre-format the cell as text, we can use the .NumberFormat property of the Range object, and set it to "@". For example, Range("A1").NumberFormat = "@" will format the cell A1 as text. Then, we can assign any value to the cell without changing the format. For example, Range("A1").Value = "1234,56" will display 1234,56 in the cell, without adding a dollar sign or a decimal point.

Procedures

To assign value to a cell via VBA without changing any format in excel formula, we can follow these steps:

1. Identify the cell or range of cells that we want to assign the value to, and store it in a variable. For example, Dim rng As Range Set rng = Range("A1").
2. Format the cell or range of cells as text, using the .NumberFormat property and setting it to "@". For example, rng.NumberFormat = "@".
3. Assign the value to the cell or range of cells, using the .Value property. For example, rng.Value = "1234,56".

Explanation

To illustrate the above steps, let us create a scenario where we want to assign the value “00:00:01.000” to cell B1, without changing the format of the cell. The cell B1 has a custom format of “[h]:mm:ss.000”, which displays the value as hours, minutes, seconds, and milliseconds. If we assign the value directly, using Range("B1").Value = "00:00:01.000", the cell will display 0.000694444, because Excel will convert the text to a decimal number representing the fraction of a day. To avoid this, we need to pre-format the cell as text, and then assign the value. Here is the VBA code that does this:

Sub AssignValueWithoutFormat ()
    'Declare a variable to store the cell
    Dim rng As Range
    'Set the variable to cell B1
    Set rng = Range("B1")
    'Format the cell as text
    rng.NumberFormat = "@"
    'Assign the value to the cell
    rng.Value = "00:00:01.000"
End Sub

After running this code, the cell B1 will display 00:00:01.000, without changing the format of the cell.

Other Approaches

Another way to assign value to a cell without changing the format is to use the .Formula property of the Range object, and prepend the value with an apostrophe ('). The apostrophe tells Excel to treat the value as a text, and not to apply any formatting. For example, Range("B1").Formula = "'00:00:01.000" will display 00:00:01.000 in the cell, without changing the format. However, this method has a drawback: the apostrophe will be visible in the formula bar, and the cell will be marked as having a formula error, because Excel expects a valid formula after the apostrophe. Therefore, this method is not recommended, unless we want to show the value as a text in the formula bar as well.