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

所以更高階一點的東西，比如VBA，討論結(jié)果是不會有什么熱度，因為大部分人覺得說一輩子也用不著吧，就不會點收藏了。

這個論點是有道理的，因為我之前連載過羅技LUA腳本系列，看一下數(shù)據(jù)就知道了。

不過這次正好有征文，Visual Basic是其中一個選題。光寫VB估計更不會有人看了，所以還是結(jié)合一下實際，寫寫一些簡單的VBA吧。

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

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

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

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

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

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

我們先點擊左上角，選擇Excel選項。

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

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

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

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

首先先到選項里，找到信任中心。

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

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

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

二、Excel妙用之高亮行與列

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

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

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

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

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

這個例子用到的函數(shù)是SheetSelectionChange，顧名思義，當(dāng)工作表選中區(qū)域改變時，觸發(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ù)時避免眼花看錯行的情況。

不過，有一點要注意的是：

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

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

之前的首頁熱門，推薦來推薦去就那幾個函數(shù)，光vlookup函數(shù)我就見了幾十次了。

不過也是，系統(tǒng)自帶的函數(shù)，常用或好用的也就這些，再推薦能推薦出什么花來呢？

這里，我來教大家做自定義函數(shù)。

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

然后，我們寫個簡單的函數(shù)。

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

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

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

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

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

輸完回車，搞定。

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

可能有的朋友會說，這函數(shù)也太簡單了，來點花哨的吧？

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

如果你有具體需求并且在編程上遇到問題，歡迎來打賞留言

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

如何才能真正的稱作提高生產(chǎn)效率？

如果只是會用vlookup函數(shù)，或者會用CTRL+C代替右鍵菜單的復(fù)制，那這所謂的生產(chǎn)效率也太入門和沒價值了。

事實上，很多時候，我們在職場上的生產(chǎn)效率，不僅取決于我們自己，也取決于協(xié)同崗位或者部門的效率。

舉一個親身經(jīng)歷，也是很多朋友會遇到的問題：程序和策劃的協(xié)同。

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

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

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

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

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

于是我就用VBA做了一個小工具。

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

點擊左上角的“生成怪物數(shù)據(jù)表”，此時就會把這些有用的字段篩選出來，生成一個EXCEL表。

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

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

效率提高的部分，在于一鍵生成刷庫SQL語句。

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

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

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

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

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

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

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

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

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

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

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

在EXCEL里面放按鈕也很簡單。

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

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

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

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

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

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

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

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

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

由于只是演示，我就直接做消息框彈出了，有需求的可以另行添加，比如扔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

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

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

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

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

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

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

由于源文件找不到了，這里大概寫一個原型。

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

Dim dict

Sub OnSlideShowPageChange()

'記錄當(dāng)前頁數(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 "停留時間：" & (iCutTime - dict.Item(iCut - 1)) & "毫秒"

End If

End Sub

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

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

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

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

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

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

事實上，VBA甚至可以直接連接數(shù)據(jù)庫，不過寫了估計沒人看也看不懂，就先略過了。

七、總結(jié)與提醒

可能有的朋友聽說過宏，宏與腳本的區(qū)別是什么呢？

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

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

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

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

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

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

不過，由于是自家的東西，微軟給VBA開放了太多的權(quán)限，VBA、VBS病毒在歷史上還是有一定地位的。大家如果網(wǎng)上找代碼啥的，一定要多加小心。

一些平日經(jīng)常用的，軟件卻又沒辦法簡單實現(xiàn)的功能，都可以考慮用VBA來實現(xiàn)。涉及多人、多個部門的工作，如果能達(dá)成共識統(tǒng)一好格式，一個腳本可以頂好幾天的工作量，還不會錯。以前項目里做過從策劃，設(shè)計，關(guān)卡，腳本，資源，測試一條龍的VBA，涉及過深這里就不細(xì)談了。

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