實例28 將選區公式轉換成數值

【技巧說明】 將選擇區域的公式一次性轉換成固定數值，使之不隨引用數據變化而變化。

【案例介紹】 如圖2.1所示，工作表引用了其他工作表的數據進行運算，當其他表的數據改變時將跟隨變化，若其他表被刪除，則本工作表的公式結果將會出錯。現需將該表公式引用轉換成固定數值。

圖2.1 引用其他工作表數據之工作表

【案例實現】 參見以下步驟：

[1] 使用快捷鍵Alt+F11進入VBE環境。

[2] 單擊菜單【插入】\【模塊】，打開模塊代碼窗口。

[3] 在右邊代碼窗口輸入以下代碼：

Sub公式轉為值()
  Dim Rng As Range            '聲明變量
  Set Rng=Application.InputBox("請選取公式單元格區域", "轉換為數值", "a1",
      Type:=8) '選擇區域
  If Rng Is Nothing Then Exit Sub '若點擊取消則退出程序
  Rng=Rng.Value               '公式轉為值
End Sub

[4] 使用快捷鍵Alt+F8調出運行宏窗口，然后單擊“執行”按鈕。

[5] 運行程序時將彈出選擇區域的窗口，此時可以手工輸入待轉換的區域，也可以用鼠標選擇區域，如圖2.2所示。

圖2.2 選擇區域窗口

[6] 單擊“確定”按鈕，程序將所選擇區域之公式全部轉換成數值。可以從編輯欄觀察到，公式已變為固定值，如圖2.3所示。

圖2.3 轉換后的工作表

【相關知識說明】

（1）Application.InputBox函數：本例在輸入單元格地址時可以用InputBox函數實現需求，也可以使用Application.InputBox函數。但InputBox只允許手工輸入字符，且不帶參數校驗功能，即輸入“ABC”等不規范的區域引用時不給予提示。本例引用區域時為了使用方便，利用Application.InputBox函數的Type參數為8，不僅可以使用鼠標選擇區域，還可以通過參數校驗功能確保返回值是有效的單元格引用。Application.InputBox的Type參數返回值見表2.1。

（2）Exit Sub：此語句一般用于中途結束程序。在本例中的作用是：如果在選擇區域框中單擊了“取消”按鈕，則退出程序，如果不用“Exit Sub”，程序將中途彈出錯誤提示。本語句也可以用另一種方式跳出程序的運行——GoTo，即程序改為以下方式將取得同樣的效果：

Sub公式轉為值二()
  Dim Rng As Range            '聲明變量
  Set Rng=Application.InputBox("請選取公式單元格區域", "轉換為數值", "a1",
      Type:=8) '選擇區域
  If Rng Is Nothing Then GoTo endd '若點擊取消則退出程序
  Rng=Rng.Value             '公式轉為值
endd:
End Sub

表2.1 Application.InputBox的Type參數

實例29 將當前區域公式轉換成數值

【技巧說明】 將當前單元格所在區域的公式一次性轉換成固定數值，使之不隨引用數據變化而變化。

【案例介紹】 如圖2.4所示，“成績表”中學號欄引用了“學生學號表”之數據，此時僅僅需要將一班成績區域的公式轉換成值，二班維持不變。

圖2.4 引用其他工作表數據之工作表

【案例實現】 參見以下步驟：

[1] 使用快捷鍵Alt+F11進入VBE環境。

[2] 單擊菜單【插入】\【模塊】，打開模塊代碼窗口。

[3] 在右邊代碼窗口輸入以下代碼：

Sub公式轉為值()
    ActiveCell.CurrentRegion.Copy                        '復制
    ActiveCell.CurrentRegion.PasteSpecial Paste:=xlPasteValues '粘貼數值
End Sub

[4] 關閉VBE窗口返回到工作表。

[5] 用鼠標單擊一班成績區域的任意單元格，然后使用快捷鍵Alt+F8，運行本程序。確定后，程序將所選擇之公式全部轉換成數值，結果如圖2.5所示。

圖2.5 轉換后的工作表

【相關知識說明】

（1）ActiveCell是指當前活動單元格，也是光標所在單元格。如果同時選擇了一個區域，則ActiveCell是指選區左上角單元格。

（2）CurrentRegion表示當前已用區域，是以空行與空列的組合為邊界的區域。如本例中用鼠標點擊A1∶C14區域中任意單元格，ActiveCell.CurrentRegion都表示A1∶C14。

（3）PasteSpecial：將剪貼板中的Range對象粘貼到指定區域中。語法如下：

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

其中，Paste參數表示選擇性粘貼的部分，其參數列表見表2.2。

表2.2 Paste參數列表

實例30 將數字轉換為文本

【技巧說明】 將指定區域中的數字格式轉換為文本格式。

【案例介紹】 輸入人事資料時，若員工身份證號超過15位數字，身份證號碼將被系統格式化為科學記數，如圖2.6所示。此時在單元格中無論如何修改數據都無法正確顯示出身份證號碼，解決辦法就是將區域格式化為文本。

圖2.6 顯示為科學記數的身份證號碼

【案例實現】 參見以下步驟：

[1] 使用快捷鍵Alt+F11進入VBE環境。

[2] 單擊菜單【插入】\【模塊】，打開模塊代碼窗口。

[3] 在右邊代碼窗口輸入以下代碼：

Sub數字轉為文本()
  Selection.NumberFormatLocal="@"
End Sub

[4] 關閉VBE窗口返回到工作表。

[5] 選擇B2∶B3，然后使用快捷鍵Alt+F8，運行本程序。確定后，程序將所選區域中的數字轉換成文本。若再輸入18位數字，則已可以正常顯示，如圖2.7所示。

圖2.7 轉換后的工作表

【相關知識說明】

NumberFormatLocal：表示單元格數字格式，@符號即表示文本格式。

實例31 自動將小寫轉換為大寫

【技巧說明】 自動將B列輸入的小寫字母轉換為大寫。

【案例介紹】 工作表中有多列數據，第一列需要輸入小寫字母，第二列必須是大寫字母，如圖2.8所示。輸入時頻繁開關大寫鎖定鍵顯然會降低輸入效率。本例代碼可將B列字母自動轉換為大寫。

圖2.8 產品編碼表

【案例實現】 參見以下步驟：

[1] 使用快捷鍵Alt+F11進入VBE環境。

[2] 使用快捷鍵Ctrl+R，顯示工程資源管理器。

[3] 雙擊左邊列表中的“產品編碼表”，打開工作表代碼窗口。

[4] 在右邊代碼窗口輸入以下代碼：

Private Sub Worksheet_Change(ByVal Target As Range)
  If IsEmpty(Target) Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Column <> 2 Then Exit Sub
  Application.EnableEvents=False
  Target.Value=UCase(Target.Value)
  Application.EnableEvents=True
End Sub

[5] 關閉VBE窗口返回到工作表。

[6] 在A列輸入小寫字母將顯示為小寫狀態，在B列輸入小寫字母則自動轉換為大寫狀態。

【相關知識說明】

（1）Worksheet_Change：工作表事件的一種。本例中表示當修改工作表中B列某單元格數據時執行相應的程序。

（2）IsEmpty：返回Boolean值，指出變量是否已經初始化。本例中表示單元格為空時不執行后面的代碼。

（3）Exit Sub：表示退出程序。

（4）EnableEvents：指定是否啟用事件。本例中修改小寫字母為大寫前禁用事件，否則將進入死循環，在修改完成后恢復。

（5）UCase：將小寫字母轉換為大寫的函數。

實例32 將英文轉換為首字母大寫

【技巧說明】 將輸入的英文單詞或者人名轉換為首字母大寫。

【案例介紹】 在單元格中輸入英文單詞時，根據國外習慣，應首字母大寫。但輸入時不斷手工切換將影響輸入效率，利用本例代碼可自動轉換。

【案例實現】 參見以下步驟：

[1] 使用快捷鍵Alt+F11進入VBE環境。

[2] 使用快捷鍵Ctrl+R，顯示工程資源管理器。

[3] 雙擊左邊列表中的“產品編碼表”，打開工作表代碼窗口。

[4] 在右邊代碼窗口輸入以下代碼：

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target=WorksheetFunction.Proper(Target.Text)
End Sub

[5] 在任意單元格輸入單詞后，首字母轉換為大寫，其余字母則小寫。

【相關知識說明】

Proper：將英文單詞首字母大寫、其余字母小寫的函數。因為是工作表函數，需要添加前綴“WorksheetFunction”。如果不用工作表函數，也可以用以下VBA方式實現：

Private Sub Worksheet_Change(ByVal Target As Range)
Target=StrConv(Target, vbProperCase)
End Sub