VBA設計Scripting.Dictionary建立字典物件,程式執行Excel VLOOKUP函數功能

目錄

Excel的VLOOKUP函數其實就是個迷你微程式,本文介紹先錄製巨集,進入VBA瞭解VLOOKUP公式執行時的原始程式碼,進而利用特殊的字典物件CreateObject(“scripting.dictionary”),設計VBA程式執行VLOOKUP任務。

通常很厚一本的計算機導論,裡面有個章節是介紹計算機語言。最低階的機器語言,純粹由零和一所組織的世界,基本元素是像個大燈泡的真空管,以機械式地一亮一滅,傳遞著零與一的訊息,雖然單調無比,但是兩儀生萬象,組合起來卻是無窮奧妙,如今資訊時代的全球資訊網,便是以這樣的方式一磚一瓦發展而成。然而,機器語言實在太純粹了,正常人類看不懂,所以有後來的高階語言,它近似於人類的語言文法,由是與否的邏輯思維架構,每個複合命令用簡單的英文單詞表達,容易理解、因此也容易學習和使用。軟體工程師便是靠各式各樣的高階語言,讓計算機運作起來。
Excel在螢幕上所看到的工作簿工作表,所做的複製、貼上、函數、樞紐等工作,這些Excel作業背後,是一連串微軟工程師寫出來的程式編碼,非資工系畢業的人,很難進入這個領域、看穿Excel背後運作的原理,在運作上難免受到限制。對此,微軟貼心提供了巨集功能、並且外掛VBA編輯器,通過這兩個法寶,用比較直接的方式操作Excel,具體範例分享如下:

一、VLOOKUP函數

進貨明細表,存貨編碼原則第一碼是會科分類,以VLOOKUP函數帶出分類。

二、錄製巨集

先把所有函數公式清除,在「開發人員」頁籤中的「程式碼」,開始「錄製巨集」。

三、錄製巨集選項

「錄製巨集」視窗,此部份於《會計人的Excel小教室》已有介紹,不再細述。

四、進入VBA

正常輸入完VLOOKUP函數,再於「開發人員」頁籤中的「程式碼」,先是「停止錄製」,接著是打開「Visual Basic」。

五、VBA中的VLOOKUP

雙撃點開「專案-VBAProject」中的「模組」「Module1」,可以看到剛才操作的步驟,在計算機高階語言是如何呈現。

六、插入模組

以VBA程式跑VLOOKUP的程式,在VBE中的「插入」頁籤,點選「模組」。

七、VBA程式

輸入VBA程式,如圖所示。

Sub VLOOKUP_VBA()
Arr = [H1:I4]
Set dic = CreateObject(“scripting.dictionary”)
For i = 2 To 2
Set dic(Arr(1, i)) = CreateObject(“scripting.dictionary”)
For j = 2 To 4
dic(Arr(1, i))(Arr(j, 1)) = Arr(j, i)
Next
Next
For i = 4 To 4
For j = 2 To 7
Cells(j, i) = dic(Cells(1, i).Text)(Cells(j, 3).Text)
Next
Next
End Sub

八、執行巨集

離開VBE後台,回到Excel前台,同樣是在「開發人員」的「程式碼」中,點選「巨集」。

九、執行VBA程式

選擇剛才所編寫的「VLOOKUP_VBA」,按「執行」。

十、VBA執行VLOOKUP函數

程式碼發揮神通,已經有了分類了,仔細看,儲存格內容是文字、並非數值,表示是VBA寫上的,不是函數公式帶出來的。

VBA跟電影《駭客任務》一樣看穿Excel原始碼

電影《駭客任務》中,遙遠的未來世界被電腦所控制,為了獲取能量,中央電腦養了一個又一個的桶中大腦,創造出電子虛擬世界,讓桶中大腦在裡面接受刺激和反應,由此產生能量。如同電影情節所詮釋,你看到吃到一個牛排,追根究底只是一連串零與一。而電影裡面的救世主,最後能在虛擬的世界裡飛天鑽地,有大神通,就是因為終於能看穿那些程式。在Excel世界裡,既然微軟提供了這麼方便的巨集程式,透過錄製巨集,我們可以看穿每一種Excel操作功能的程式碼,運用這個法寶,於VBA的學習探索是大有助益。

相關文章