Excel displays formula rather than result

Sometimes a bug in Excel results in the application displaying the text of a formula rather than the result of the formula in the spreadsheet. I have not been able to find a pattern of when it does it, but I have some spreadsheets that do this consistently.

The Fix

To get Excel to properly display the result:

• Select the cell.
• Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.")
• Delete the "=" at the beginning of your formula, and hit Enter.
• Insert the "=" back in the formula at the beginning.

That is it. You should now see the result of the calculation in the cell rather than the text of the formula.

Correct negative dates or times in Excel

When Excel shows ##### because the cell has a negative date or time value, make sure that you:

• Verify dates and times are positive values when you’re using the 1900 date system.
• Use a formula to subtract dates or Add or subtract time correctly to avoid negative date or time value results.

• Change negative formula results that are formatted as dates or times to a different number format.

### in Excel

 Possible Reason No. 1

If the cell width is too short, Excel either simply cuts the visible text off, or it flows into the next cell (depending if the next cell has some content in it or not).

Some cell formats (e.g. decimals) can be shortened if a cell is not wide enough to display the entire number. However, some formats (e.g. dates and times) require the cell to be wide enough to display the entire value. If the cell is not wide enough, it will display a row of hashes.

Solution

This problem is easily solved, by altering the cell width. The easiest way to do this is to drag the bar separating the column headers (as shown in the image on the right), until the cell is wider.

Alternatively, if you double-click on this bar, the cell should automatically re-size to fit the contents.

  

Possible Reason No. 2

If Excel continues to display a string of # symbols, no matter how wide you make your column, it is likely that Excel is attempting to display the contents of the cell as a date or time, but the cell contains an invalid date or time value.

As dates and times are stored as positive numeric values within Excel, some values (for example negative values) are invalid as dates or times. Excel shows this by filling the cell with # symbols.

Solution

1. If the cell is intended to contain a date or time, check your formulas. Excel dates and times must be positive numeric values. Note that Excel can not handle negative dates or times.
2. If the cell is not intended to contain a date or time, change the formatting of the cell.
   The quickest way to change a cell's formatting is to select the cell to be formatted and then select the required cell formatting from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):
   

However! If you use this cell as a data-input to, for example, a field in a merged Word document, only the first 256 characters will be grabbed!!!

Email charts using Excel VBA

While I was doing my project, I got a request from the client, to include charts in the mails along with other content which is being shared earlier.

As we all are so much into google, so I did some research on how we can do this.

Following are some nice examples that really helped me.


For simple mail, we all do this

Option Explicit
Private Sub CommandButton1_Click()
    On Error GoTo ErrHandler
    
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = "abc@xyz.com"
        .Subject = "This is a test message"
        .Body = "Hi there"
        .Display     ' DISPLAY MESSAGE.
    End With
    
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
        
ErrHandler:

End Sub

Now for including a chart following are the examples:

Example 1

This example sends a chart with the name "Chart 1" from "Sheet1" of the ActiveWorkbook. It will save My_Sales1.gif in the temp folder, send the mail and delete My_Sales1.gif after that.

Sub SaveSend_Embedded_Chart()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Fname As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'File path/name of the gif file
    Fname = Environ$("temp") & "\My_Sales1.gif"

    'Save Chart named "Chart 1" as gif file
    'If you hold down the CTRL key when you select the chart
    'in 2000-2013 you see the name in the Name box(formula bar)
    ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Export _
            Filename:=Fname, FilterName:="GIF"

    On Error Resume Next
    With OutMail
        .To = "abc@xyz.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add Fname
        .Send   'or use .Display
    End With
    On Error GoTo 0

    'Delete the gif file
    Kill Fname

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Example 2

This example sends a chart sheet with the name "Chart1" from the ActiveWorkbook.
It will save My_Sales2.gif the temp folder, send the mail and delete My_Sales2.gif after that.

Sub SaveSend_Chart_Sheet()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Fname As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    'File path/name of the gif file
    Fname = Environ$("temp") & "\My_Sales2.gif"

    'Save Chart sheet named "Chart1" as gif file
    ActiveWorkbook.Sheets("Chart1").Export _
            Filename:=Fname, FilterName:="GIF"

    On Error Resume Next
    With OutMail
        .To = "abc@xyz.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add Fname
        .Send   'or use .Display
    End With
    On Error GoTo 0

    'Delete the gif file
    Kill Fname

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub