Formula in worksheet and VBA works differently
Cell A1 contains the number 25, which is right aligned, which means its number, not text.
D1 contains the formula:
= "" and A1
25 in D1 is left aligned, implying its text. This is confirmed by the following formula, which returns 1:
= IF (D1 = "25", 1.0)
The following VBA code puts 25 in F1, but 25 is right-aligned and the IF test returns 0:
Range("F1") = "" & Range("A1")
Any explanation for the discrepancy?
E1 contains the following formula, which gives a left alignment of 25, and the IF test returns 1:
TEXT (A1, "0")
However, the following code gives right-aligned 25 and the IF test returns 0:
Range("F1") = Application.WorksheetFunction.Text(Range("A1"), "0")
Not that I was using a function TEXT
. I'm just wondering why this works differently than when on a sheet.
Any rule that tells when or what functions of a worksheet will not work in VBA code, or more accurately, will give different results than when on a worksheet?
source to share
When vba writes data to a cell, the internal type conversion function is called if necessary, that is, if the data type is different from the cell's numberformat property.
You don't want the transform function to be called.
To avoid calling this transform function, select the correct Numberformat property for the cell before writing the data.
Range("b4").NumberFormat = "@"
Range("b4") = Application.WorksheetFunction.Text(Range("A1"), "0")
source to share
You are just mistaken for what a number is in Excel.
in general ALL input is a string. Also write "25" in the cell. However: if possible, Excel will convert all inputs to a numeric value if possible. Also for dates and times.
To prevent this, you simply paste '
in front of your "text" in the cell.
The confusing part for you is the different behavior for formulas. The formula will always output "result" and "data type".
So, it =1+1
will be numeric, since the last action was mathematical.
=Left(1+1,1)
will be text-based since the last action was text-based.
For =A1
it will just copy the type. If there is a formula, it will be the same. But if there is a "direct input" it will always try to convert to numeric and only text if it cannot be converted or if it starts with a leading '
( A1
it does it already).
As a result: if there is a simple cell in 25
it, it will always be "numeric" no matter how you enter 25.
There is only one exception for new Excel: if the cell formatting is text before entering a number, it will be treated as text (no conversion). This does not apply if you change the formatting later.
Simple test:
- type
25
in A1 (formatting in general) - enter
=ISNUMBER(A1)
in A2 (will be TRUE) - set formatting for A1 to "text" (A2 will still be TRUE)
- enter
25
in A1 (now A2 will become FALSE)
This may end up with an error (Excel confuses itself sometimes). Try this with a new sheet.;)
I hope you understood the error in your logic;)
source to share
Cell mating does not say anything about the contents of the cell. Forget what they "mean". When you start with a virgin worksheet, the format for all cells is "common", which means Excel will determine the format of the input. If you enter a number, the format will be "Number". If you enter what looks like a date in Excel, the format will be "Date" and for most other formats it will be "Text". So, if you enter "25" in a cell formatted as "General", Excel recognizes this number despite the leading spaces, read it numeric and format the cell to the right. This will happen regardless of whether you record manually or use VBA. Then you can continue with the alignment formatting as you like. However,if you enter the number 25 in a cell that is formatted as Text Excel will recognize the number as text and display it in the left format (unless you directly formatted the horizontal right alignment).
The best way to deal with any problems that you might encounter in this respect, to set the properties NumberFormat
and HorizontalAlignment
for the cells that you want to record. You can do it either manually or using VBA.
source to share
The worksheet function, when used in a worksheet, behaves / works the same as when used in VBA. Consider below code:
Note: Range ("B1") contains numeric value 25
Dim r As Range, v As Variant
Dim wf As WorksheetFunction: Set wf = Application.WorksheetFunction
With Sheet1
Set r = .Range("B1")
v = r.Value2
v = wf.Text(r.Value2, "0")
End With
Now, using the local window, let's check the data type of the variant v
.
SC1: All variables, not initialized
At the beginning you can see that all the variables have no value and the variant type is v
empty.
SC2: Variables initialized and v
assigned a value
After executing the lines before, v = r.value2
all variable types have been confirmed (eg, Range/Range
etc.), and the option is v
now Variant/Double
.
SC3: Reassign a value v
but using a worksheet functionText
By executing the last line that uses the worksheet function Text
, the type v
becomes Variant/String
. I think this confirms that the function is Text
working as expected, converting numeric 25
to string type.
As for the behavior of passing the VBA
generated value to the worksheet, it is covered by Docmarti's post above.
source to share