How to read a formatted text view of a cell in Excel

Question

I am using the COM interface for Excel, and I would like to get a formatted textual representation of the cell, not a true base value.

For example, suppose a cell contains a number 1.23456, and the user specified the format of the number with 1 decimal point. Then I would like to read the line "1.2". I know what I can use Range.Text, but this fails in several important ways. Range.Textreturns what the user sees in the sheet view, so if the cell is hidden, an empty string is returned. If the cell width is low, a truncated row is returned. Range.Textalso crashes since it is limited to 1024 characters.

Another use case is when a cell evaluates an error, for example. #DIV/0!, #NAME?, #REF!Etc. I know that I can read Range.Valueand check if the option is a type varError(I use Delphi, in VBA it will be vbError). I can’t figure out how to get a text representation #DIV/0!, etc. It Range.Textreturns this again , but not if the cell is hidden or too narrow.

EDIT

I think the limit on Range.Text is actually 255 characters.

Answer 1

: VBA.
- , , .

    Sub testing()
    Dim oRng As Range
    Dim var As Variant
    Set oRng = Range("a3")
    If IsError(oRng) Then
        var = cstrError(oRng.Value)
    Else
    var = oRng.Value2
    If IsNumeric(var) Then var = Format(var, oRng.NumberFormatLocal)
    End If
    MsgBox Len(var) & " " & var
End Sub
Function cstrError(vError As Variant) As String
    Select Case CLng(vError)
    Case xlErrDiv0
        cstrError = "#DIV/0!"
    Case xlErrNA
        cstrError = "#N/A"
    Case xlErrName
        cstrError = "#NAME?"
    Case xlErrNull
        cstrError = "#NULL!"
    Case xlErrNum
        cstrError = "#NUM!"
    Case xlErrValue
        cstrError = "#VALUE!"
    Case xlErrRef
        cstrError = "#REF!"
    Case Else
        cstrError = "#N/A"
    End Select
End Function

Answer 2

, :

Application.WorksheetFunction.Text(the_cell.Value, the_cell.NumberFormat)

, . :

Application.WorksheetFunction.IsError(the_cell)

, , , Error.Type VBA COM. , .

Answer 3

Thank you very much for your reply and useful comments. Now I have compiled the version of Delphi / COM that I need, which looks like this:

function GetCell(const Sheet: ExcelWorksheet; const Row, Col: Integer): string;

  function ErrorText(const Cell: ExcelRange; hr: HRESULT): string;
  const
    ErrorBase=HRESULT($800A0000);
  var
    i: Integer;
  begin
    Result := Cell.Text;
    for i := 1 to Length(Result) do begin
      if Result[i]<>'#' then begin
        exit;
      end;
    end;
    if hr=ErrorBase or xlErrDiv0 then begin
      Result := '#DIV/0!';
    end else if hr=ErrorBase or xlErrNA then begin
      Result := '#N/A';
    end else if hr=ErrorBase or xlErrName then begin
      Result := '#NAME?';
    end else if hr=ErrorBase or xlErrNull then begin
      Result := '#NULL!';
    end else if hr=ErrorBase or xlErrNum then begin
      Result := '#NUM!';
    end else if hr=ErrorBase or xlErrRef then begin
      Result := '#REF!';
    end else if hr=ErrorBase or xlErrValue then begin
      Result := '#VALUE!';
    end else begin
      Result := 'an error';
    end;
  end;

var
  Cell: ExcelRange;
  hr: HRESULT;

begin
  Cell := GetCellAsRange(Sheet, Row, Col);
  if VarIsError(Cell.Value, hr) then begin
    raise ECellValueError.CreateFmt(
      'Cell %s contains %s.',
      [R1C1toA1(Row,Col), ErrorText(Cell, hr)]
    );
  end else if VarIsNumeric(Cell.Value) then begin
    Result := Sheet.Application.WorksheetFunction.Text(Cell.Value, Cell.NumberFormatLocal);
  end else begin
    Result := ConvertToString(Cell.Value);
  end;
end;