第5章 修改选区格式
在录入数据后，常常需要根据不同需求在不改变原数据的前提下让单元格显示不同信息，或者对其部分字符突出显示。本章通过8个实例讲解格式转换方面的运用。
● 实例33修改日期格式
● 实例34将零值替换为空
● 实例35将区域数据改成以“万”为单位
● 实例36将“#”号以上标显示
● 实例37修改任意字符为上标
● 实例38为任意字符添加下划线
● 实例39在任意字符上方添加着重符
● 实例40数据重排
实例33 修改日期格式
【技巧说明】 将日期列之数据格式转换为长日期格式以简化输入。
【案例介绍】 输入日期时为了提升效率，可以输入短日期格式如“6-11”，但让它显示时却是长日期格式，如“2007年6月25日星期一”，如图2.9所示。
图2.9 仓库进出表
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE环境。
[2] 使用快捷键Ctrl+R，显示工程资源管理器。
[3] 双击左边列表中的“进出表”，打开工作表代码窗口。
[4] 在右边代码窗口输入以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column=1 And Target.Count=1 Then
Target=Application.WorksheetFunction.Text(Target.Text, "yyyy""年"
"m""月""d""日"" [$-804]aaaa")
End If
End Sub
[5] 关闭VBE窗口返回到工作表。
[6] 在A列输入“6-15”格式之数据，单元格将返回“2007年6月15日 星期五”，而在其他列输入则忽略。
提示
本实例参见光盘样本：..\第2部分\实例33.xlsm。
【相关知识说明】
Target.Column=1 And Target.Count=1：表示当前激活的单元格只有一个而且在第一列。其他列输入数据时忽略。
实例34 将零值替换为空
【技巧说明】 将选区中的零值替换为空。
【案例介绍】 如图2.10所示，仓库进出表中未入库或者未出库时数据为0，需要将零值替换为空。
图2.10 仓库进出表
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub将零值替换为空()
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole
End Sub
[4] 选择A1:E8区域，使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，区域中的“0”已全部转换成空。
提示
本实例参见光盘样本：..\第2部分\实例34.xlsm。
【相关知识说明】
Replace：替换字符的函数。语法如下：
Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
其中，第一个参数表示替换的目标，第二个参数表示替换后的新值，第三个参数表示字符匹配方式。本例表示查找值为“0”，替换值为空（符号""），必须完全匹配才替换。
实例35 将区域数据改成以“万”为单位
【技巧说明】 将区域数据改成以“万”为单位。
【案例介绍】 如图2.11所示，当工作表中数值太大时，不太方便阅读和比较。现需将该区域数值格式改成以“万”为单位显示，但不修改原值。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
图2.11 销售表
[3] 在右边代码窗口输入以下代码：
Sub将区域数据改成以万为单位()
Selection.NumberFormatLocal="#"".""#,万"
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选择B3:F6区域，使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，结果如图2.12所示。
图2.12 以“万”为单位的销售表
提示
本实例参见光盘样本：..\第2部分\实例35.xlsm。
【相关知识说明】
NumberFormatLocal：单元格数字格式，可读/写。
实例36 将“#”号以上标显示
【技巧说明】 将区域中的“#”号以上标显示。
【案例介绍】 在输入机台号时，按照中国人的习惯，“#”号应该以上标显示——5#，如图2.13所示。然而，每输入一次机台号都进行格式设置显然效率太低，本例代码可以批量将指定的任何字符在瞬间以上标标示。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub标示上标()
Dim TRan As Range, FirstAddress As String, FindStr As String, i, j, k, l
FindStr="#" '标上上标的字符串
With Selection
Set TRan=.Find(FindStr, LookIn:=xlValues, lookat:=xlPart) '设定查找值
If Not TRan Is Nothing Then '如果找到
FirstAddress=TRan.Address '记录地址
Do '开始循环
i=(Len(TRan.Value) –
Len(WorksheetFunction.Substitute(TRan.Value, FindStr, ""))) / Len(FindStr)
k=1
For j=1 To i
l=WorksheetFunction.Find(FindStr, TRan.Value, k)
TRan.Characters(Start:=l,
Length:=Len(FindStr)).Font.Superscript=True '标示上标
k=l+Len(FindStr)
Next
'查找下一个
Set TRan=.FindNext(TRan)
Loop While Not TRan Is Nothing And TRan.Address <> FirstAddress
'直到返回第一个地址
End If
End With
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选择区域，使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，结果如图2.14所示，所有“#”号都在瞬间以上标显示。
图2.13 产量表
图2.14 “#”号上标显示后的工作表
提示
本实例参见光盘样本：..\第2部分\实例36.xlsm
【相关知识说明】
（1）Len：返回字符串中字符的数目，或是存储一个变量所需的字节数。
（2）For…Next：以指定次数来重复执行一组语句，达到循环检测目的。语法如下：
For counter=start To end [Step step]
（3）Do...Loop：当条件为True时，或直到条件变为True时，重复执行一个语句块中的命令。语法如下：
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop
或者可以使用下面这种语法：
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]
实例37 修改任意字符为上标
【技巧说明】 将区域中的指定字符显示为上标。
【案例介绍】 本例在实例36中变化而来，适用于当需求上标显示之字符不限制为“#”时的情况。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub将任意字符标示为上标()
Dim r As Range, i%, First$, inputt
inputt=InputBox("上标对象", "请输入加上标之对象", "#")
Application.ScreenUpdating=False
Set r=Cells.Find(inputt, lookat:=xlPart) 'xlPart表示单元格不用完全匹配
If Not r Is Nothing Then '当找到时
First=r.Address '用First记录下第一个单元格的地址
Do '查找下一个循环过程
For i=1 To Len(r) '对找到的单元格，从第一个字符到最后一个字符
If Mid(r, i, 1)=inputt Then '假如是inputt指定字符时，则设置它为上标
r.Characters(Start:=i, Length:=1).Font.Superscript=True
End If
Next
Set r=Cells.FindNext(r) '在找到的单元格之后，查找新一个单元格
Loop Until r.Address=First
'重复过程，直到最后找到的单元格的地址等于第一个单元格的地址
End If
Application.ScreenUpdating=True
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选择区域，使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，程序将弹出输入符号之对话框，如图2.15所示，默认为“#”，可以输入任意字符，本例输入“业”字。
[6] 程序执行完毕后，指定的字符全部以上标显示，如图2.16所示。
图2.15 提示输入字符之对话框
图2.16 标示上标后的工作表
提示
本实例参见光盘样本：..\第2部分\实例37.xlsm。
【相关知识说明】
InputBox：在一个对话框中显示提示，等待用户输入正文或单击按钮，并返回包含文本框内容的String。语法如下（[]符号内的表示可选参数，其余的为必选参数）：
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
其中，prompt表示显示在对话框中的字符串；title表示对话框标题；default表示输入字符的默认值；xpos和ypos表示对话框的XY坐标；最后两个参数指定帮助文件，一般不用。
实例38 为任意字符添加下划线
【技巧说明】 为任意字符添加下划线。
【案例介绍】 为任意指定的字符添加下划线。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub为任意字符加下划线()
Dim r As Range, i%, First$, inputt, indexx
inputt=InputBox("下划线对象", "请输入加下划线之对象", "#")
Set indexx=Application.InputBox("请输入欲加下划线之单元格区域,也可以用
鼠标选择", "定位", "A1", , , , , 8)
Application.ScreenUpdating=False
Set r=indexx.Find(inputt, lookat:=xlPart)
If Not r Is Nothing Then
First=r.Address
Do
For i=1 To Len(r)
If Mid(r, i, 1)=inputt Then
r.Characters(Start:=i,Length:=1).Font.Underline=xlUnderlineStyleSingle
End If
Next
Set r=indexx.FindNext(r)
Loop Until r.Address=First
End If
Application.ScreenUpdating=True
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，程序将弹出输入下划线对象之对话框，如图2.17所示，默认为“#”，可以输入任意字符，本例输入“业”字。
[6] 程序再次弹出对话框，询问需要添加下划线之区域。可以手工输入地址，也可以用鼠标选择，如图2.18所示。
图2.17 输入下划线对象之对话框
图2.18 选择区域对话框
[7] 程序执行完毕后，指定的字符全部加上下划线，如图2.19所示。
图2.19 已添加下划线之数据
提示
本实例参见光盘样本：..\第2部分\实例38.xlsm。
【相关知识说明】
Underline：返回或设置应用于字体的下划线类型。
实例39 在任意字符上方添加着重符
【技巧说明】 在任意字符上方添加着重符。
【案例介绍】 Excel本身可以在字符下方添加着重符，本例介绍在文字上方添加着重符之技巧。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub在任意字符上方添加着重符()
Dim r As Range, i%, First$, inputt
inputt=InputBox("输入欲加着重符之字符", "定位", "#", 50, 50)
Application.ScreenUpdating=False
Set r=Selection.Find(inputt, lookat:=xlPart)
If Not r Is Nothing Then
First=r.Address
Do
For i=1 To Len(r)
If Mid(r, i, 1)=inputt Then
r.Phonetics.Visible=True
r.Characters(Start:=i, Length:=1).PhoneticCharacters="·"
r.Phonetics.Font.Size=r.Font.Size+2
r.Phonetics.Font.Name="黑体"
Else
r.Characters(Start:=i,Length:=1).PhoneticCharacters=""
r.Phonetics.Alignment=xlPhoneticAlignCenter
End If
Next
Set r=Selection.FindNext(r)
Loop Until r.Address=First
End If
Application.ScreenUpdating=True
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中目标区域，使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，程序将弹出输入欲加着重符之字符对话框，本例输入“业”字。
[6] 程序执行完毕后，指定的字符全部加上着重符，如图2.20所示。
图2.20 已添加上着重符之数据
提示
本实例参见光盘样本：..\第2部分\实例39.xlsm。
【相关知识说明】
（1）PhoneticCharacters：返回或设置指定Characters对象中的拼音文本，可读/写。
本实例中利用拼音指南功能将字符的拼音设置为着重符从而变相达到目的。
（2）xlPhoneticAlignCenter：表示拼音对齐方式为居中。
实例40 数据重排
【技巧说明】 用工作表函数对A列数据重新排序。
【案例介绍】 Excel自身的排序功能是在原列中排序，本例将数据产生在另一列并可选择“升”序或者“降”序。
【案例实现】 参见以下步骤：
[1] 使用快捷键Alt+F11进入VBE（Visual Basic Editor）环境。
[2] 单击菜单【插入】\【模块】，打开模块代码窗口。
[3] 在右边代码窗口输入以下代码：
Sub对A列数据排序()
Dim ans As Byte, anss As Byte, a(1 To 50) As Integer, i As Byte
ans=InputBox("请选择:1为升序,2为降序", "选项", 1, 10, 10)
anss=InputBox("请选择新数据产生在哪一列" & Chr(10) & "只能输入数字。", "选
项", 2, 10, 10)
For i=1 To 50
a(i)=ActiveSheet.Cells(i+1, 1)
Next
Dim b(1 To 50)
For i=1 To 50
If ans=1 Then b(i)=Application.WorksheetFunction.Small(a, i)
If ans=2 Then b(i)=Application.WorksheetFunction.Large(a, i)
Next
For i=1 To 50
ActiveSheet.Cells(i+1, anss)=b(i)
Next
ActiveSheet.Cells(1, anss)="重排序"
End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 使用快捷键Alt+F8调出运行宏窗口，然后单击“执行”按钮，程序将弹出对话框询问是按升序还是降序排列，如图2.21所示。
[6] 再次弹出对话框询问新数据产生在哪一列，如图2.22所示。
图2.21 选择排序方式
图2.22 选择新数据存放位置
[7] 输入后返回结果如图2.23所示。
图2.23 排序后的数据
提示
本实例参见光盘样本：..\第2部分\实例40.xlsm。
【相关知识说明】
（1）Small：工作表函数，需加前缀WorksheetFunction使用，返回第N小的值。
（2）Large：工作表函数，返回第N大的值。