CODEWITHSUNDEEP
vbaexcelexcel-formula
joehua
joehua

Reputation: 735

Formula in worksheet and VBA works differently

Cell A1 contains the number 25, which is right-aligned, implying it's a number, not text.

D1 contains the formula:

="" & A1

The 25 in D1 is left-aligned, implying it's text. That is confirmed by the following formula, which returns 1:

=IF(D1="25",1,0)

The following VBA code puts 25 in F1 but the 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-aligned 25 and the IF test returns 1:

TEXT(A1,"0")

However, the following code gives a right-aligned 25 and the IF test returns 0:

Range("F1") = Application.WorksheetFunction.Text(Range("A1"), "0")

Not that I have to use the TEXT function. I just wonder why it works differently than when in a worksheet.

Any rule that tells when or what worksheet functions won't work in VBA code, or more precisely, will give different results than when in worksheet?

Upvotes: 3

Views: 1015

Answers (4)

L42
L42

Reputation: 19727

Worksheet function when used in the worksheet behaves / works the same way as when used in VBA. Consider below code:

Note: Range("B1") contains a 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 us check the data type of variant v.

SC1: All variables un-initialized All variables not initialized

You can see, at the start that all variables have no value and the variant type v is empty.

SC2: Variables initialized and v assigned a value

Variant v set to Variant/Double

After executing lines up to v = r.value2, all variable types were confirmed (e.g. Range/Range etc.) and variant v is now Variant/Double.

SC3: Re-assign a value on v but using worksheet function Text

enter image description here

Executing the last line which uses the worksheet function Text, variant v type becomes Variant/String. I think this confirms that the function Text works as expected converting the numeric 25 into a string type.

As for the behavior of passing VBA generated value to worksheet, it is covered by Docmarti's post above.

Upvotes: 0

Docmarti
Docmarti

Reputation: 386

When a data is written by vba into a cell, an internal type conversion function is called if required, that is if the data type is different from the cell's numberformat property.

You dont want that conversion function to be called.

To avoid this conversion function to be called, choose the proper Numberformat property for the cell before writing the data.

Range("b4").NumberFormat = "@"
Range("b4") = Application.WorksheetFunction.Text(Range("A1"), "0")

Upvotes: 4

Dirk Reichel
Dirk Reichel

Reputation: 7979

You simply get the wrong idea of what is a number in Excel.

in general ALL input is a string. Also writing "25" in a cell. However: If possible, Excel will convert all inputs to a numerical value if possible. Also for dates and times.

To prevent this, you simply insert a ' in front of your "text" in the cell.

The confusing part for you is the different behavior for formulas. A formula will always output a "result" AND the "data type".

So =1+1 will be numeric as the last action was math. =Left(1+1,1) will be text as the last action was text-based.

For =A1 it will simply copy the type. If there is a formula, then this will be the same. But if there is a "direct input" it will always try to convert to numerical and only be text if it can't be converted or if it starts with a leading ' (A1 itself does this already).

As a result: If there is a plain 25 in the cell, it will always be "numerical" no matter "how" you input the 25.

For newer Excel there is only one exception: if the cell formatting is text prior to entering a number, it will be treated as text (no converting). This does not apply if you change the formatting later.

Simple test:

  • enter 25 in A1 (formatting 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 fail (Excel confuses itself sometimes here). Try it with a new sheet. ;)

Hopefully you understood the fault in your logic ;)

Upvotes: 2

Variatus
Variatus

Reputation: 14373

The cell alignment says nothing about the cell's contents. Forget about anything being "implied" by it. When you start on a virgin worksheet the format for all cells is "General" which means that Excel will decide the format of what you enter. If you enter a number the format will be "Number". If you enter what looks like a date to Excel the format will be "Date", and for most other things the format will be "Text". So, if you enter " 25" in a cell formatted as "General" Excel will recognise this to be a number despite the leading spaces, read it is numeric, and format the cell to the right. This will happen regardless of whether you made the entry by hand or used VBA. You can then proceed to format the alignment as you wish. However, if you enter the number 25 in a cell formatted as Text Excel will recognise the number as text and display it formatted to the left (unless you expressly formatted the horizontal alignment to the right).

The best way to deal with any problems you might encounter in this regard, set the NumberFormat and HorizontalAlignment properties for the cells that you want to write to. You can do that both manually or using VBA.

Upvotes: 0

Related Questions