 |
 |
| Physiology? | Figures & Illustrations | Test Questions | Daily Quiz | Calculators | Physiology Tutor | Glossary |
 |
 |
 |
 |
 |
 |
Delphi Code - Microsoft Excel Currency Formatting
Here is a simple Delphi procedure that opens a new Microsoft Excel file (i.e., Excel workbook) and demonstrates how to apply a variety of currency formatting options. The file is then saved. The most relevant parts of the code are highlighted. Comments are included to explain the code. If you copy and paste the code into your program, be sure to change the form and procedure names to match your setup. We recommend that you run the code and open and examine the Excel file generated. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
procedure TForm1.MicrosoftExcelCurrencyFormatting(Sender: TObject);
var
ExcelFileName: String;
ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause
ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_currency_formatting.xlsx'; //replace file name with the name of your file
ExcelApplication := Null;
ExcelWorkbook := Null;
ExcelWorksheet := Null;
try
//create Excel OLE
ExcelApplication := CreateOleObject('Excel.Application');
except
ExcelApplication := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelApplication) = False then
begin
try
ExcelApplication.Visible := True; //set to False if you do not want to see the activity in the background
ExcelApplication.DisplayAlerts := False; //ensures message dialogs do not interrupt the flow of your automation process. May be helpful to set to True during testing and debugging.
//Open Excel Workbook
try
ExcelWorkbook := ExcelApplication.WorkBooks.Add(-4167);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorkbook) = False then
begin
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
try
ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Item
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
//add text to A1 cell [row,column]
ExcelWorksheet.Cells[1,1] := 'Microsoft Excel - Cell Number Formatting - Currency Formatting';
//change the font size in A1 cell
ExcelWorksheet.Cells[1,1].Font.Size := 20;
//make A1 cell font bold
ExcelWorksheet.Cells[1,1].Font.Bold := True;
//change font color to blue
ExcelWorksheet.Cells[1,1].Font.Color := clBlue;
//add text to A3 cell [row,column]
ExcelWorksheet.Cells[3,1] := 'Hold your mouse over cells with numbers to see comments.';
//add text to A5 cell [row,column]
ExcelWorksheet.Cells[5,1] := 'Currency Format';
ExcelWorksheet.Cells[5,1].Font.Bold := True;
ExcelWorksheet.Cells[5,1].Interior.Color := clAqua;
//add text to B5 cell [row,column]
ExcelWorksheet.Cells[5,2] := 'Format Code';
ExcelWorksheet.Cells[5,2].Font.Bold := True;
ExcelWorksheet.Cells[5,2].Interior.Color := clAqua;
//add text to C5 cell [row,column]
ExcelWorksheet.Cells[5,3] := 'Format Description';
ExcelWorksheet.Cells[5,3].Font.Bold := True;
ExcelWorksheet.Cells[5,3].Interior.Color := clAqua;
//add number, code, and description to row 6 [row,column]
ExcelWorksheet.Cells[6,1] := '45';
ExcelWorksheet.Cells[6,1].AddComment('No number formatting specified for entered number (45).');
ExcelWorksheet.Cells[6,3] := 'No number formatting specified for entered number (45).';
//add number, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '45.79';
ExcelWorksheet.Cells[7,1].NumberFormat := '0.00';
ExcelWorksheet.Cells[7,1].AddComment('Number format: 0.00 (two decimals) for entered number (45.79)');
ExcelWorksheet.Cells[7,2] := 'Format code: 0.00';
ExcelWorksheet.Cells[7,3] := 'Number format: 0.00 (two decimals) for entered number (45.79)';
//add number, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '45.79';
ExcelWorksheet.Cells[8,1].NumberFormat := '$0.00';
ExcelWorksheet.Cells[8,1].AddComment('Number format: $0.00 (two decimals) for entered number (45.79). $ sign added. Excel recognizes this as Custom formatting.');
ExcelWorksheet.Cells[8,2] := 'Format code: $0.00';
ExcelWorksheet.Cells[8,3] := 'Number format: $0.00 (two decimals) for entered number (45.79). $ sign added. Excel recognizes this as Custom formatting.';
//add number, code, and description to row 9 [row,column]
ExcelWorksheet.Cells[9,1] := '45.79';
ExcelWorksheet.Cells[9,1].NumberFormat := '$#,##0.00';
ExcelWorksheet.Cells[9,1].AddComment('Number format: $#,##0.00 (two decimals) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[9,2] := 'Format code: $#,##0.00';
ExcelWorksheet.Cells[9,3] := 'Number format: $#,##0.00 (two decimals) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.';
//add number, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := '45.79';
ExcelWorksheet.Cells[10,1].NumberFormat := '$#,##0.0';
ExcelWorksheet.Cells[10,1].AddComment('Number format: $#,##0.0 (one decimal) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[10,2] := 'Format code: $#,##0.0';
ExcelWorksheet.Cells[10,3] := 'Number format: $#,##0.0 (one decimal) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.';
//add number, code, and description to row 11 [row,column]
ExcelWorksheet.Cells[11,1] := '45.79';
ExcelWorksheet.Cells[11,1].NumberFormat := '$#,##0';
ExcelWorksheet.Cells[11,1].AddComment('Number format: $#,##0 (no decimals) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[11,2] := 'Format code: $#,##0';
ExcelWorksheet.Cells[11,3] := 'Number format: $#,##0 (no decimals) for entered number (45.79). $ sign added. Excel properly recognizes this as Currency formatting.';
//add number, code, and description to row 12 [row,column]
ExcelWorksheet.Cells[12,1] := '-45.79';
ExcelWorksheet.Cells[12,1].NumberFormat := '$#,##0.00;[red]$#,##0.00';
ExcelWorksheet.Cells[12,1].AddComment('Number format: $#,##0.00;[red]$#,##0.00 (two decimals) for entered number (45.79). $ sign added. Red for negative numbers. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[12,2] := 'Format code: $#,##0.00;[red]$#,##0.00';
ExcelWorksheet.Cells[12,3] := 'Number format: $#,##0.00;[red]$#,##0.00 (two decimals) for entered number (45.79). $ sign added. Red for negative numbers. Excel properly recognizes this as Currency formatting.';
//add number, code, and description to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := '-45.79';
ExcelWorksheet.Cells[13,1].NumberFormat := '$#,##0.00;($#,##0.00)';
ExcelWorksheet.Cells[13,1].AddComment('Number format: $#,##0.00;($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses for negative numbers. Excel recognizes this as Custom formatting.');
ExcelWorksheet.Cells[13,2] := 'Format code: $#,##0.00;($#,##0.00)';
ExcelWorksheet.Cells[13,3] := 'Number format: $#,##0.00;($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses for negative numbers. Excel recognizes this as Custom formatting.';
//add number, code, and description to row 14 [row,column]
ExcelWorksheet.Cells[14,1] := '-45.79';
ExcelWorksheet.Cells[14,1].NumberFormat := '$#,##0.00_);($#,##0.00)';
ExcelWorksheet.Cells[14,1].AddComment('Number format: $#,##0.00_);($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses for negative numbers. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[14,2] := 'Format code: $#,##0.00_);($#,##0.00)';
ExcelWorksheet.Cells[14,3] := 'Number format: $#,##0.00_);($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses for negative numbers. Excel properly recognizes this as Currency formatting.';
//add number, code, and description to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := '-45.79';
ExcelWorksheet.Cells[15,1].NumberFormat := '$#,##0.00;[red]($#,##0.00)';
ExcelWorksheet.Cells[15,1].AddComment('Number format: $#,##0.00;[red]($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses and red for negative numbers. Excel recognizes this as Custom formatting.');
ExcelWorksheet.Cells[15,2] := 'Format code: $#,##0.00;[red]($#,##0.00)';
ExcelWorksheet.Cells[15,3] := 'Number format: $#,##0.00;[red]($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses and red for negative numbers. Excel recognizes this as Custom formatting.';
//add number, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '-45.79';
ExcelWorksheet.Cells[16,1].NumberFormat := '$#,##0.00_);[red]($#,##0.00)';
ExcelWorksheet.Cells[16,1].AddComment('Number format: $#,##0.00_);[red]($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses and red for negative numbers. Excel properly recognizes this as Currency formatting.');
ExcelWorksheet.Cells[16,2] := 'Format code: $#,##0.00_);[red]($#,##0.00)';
ExcelWorksheet.Cells[16,3] := 'Number format: $#,##0.00_);[red]($#,##0.00) (two decimals) for entered number (45.79). $ sign added. Parentheses and red for negative numbers. Excel properly recognizes this as Currency formatting.';
//add text and formatting to row 18 [row,column]
ExcelWorksheet.Cells[18,1] := 'Specifying the Currency';
ExcelWorksheet.Cells[18,1].Font.Bold := True;
ExcelWorksheet.Cells[18,1].Interior.Color := clSilver;
ExcelWorksheet.Cells[18,2].Interior.Color := clSilver;
ExcelWorksheet.Cells[18,3].Interior.Color := clSilver;
//add number, code, and description to row 19 [row,column]
ExcelWorksheet.Cells[19,1] := '45.79';
ExcelWorksheet.Cells[19,1].NumberFormat := '[$$-en-US]#,##0.00';
ExcelWorksheet.Cells[19,1].AddComment('Number format: [$$-en-US]#,##0.00 (two decimals) for entered number (45.79). $ English (United States).');
ExcelWorksheet.Cells[19,2] := 'Format code: [$$-en-US]#,##0.00';
ExcelWorksheet.Cells[19,3] := 'Number format: [$$-en-US]#,##0.00 (two decimals) for entered number (45.79). $ English (United States).';
//add number, code, and description to row 20 [row,column]
ExcelWorksheet.Cells[20,1] := '45.79';
ExcelWorksheet.Cells[20,1].NumberFormat := '[$USD] #,##0.00';
ExcelWorksheet.Cells[20,1].AddComment('Number format: [$USD] #,##0.00 (two decimals) for entered number (45.79). USD for US Dollar.');
ExcelWorksheet.Cells[20,2] := 'Format code: [$USD] #,##0.00';
ExcelWorksheet.Cells[20,3] := 'Number format: [$USD] #,##0.00 (two decimals) for entered number (45.79). USD for US Dollar.';
//add number, code, and description to row 21 [row,column]
ExcelWorksheet.Cells[21,1] := '45.79';
ExcelWorksheet.Cells[21,1].NumberFormat := '[$CAD] #,##0.00';
ExcelWorksheet.Cells[21,1].AddComment('Number format: [$CAD] #,##0.00 (two decimals) for entered number (45.79). CAD for Canadian Dollar.');
ExcelWorksheet.Cells[21,2] := 'Format code: [$CAD] #,##0.00';
ExcelWorksheet.Cells[21,3] := 'Number format: [$CAD] #,##0.00 (two decimals) for entered number (45.79). CAD for Canadian Dollar.';
//add number, code, and description to row 22 [row,column]
ExcelWorksheet.Cells[22,1] := '45.79';
ExcelWorksheet.Cells[22,1].NumberFormat := '[$AUD] #,##0.00';
ExcelWorksheet.Cells[22,1].AddComment('Number format: [$AUD] #,##0.00 (two decimals) for entered number (45.79). AUD for Australian Dollar.');
ExcelWorksheet.Cells[22,2] := 'Format code: [$AUD] #,##0.00';
ExcelWorksheet.Cells[22,3] := 'Number format: [$AUD] #,##0.00 (two decimals) for entered number (45.79). AUD for Australian Dollar.';
//add number, code, and description to row 23 [row,column]
ExcelWorksheet.Cells[23,1] := '45.79';
ExcelWorksheet.Cells[23,1].NumberFormat := '[$NZD] #,##0.00';
ExcelWorksheet.Cells[23,1].AddComment('Number format: [$NZD] #,##0.00 (two decimals) for entered number (45.79). NZD for New Zealand Dollar.');
ExcelWorksheet.Cells[23,2] := 'Format code: [$NZD] #,##0.00';
ExcelWorksheet.Cells[23,3] := 'Number format: [$NZD] #,##0.00 (two decimals) for entered number (45.79). NZD for New Zealand Dollar.';
//add number, code, and description to row 24 [row,column]
ExcelWorksheet.Cells[24,1] := '45.79';
ExcelWorksheet.Cells[24,1].NumberFormat := '[$EUR] #,##0.00';
ExcelWorksheet.Cells[24,1].AddComment('Number format: [$EUR] #,##0.00 (two decimals) for entered number (45.79). EUR for Euro.');
ExcelWorksheet.Cells[24,2] := 'Format code: [$EUR] #,##0.00';
ExcelWorksheet.Cells[24,3] := 'Number format: [$EUR] #,##0.00 (two decimals) for entered number (45.79). EUR for Euro.';
//add number, code, and description to row 25 [row,column]
ExcelWorksheet.Cells[25,1] := '45.79';
ExcelWorksheet.Cells[25,1].NumberFormat := '[$GBP] #,##0.00';
ExcelWorksheet.Cells[25,1].AddComment('Number format: [$GBP] #,##0.00 (two decimals) for entered number (45.79). GBP for British Pound Sterling.');
ExcelWorksheet.Cells[25,2] := 'Format code: [$GBP] #,##0.00';
ExcelWorksheet.Cells[25,3] := 'Number format: [$GBP] #,##0.00 (two decimals) for entered number (45.79). GBP for British Pound Sterling.';
//add number, code, and description to row 26 [row,column]
ExcelWorksheet.Cells[26,1] := '45.79';
ExcelWorksheet.Cells[26,1].NumberFormat := '[$DEM] #,##0.00';
ExcelWorksheet.Cells[26,1].AddComment('Number format: [$DEM] #,##0.00 (two decimals) for entered number (45.79). DEM for German Deutsche Mark.');
ExcelWorksheet.Cells[26,2] := 'Format code: [$DEM] #,##0.00';
ExcelWorksheet.Cells[26,3] := 'Number format: [$DEM] #,##0.00 (two decimals) for entered number (45.79). DEM for German Deutsche Mark.';
//add number, code, and description to row 27 [row,column]
ExcelWorksheet.Cells[27,1] := '45.79';
ExcelWorksheet.Cells[27,1].NumberFormat := '[$JPY] #,##0.00';
ExcelWorksheet.Cells[27,1].AddComment('Number format: [$JPY] #,##0.00 (two decimals) for entered number (45.79). JPY for Japanese Yen.');
ExcelWorksheet.Cells[27,2] := 'Format code: [$JPY] #,##0.00';
ExcelWorksheet.Cells[27,3] := 'Number format: [$JPY] #,##0.00 (two decimals) for entered number (45.79). JPY for Japanese Yen.';
//add number, code, and description to row 28 [row,column]
ExcelWorksheet.Cells[28,1] := '45.79';
ExcelWorksheet.Cells[28,1].NumberFormat := '[$CNY] #,##0.00';
ExcelWorksheet.Cells[28,1].AddComment('Number format: [$CNY] #,##0.00 (two decimals) for entered number (45.79). CNY for Chinese Yuan.');
ExcelWorksheet.Cells[28,2] := 'Format code: [$CNY] #,##0.00';
ExcelWorksheet.Cells[28,3] := 'Number format: [$CNY] #,##0.00 (two decimals) for entered number (45.79). CNY for Chinese Yuan.';
//add number, code, and description to row 29 [row,column]
ExcelWorksheet.Cells[29,1] := '45.79';
ExcelWorksheet.Cells[29,1].NumberFormat := '[$€-2] #,##0.00';
ExcelWorksheet.Cells[29,1].AddComment('Number format: [$€-2] #,##0.00 (two decimals) for entered number (45.79). € Euro (€ 123).');
ExcelWorksheet.Cells[29,2] := 'Format code: [$€-2] #,##0.00';
ExcelWorksheet.Cells[29,3] := 'Number format: [$€-2] #,##0.00 (two decimals) for entered number (45.79). € Euro (€ 123).';
//add number, code, and description to row 30 [row,column]
ExcelWorksheet.Cells[30,1] := '45.79';
ExcelWorksheet.Cells[30,1].NumberFormat := '#,##0.00 [$€-1]';
ExcelWorksheet.Cells[30,1].AddComment('Number format: #,##0.00 [$€-1] (two decimals) for entered number (45.79). € Euro (123 €).');
ExcelWorksheet.Cells[30,2] := 'Format code: #,##0.00 [$€-1]';
ExcelWorksheet.Cells[30,3] := 'Number format: #,##0.00 [$€-1] (two decimals) for entered number (45.79). € Euro (123 €).';
//add number, code, and description to row 31 [row,column]
ExcelWorksheet.Cells[31,1] := '45.79';
ExcelWorksheet.Cells[31,1].NumberFormat := '#,##0.00 [$€-de-DE]';
ExcelWorksheet.Cells[31,1].AddComment('Number format: #,##0.00 [$€-de-DE] (two decimals) for entered number (45.79). € German (Germany).');
ExcelWorksheet.Cells[31,2] := 'Format code: #,##0.00 [$€-de-DE]';
ExcelWorksheet.Cells[31,3] := 'Number format: #,##0.00 [$€-de-DE] (two decimals) for entered number (45.79). € German (Germany).';
//add number, code, and description to row 32 [row,column]
ExcelWorksheet.Cells[32,1] := '45.79';
ExcelWorksheet.Cells[32,1].NumberFormat := '[$£-809]#,##0.00';
ExcelWorksheet.Cells[32,1].AddComment('Number format: [$£-809]#,##0.00 (two decimals) for entered number (45.79). £ English (United Kingdom).');
ExcelWorksheet.Cells[32,2] := 'Format code: [$£-809]#,##0.00';
ExcelWorksheet.Cells[32,3] := 'Number format: [$£-809]#,##0.00 (two decimals) for entered number (45.79). £ English (United Kingdom).';
//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformat
//https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformats
//https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-input-locales-for-windows-language-packs?view=windows-11
//autofit the width of Column A
ExcelWorksheet.Columns[1].ColumnWidth := 25; //sets width of Column A
ExcelWorksheet.Columns[2].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[3].EntireColumn.AutoFit; //autofits Column A
//reference
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.columnwidth
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit
//select B1 cell
ExcelWorksheet.Cells[2,1].Select; //selects Cell B1
ExcelWorkbook.SaveAs(ExcelFileName);
//Note: If a file with the new name already exists, it overwrites it. Write additional code to address as desired.
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;
ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;
Posted: Saturday, November 26, 2022