Excel VBA 基礎(03.3) - Excel對象模型之Range (三）

由於審計外勤，專欄停更了兩周，在這裡再次跟大家表示抱歉。目前由於工作關係，更新頻率不能保證，請見諒。

最近有朋友私信我，問到用VBA如何實現網頁類似的複雜交互，並且最近知乎上出現的利用VBA寫交易系統的文章。雖然作為圖靈完備的編程語言VBA可以實現其他圖靈完備語言的所有功能，但是每種技術都有其相應的適用領域及範圍。譬如 共享單車的 定位就是 「最後一公里」，Excel的定位無疑是數據處理與分析領域的"最後一公里"，而前期大量的交互，錄入，匯總工作都交給ERP系統。你固然可以 作為一項人生壯舉 騎共享單車 從北京跑到上海，但這並不意味著這種舉動有任何 現實方面的 價值。類似的，個人覺得作者的精神值得欽佩，這是一個不錯的練手項目，畢竟一個沒有實時數據更新的交易系統 並且 在響應時間以毫秒計的當下，Excel是遠遠無法勝任的。

接著上期文章，本期要把Range部分講完。

（一）.End 與 獲取工作表中最末一行

實際項目中經常要獲取目標工作表中非空白最後一行的行號，最簡便的方法是

Cells(Rows.Count, col).End(xlUp).Row

其中 col 為指定列數。

.End（方向常量）

對應， 按下End鍵 之後再按下對應方向鍵的操作

上述例子中col 為1，即為第一列，即A列

Rows.Count 即當前工作表中總列數，當我們選中A12後按下End, 再按下向下方向鍵時如圖所示，活動單元格移動到第1048576，即Rows.Count的值。

Cells(Rows.Count, col) 即為 單元格A1048576。

.End(xlUp) 表示活動單元格為 A1048576 時按下End, 再按下向上方向鍵時所到達的單元格。本例中為A12,

.Row 取得A12所在行號，即12，亦即最後一行非空單元格行號。

其中方向常量有

xlUpxlDownxlToLeftxlToRight

上述4個方向常量分別為 上、下、左、右

Excel中的系統常量以 xl 開頭 （Word中，類似地 為 wd）。還記得之前所說的常量定義么？所謂常量一定是對應某個不可改變的數值。

在 立即窗口中 測試

? xlUp-4162

將上例中的方向常量替換為數值，等價為

Cells(Rows.Count, col).End(-4162).Row

系統常量的意義在於，與相關數值相比 常量名稱更加直觀。

請大家考慮，用類似方法獲取最後一列的列號VBA代碼應該如何表示

【採用此方法獲取最末行列號時請注意，所有單元格處於非隱藏狀態，否則會跳過隱藏的單元格。】

接下來說一說Range對象的常用方法

（二）單元格排序 Sort

3.3.1 上例中

* 按 日期升序 排序。

* 日期相同的 金額降序 排列。

* 總行數以及列數未知。已知 日期以及金額位於A B 兩列。

* 要求排序之後保留原格式。

Option ExplicitSub main() sort End Sub 保留原格式的排序Function sort() 分別對應 兩鍵 所在的列 Dim keyCol1 As Long Dim keyCol2 As Long keyCol1 = 1 keyCol2 = 2 最末行數 Dim y As Long y = Cells(Rows.Count, 1).End(xlUp).Row 最末列數 Dim x As Long x = Cells(1, Columns.Count).End(xlToLeft).Column 排序的目標區域 Dim targRng As Range Set targRng = Range(Cells(1, 1), Cells(y, x)) 當前工作表名稱 Dim targShtName As String targShtName = ActiveSheet.Name 關掉 屏幕更新以及警告提示 涉及工作表操作的時候提高效率 With Application .ScreenUpdating = False .DisplayAlerts = False End With 增加新工作表， 將目標區域格式 暫存在新工作表當中 Worksheets.Add after:=Worksheets(Worksheets.Count) targRng.Copy Worksheets(Worksheets.Count).Cells(1, 1) 增加工作表後 會自動 選中新工作表 因此需要指定原目標工作表 進行排序操作 With Worksheets(targShtName) targRng.sort key1:=.Range(.Cells(1, keyCol1), .Cells(y, keyCol1)), key2:=.Range(.Cells(1, keyCol2), .Cells(y, keyCol2)), order2:=xlDescending, Header:=xlYes End With 從暫存工作表中 拷貝格式 Worksheets(Worksheets.Count).UsedRange.Copy targRng.PasteSpecial xlPasteFormats 刪除暫存工作表中 此處會引發警告 如不關提示會中斷程序執行 Worksheets(Worksheets.Count).Delete 確保重新選中目標工作表 Worksheets(targShtName).Activate 打開 屏幕更新以及警告提示 With Application .ScreenUpdating = True .DisplayAlerts = True End With End Function

Range.sort 本身的用法簡單。由於涉及到參數較多，請務必具體指定到參數名

targRng.sort key1:=.Range(.Cells(1, keyCol1), .Cells(y, keyCol1)), key2:=.Range(.Cells(1, keyCol2), .Cells(y, keyCol2)), order2:=xlDescending, Header:=xlYes

形如

key1 := 值

指定到參數名時不必局限於函數簽名中所定義的各參數的先後順序。

完成高階的排序任務，例如按顏色排序，需要用到Worksheet對象中的

.Sort.SortFields.

留待以後詳解

(三) 利用 .GoalSeek 解決循環引用

3.3.2. 試解決以下問題

確定負債總額 上圖C7

• 資產負債表滿足以 資產 = 負債 + 所有者權益 + 損益, 即 =+C5-C7-C8-C9 應當為0， 對應圖中 C10的值
• 負債的平均利率為5%，產生的利息費用又會影響損益。 如負債為100時，損益為 -5, 依此類推
• 已知資產與所有者權益數額，求負債總額

理解問題之後，代碼本身不複雜

.GoalSeek用法

目標單元格.GoalSeek 目標值， 可變單元格

Option ExplicitPublic Sub main() getLiability [C7], [C11]End SubPrivate Function getLiability(ByRef targ As Range, ByRef check As Range) check.GoalSeek 0, targ End Function

查找單元格 Find方法 也比較常用，具體方法可以自行查閱API

Range其他屬性，如邊框， 字體， 顏色，可以通過錄製宏的進行查看，具體方法請參見之前文章，在此不再贅述。

本節示例可以自行完成 實戰一