|
在VB或VBA中经常使用代码来控制单元格的显示格式，如前面文章所述，用Cells(m, n).NumberFormatLocal = "@"可以控制第m行第n列的单元格格式为文本，若在该单元格内输入数字，则显示为文本，在Excel2003以后的版本中，单元格右上角有一个绿色三角。除了文本可以用代码，其它所有格式都可以用代码显示，大约有五六十种，以下代码以VBA for Excel为例，介绍其主要格式的控制代码。只要将以下代码放在Excel文件的VB编辑器中，如Private sub Workbook_Open()后面，以后只要打开该文件，并启用宏，相应的单元格格式即设定好了，在VB中应用也是一样的。 代码如下： Sheet1.Cells(1, 1).NumberFormatLocal = "@" Sheet1.Cells(1, 2).NumberFormatLocal = "G/通用格式" Sheet1.Cells(1, 3).NumberFormatLocal = "0" Sheet1.Cells(1, 4).NumberFormatLocal = "0.00" Sheet1.Cells(1, 5).NumberFormatLocal = "#,##0" Sheet1.Cells(1, 6).NumberFormatLocal = "#,##0.00" Sheet1.Cells(1, 7).NumberFormatLocal = "_ * #,##0_ ;_ * -#,##0_ ;_ * ' - '_ ;_ @_ " Sheet1.Cells(1, 8).NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ' - '??_ ;_ @_ " Sheet1.Cells(1, 9).NumberFormatLocal = "_ ￥* #,##0_ ;_ ￥* -#,##0_ ;_ ￥* ' - '_ ;_ @_ " Sheet1.Cells(1, 10).NumberFormatLocal = "_ ￥* #,##0.00_ ;_ ￥* -#,##0.00_ ;_ ￥* ' - '??_ ;_ @_ " Sheet1.Cells(1, 11).NumberFormatLocal = "#,##0;-#,##0" Sheet1.Cells(1, 12).NumberFormatLocal = "#,##0;[红色]-#,##0" Sheet1.Cells(1, 13).NumberFormatLocal = "#,##0.00;-#,##0.00" Sheet1.Cells(1, 14).NumberFormatLocal = "#,##0.00;[红色]-#,##0.00" Sheet1.Cells(1, 15).NumberFormatLocal = "￥#,##0;￥-#,##0" Sheet1.Cells(1, 16).NumberFormatLocal = "￥#,##0;[红色]￥-#,##0" Sheet1.Cells(1, 17).NumberFormatLocal = "￥#,##0.00;￥-#,##0.00" Sheet1.Cells(1, 18).NumberFormatLocal = "￥#,##0.00;[红色]￥-#,##0.00" Sheet1.Cells(1, 19).NumberFormatLocal = "0%" Sheet1.Cells(1, 20).NumberFormatLocal = "0.00%" Sheet1.Cells(1, 21).NumberFormatLocal = "0.00E+00" Sheet1.Cells(1, 22).NumberFormatLocal = "##0.0E+0" Sheet1.Cells(1, 23).NumberFormatLocal = "# ?/?" Sheet1.Cells(1, 24).NumberFormatLocal = "# ??/??" Sheet1.Cells(1, 25).NumberFormatLocal = "$#,##0_);($#,##0)" Sheet1.Cells(1, 26).NumberFormatLocal = "$#,##0_);[红色]($#,##0)" Sheet1.Cells(1, 27).NumberFormatLocal = "$#,##0.00_);($#,##0.00)" Sheet1.Cells(1, 28).NumberFormatLocal = "$#,##0.00_);[红色]($#,##0.00)" Sheet1.Cells(1, 29).NumberFormatLocal = "yyyy'年'm'月'" Sheet1.Cells(1, 30).NumberFormatLocal = "m'月'd'日'" Sheet1.Cells(1, 31).NumberFormatLocal = "yyyy-m-d" Sheet1.Cells(1, 32).NumberFormatLocal = "yyyy'年'm'月'd'日'" Sheet1.Cells(1, 33).NumberFormatLocal = "m-d-yy" Sheet1.Cells(1, 34).NumberFormatLocal = "d-mmm-yy" Sheet1.Cells(1, 35).NumberFormatLocal = "d-mmm" Sheet1.Cells(1, 36).NumberFormatLocal = "mmm-yy" Sheet1.Cells(1, 37).NumberFormatLocal = "h:mm AM/PM" Sheet1.Cells(1, 38).NumberFormatLocal = "h:mm:ss AM/PM" Sheet1.Cells(1, 39).NumberFormatLocal = "h:mm" Sheet1.Cells(1, 40).NumberFormatLocal = "h:mm:ss" Sheet1.Cells(1, 41).NumberFormatLocal = "h'时'mm'分'" Sheet1.Cells(1, 42).NumberFormatLocal = "h'时'mm'分'ss'秒'" Sheet1.Cells(1, 43).NumberFormatLocal = "上午/下午h'时'mm'分'" Sheet1.Cells(1, 44).NumberFormatLocal = "上午/下午h'时'mm'分'ss'秒'" Sheet1.Cells(1, 45).NumberFormatLocal = "yyyy-m-d h:mm" Sheet1.Cells(1, 46).NumberFormatLocal = "mm:ss" Sheet1.Cells(1, 47).NumberFormatLocal = "mm:ss.0" Sheet1.Cells(1, 48).NumberFormatLocal = "[h]:mm:ss" Sheet1.Cells(1, 49).NumberFormatLocal = "g" ...... 限于篇幅，这里就不一一介绍每个格式所代表的意义了，大家可以在做好之后，在相应的单元格中输入数字，如12，按下回车后，内容即发生相应的变化，根据变化后的内容，即可看出是什么格式了。 |
|
|
来自： Alkaid2015 > 《75.VFP狐仙◆》
