文章來(lái)自站內(nèi)生活家 波導(dǎo)終結(jié)者

之前群里討論過(guò)，為什么基礎(chǔ)Excel函數(shù)教程之類(lèi)的會(huì)成為月經(jīng)首頁(yè)熱門(mén)，得到的結(jié)論是：基礎(chǔ)用戶比較多，看到了就想著說(shuō)，以后說(shuō)不定有用吧，點(diǎn)了收藏。

所以更高階一點(diǎn)的東西，比如VBA，討論結(jié)果是不會(huì)有什么熱度，因?yàn)榇蟛糠秩擞X(jué)得說(shuō)一輩子也用不著吧，就不會(huì)點(diǎn)收藏了。

不過(guò)這次正好有征文，Visual Basic是其中一個(gè)選題。光寫(xiě)VB估計(jì)更不會(huì)有人看了，所以還是結(jié)合一下實(shí)際，寫(xiě)寫(xiě)一些簡(jiǎn)單的VBA吧。

一、開(kāi)啟Office當(dāng)中的VBA

首先，VBA的全稱(chēng)是Visual Basic for Applications，本質(zhì)上是嵌入于應(yīng)用程序中，使用VB語(yǔ)法的腳本語(yǔ)言。

腳本語(yǔ)言的優(yōu)勢(shì)是保存即生效，不用編譯。所以調(diào)整一些細(xì)節(jié)的時(shí)候還是很方便的。

不過(guò)，由于VB是微軟自家的東西，給的權(quán)限遠(yuǎn)比LUA這種純外部腳本大得多，所以安全問(wèn)題一直是個(gè)困擾。事實(shí)上，VBS病毒一直都有。

所以想用得順暢，有一些前置步驟是要做的，此處以O(shè)ffice 2007版本為例。

首先，出于安全考慮，OFFICE默認(rèn)是不會(huì)把任何VBA的東西放出來(lái)的，連按鈕都是。

我們先點(diǎn)擊左上角，選擇Excel選項(xiàng)。

來(lái)到自定義，右邊找到開(kāi)發(fā)工具選項(xiàng)卡，找到Visual Basic，拉到你想要的地方去。

如圖，我直接拉到了左上角的快捷欄，點(diǎn)擊這個(gè)按鈕，就進(jìn)入VBA的編輯界面。

另外，默認(rèn)的XLSX格式是不包含宏的，所以還要另存為XLSM格式。

另外，有時(shí)候我們會(huì)遇到腳本無(wú)論如何也沒(méi)反應(yīng)的情況，一般還是OFFICE的安全設(shè)置問(wèn)題。

首先先到選項(xiàng)里，找到信任中心。

把你放含有腳本文件文檔的目錄添加進(jìn)去，就可以了。

另一種方式，直接在宏設(shè)置里，默認(rèn)啟用所有宏。

比較適合文件很多的朋友。

二、Excel妙用之高亮行與列

首先，我們通過(guò)之前添加的按鈕進(jìn)入VBA界面，默認(rèn)會(huì)來(lái)到這么一個(gè)地方。

Sheet1、2、3是三張工作表，可以單獨(dú)擁有獨(dú)立的代碼。

而最后的ThisWorkbook里的代碼，則是整個(gè)文檔共用的。

這里我們用全局通用的Workbook。

雙擊ThisWorkbook這條，默認(rèn)會(huì)產(chǎn)生一個(gè)函數(shù)，這個(gè)函數(shù)是對(duì)象的默認(rèn)函數(shù)Open，我們不需要，等下可以刪除。

這個(gè)例子用到的函數(shù)是SheetSelectionChange，顧名思義，當(dāng)工作表選中區(qū)域改變時(shí)，觸發(fā)此函數(shù)。

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Cells.FormatConditions.Delete

iColor = RGB(127, 127, 0)

With Target.EntireRow.FormatConditions

.Delete

.Add xlExpression, , 'TRUE'

.Item(1).Interior.Color = iColor

End With

With Target.EntireColumn.FormatConditions

.Delete

.Add xlExpression, , 'TRUE'

.Item(1).Interior.Color = iColor

End With

End Sub

代碼如上。

顏色可以在 iColor = RGB(127, 127, 0) 這一句改。

這種效果只能用VBA做，很適合大量只讀數(shù)據(jù)時(shí)避免眼花看錯(cuò)行的情況。

不過(guò)，有一點(diǎn)要注意的是：

VBA做的一切修改，都是無(wú)法撤銷(xiāo)的，所以一定要慎重。就像代碼里，修改了單元格的格式，如果沒(méi)有額外寫(xiě)代碼存儲(chǔ)和恢復(fù)的話，就是無(wú)法還原的。

三、Excel妙用之自定義函數(shù)

首先，自定義函數(shù)必須寫(xiě)在模塊里，所以我們右擊，插入，模塊。

然后，我們寫(xiě)個(gè)簡(jiǎn)單的函數(shù)。

這個(gè)函數(shù)很簡(jiǎn)單，將引用單元格的值取出，作為文本，然后再串上“波導(dǎo)終結(jié)者”這個(gè)字符串，并返回。

VB函數(shù)的返回值賦值方法比較特別，其他大部分語(yǔ)言都是return XXX啥的，就VB是把函數(shù)本身作為一個(gè)變量去賦值。

總之，一個(gè)最簡(jiǎn)單的自定義函數(shù)就這樣成了。

注意，函數(shù)前面要加上Public以方便外部調(diào)用。

回到表中，打個(gè)等號(hào)，敲出自定義函數(shù)前面?zhèn)z字母，可以看到，系統(tǒng)已經(jīng)將自定義函數(shù)自動(dòng)補(bǔ)完。

輸完回車(chē)，搞定。

可以看到，此單元格的內(nèi)容，就是引用單元格的內(nèi)容再串上波導(dǎo)終結(jié)者這個(gè)字樣。

可能有的朋友會(huì)說(shuō)，這函數(shù)也太簡(jiǎn)單了，來(lái)點(diǎn)花哨的吧？

但是，每個(gè)人的具體需求都各有不同，我只能教大家如何弄一個(gè)自定義函數(shù)，具體要做什么，肯定只能依照自己需求去寫(xiě)具體的代碼了。

四、將Excel打造成職場(chǎng)里的真·生產(chǎn)效率工具

以前我在網(wǎng)游公司寫(xiě)腳本，毫無(wú)疑問(wèn)的要跟策劃有非常多的溝通。很多時(shí)候，策劃那邊東西沒(méi)定好，我們這邊就沒(méi)辦法開(kāi)做。

策劃提供的東西如果太模糊，還得回頭跟他重新確認(rèn)。但是要太細(xì)的話，比如涉及到程序核心的一些數(shù)值，很多策劃也懵。

就比如，現(xiàn)在要做一些新怪物，從程序的角度來(lái)講，表里的大幾十個(gè)字段各有各的用處。

從腳本的角度來(lái)講，相關(guān)數(shù)值策劃要是不給，我也不可能自己給你填。

從策劃的角度來(lái)看，其實(shí)對(duì)他們有用的就幾個(gè)：血藍(lán)攻防外觀等。

于是我就用VBA做了一個(gè)小工具。

首先，把所有字段列出來(lái)，默認(rèn)值列出來(lái)，策劃有用到的字段篩選出來(lái)。

點(diǎn)擊左上角的“生成怪物數(shù)據(jù)表”，此時(shí)就會(huì)把這些有用的字段篩選出來(lái)，生成一個(gè)EXCEL表。

策劃只需要照著這個(gè)表里的數(shù)據(jù)填好數(shù)值就行了。

不過(guò)，如果只是策劃填表，那策劃自己也能做，這個(gè)生產(chǎn)效率并沒(méi)有本質(zhì)性的提高。

效率提高的部分，在于一鍵生成刷庫(kù)SQL語(yǔ)句。

點(diǎn)擊生成Insert或者Update數(shù)據(jù)按鈕之后，把刷庫(kù)語(yǔ)句輸出到文本文件里并自動(dòng)打開(kāi)，復(fù)制即可用。

原來(lái)的流程里，每個(gè)策劃提供的數(shù)值格式各不同，當(dāng)然，也不可能一鍵導(dǎo)入，于是每個(gè)案子，腳本都得把策劃給的數(shù)值一個(gè)一個(gè)手動(dòng)填，填完還得核對(duì)，然后再刷庫(kù)試驗(yàn)……

使用了VBA之后，整個(gè)流程從策劃設(shè)計(jì)數(shù)值，到腳本刷庫(kù)成功的耗時(shí)，由原來(lái)的0.5至1天左右，減少至半小時(shí)（根據(jù)策劃自己出數(shù)值的速度決定）。

腳本這邊最麻煩的填數(shù)據(jù)步驟，耗時(shí)由3-5個(gè)小時(shí)縮短至2秒左右，數(shù)據(jù)的正確率為100％（除非策劃自己填錯(cuò)）。

后續(xù)如果數(shù)值有變動(dòng)，甚至是服務(wù)器表結(jié)構(gòu)變動(dòng)（比如新增字段），只需要改一下EXCEL，重新點(diǎn)擊按鈕，耗時(shí)在半分鐘以內(nèi)。

處理這種有規(guī)律的、矩陣形式的數(shù)據(jù)，EXCEL非常擅長(zhǎng)，但是光靠系統(tǒng)函數(shù)或者宏，最多只能做做排序篩選。

我花了2天的工作時(shí)間寫(xiě)這個(gè)腳本，應(yīng)用之后，每天都可以給策劃和腳本節(jié)省幾百小時(shí)的工作時(shí)間，關(guān)鍵的是，準(zhǔn)確率100％。

雖然我離職已久，不過(guò)這個(gè)腳本估計(jì)現(xiàn)在仍然在用吧。設(shè)計(jì)的時(shí)候就已經(jīng)做成通用的，不同項(xiàng)目只要修改表字段、表名，即可通用。

由于代碼應(yīng)用太過(guò)具體，這里就象征性截張圖上來(lái)。

關(guān)鍵的是，代碼不能寫(xiě)死。比如項(xiàng)目最早是A游戲做，字段有50個(gè)，你如果寫(xiě)死50個(gè)，后面B項(xiàng)目的字段不一樣，代碼全部得重寫(xiě)。

這里一定要用循環(huán)以及內(nèi)容判斷，我不管你字段有幾個(gè)，是什么內(nèi)容，反正我就按照規(guī)則，把所有字段用循環(huán)篩選一遍即可。

在EXCEL里面放按鈕也很簡(jiǎn)單。

選項(xiàng)里先把開(kāi)發(fā)工具欄放出來(lái)，插入，底下就有按鈕復(fù)選框等等控件。

點(diǎn)擊設(shè)計(jì)模式，就能像在VB6里面一樣設(shè)計(jì)，之后在按鈕的點(diǎn)擊事件里寫(xiě)代碼即可。

五、在Word里統(tǒng)計(jì)字頻

這里用一個(gè)比較普通的例子：在Word里統(tǒng)計(jì)字頻。

如果你要統(tǒng)計(jì)一個(gè)字或者一個(gè)詞，在一段WORD里面出現(xiàn)的次數(shù)，那么你網(wǎng)上搜方法，肯定都是告訴你查找替換法。

但如果你要把每個(gè)字都做字頻統(tǒng)計(jì)，這種方法就行不通了。

使用代碼，我們可以很輕松的做到。

這里由于是演示，我直接用Msgbox輸出結(jié)果了。

代碼效果就是，選中一段話，然后到VBA窗口里執(zhí)行，這時(shí)候就會(huì)自動(dòng)把所選中的這段文字，每個(gè)字出來(lái)的字頻統(tǒng)計(jì)出來(lái)。

由于只是演示，我就直接做消息框彈出了，有需求的可以另行添加，比如扔EXCEL排序等等。

Sub bdzjz_tongji()

Dim i As Integer

Dim n As Integer

n = Selection.Characters.Count

Dim dict

Set dict = CreateObject('Scripting.Dictionary')

For i = 1 To n

Dim s As String

s = Selection.Characters.Item(i)

If dict.Exists(s) Then

dict.Item(s) = dict.Item(s) + 1

Else

dict.Add s, 1

End If

Next i

Dim d_keys

d_keys = dict.keys

Dim d_items

d_items = dict.items

Dim sOut As String

For i = 0 To UBound(d_keys)

sOut = sOut & d_keys(i) & ' 出現(xiàn)次數(shù)：' & d_items(i) & '次' & vbCrLf

Next

MsgBox sOut

End Sub

代碼用到了字典類(lèi)Dictionary，類(lèi)似哈希表一樣的結(jié)構(gòu)。

將單字本身作為鍵值KEY，將字出現(xiàn)的次數(shù)存儲(chǔ)和疊加，代碼框架碼好了之后就一勞永逸了。

六、在PPT里統(tǒng)計(jì)停留時(shí)間

以前在鞋廠做開(kāi)發(fā)，曾經(jīng)接到過(guò)運(yùn)營(yíng)的這么一個(gè)需求：

他們自己做了一個(gè)PPT，展示一些產(chǎn)品。現(xiàn)在，想在播放的時(shí)候，收集一下用戶在具體某一頁(yè)上停留的時(shí)間，以獲得用戶對(duì)產(chǎn)品的關(guān)注度。

PPT可以設(shè)置播放時(shí)各種延時(shí)效果，但是貌似沒(méi)有統(tǒng)計(jì)的功能。

由于源文件找不到了，這里大概寫(xiě)一個(gè)原型。

Private Declare Function timeGetTime Lib 'winmm.dll' () As Long

Dim dict

Sub OnSlideShowPageChange()

'記錄當(dāng)前頁(yè)數(shù)

Dim iCut As Integer

iCut = SlideShowWindows(1).View.Slide.SlideIndex

Dim iCutTime As Long

iCutTime = timeGetTime()

'初始化字典

If IsNull(dict) Or (iCut = 1) Then

Set dict = CreateObject('Scripting.Dictionary')

End If

dict.Item(iCut) = iCutTime

If dict.Exists(iCut - 1) And (dict.Item(iCut - 1) > 0) Then

MsgBox '停留時(shí)間：' & (iCutTime - dict.Item(iCut - 1)) & '毫秒'

End If

End Sub

VBA自帶的時(shí)間函數(shù)比較坑，只能精確到秒。而且是當(dāng)天的秒，即晚上0點(diǎn)過(guò)后會(huì)重置，到時(shí)候還得判斷是否跨天。

所以這里干脆用了系統(tǒng)API，timeGetTime獲得的是開(kāi)機(jī)到現(xiàn)在經(jīng)過(guò)的毫秒數(shù)。

然后將PPT設(shè)置為手動(dòng)播放，此時(shí)程序會(huì)自動(dòng)記錄每一次切換幻燈片時(shí)的時(shí)間，并且和上一張的時(shí)間相減，得出用戶在上一張幻燈片里停留的時(shí)間。

由于是原型，這里就簡(jiǎn)單的減去序號(hào)上一張的時(shí)間，一般人播放就是滾輪或者鼠標(biāo)一張一張點(diǎn)。如果是跳著播放的，比如從第1張直接跳到第3張等特殊情況，代碼需要改動(dòng)。

如圖，我從第2張?zhí)降?張時(shí)，得到第二張幻燈片的停留時(shí)間是5124毫秒。

得到數(shù)據(jù)后要怎么用，那就看具體需求了。

事實(shí)上，VBA甚至可以直接連接數(shù)據(jù)庫(kù)，不過(guò)寫(xiě)了估計(jì)沒(méi)人看也看不懂，就先略過(guò)了。

七、總結(jié)與提醒

可能有的朋友聽(tīng)說(shuō)過(guò)宏，宏與腳本的區(qū)別是什么呢？

一個(gè)宏，可以用一行或者一段腳本來(lái)實(shí)現(xiàn)。事實(shí)上，微軟提供的“錄制宏”功能，就是把操作錄制成一段代碼，然后作為宏來(lái)調(diào)用。

但是反過(guò)來(lái)，代碼能做的事情多了去了，熟練運(yùn)用了之后，比宏要強(qiáng)上百倍。

在應(yīng)用性方面，Excel肯定是最常用的，放些文本框下拉框按鈕啥的，甚至可以當(dāng)成簡(jiǎn)易的程序來(lái)用。之前就幫別人做過(guò)簡(jiǎn)單的出題答題系統(tǒng)，太復(fù)雜應(yīng)該沒(méi)人看，就不提了。

Word其次，由于排版上的問(wèn)題，控件不好放。而且由于不涉及太多運(yùn)算，大多數(shù)功能通過(guò)軟件原生就能實(shí)現(xiàn)。

PPT最次，需要強(qiáng)調(diào)的是，PPT運(yùn)行時(shí)雖然能觸發(fā)VBA腳本，但是卻無(wú)法調(diào)試。PPT前臺(tái)播放，和后臺(tái)的腳本是多線程異步的，沒(méi)有出錯(cuò)信息，也斷不了斷點(diǎn)。而且PPT支持的事件也比較少。

支持VBA的，也不一定只有微軟自家的Office，像鼎鼎有名的AutoCAD也支持。

當(dāng)然，VBA編程需要對(duì)VB語(yǔ)法有基礎(chǔ)，并且不同軟件的事件、屬性都不同，查資料是難免的，肯定不像一些基礎(chǔ)操作和函數(shù)一樣人人都能用。但是如果能用好，那才真的是效率飛升了。