VBA教學:For Next迴圈及Step參數,報表插入空白列

VBA中的For next迴圈和IF邏輯判斷是兩個最基本且常用的程式語句,本文以應付帳款負數檢查為例,具體介紹如何自動化進行資料異常檢查,說明step參數用法。

一、負數應付帳款

範例是一個簡單的應付帳款明細帳,注意到其中有一筆是負數,這個顯然是較為異常情況,在此很容易看出來,但在實務上通常資料量很大,因此希望藉由VBA程式設計能夠自動標記出負數的應付帳款。

一張含有 桌 的圖片

自動產生的描述

二、For Next迴圈

設計VBA程式,將資料從第2列到第11列依序檢查,因此設計迴圈語句:「For i = 2 TO 11」,這裡的For和最下方的「Next」形成迴圈結構,而VBA在實際執行的時候,會先以i=2執行For…Next中間的程式片段,到了Next之後,再回到For開始,接著是i=3再執行一次,如此依序執行,一直到i等於11為止。

For跟Next之間是要迴圈執行的程式,在此是一個If判斷語句,如果第i列F欄的儲存格值是負數的話,那麼就在同一列的G欄儲存格寫入「應付帳款負數?」。

注意到在第1行的「For I =2 To 11」其實還有一個參數「step」只不過如果沒有設定的話,它的預設值就是1,剛好也就是大部分的所適用的情況,自然數流水編號,因此很多時候都是省略的。

關於Excel如何進入VBA編輯器開始設計程式及執行,可以參考贊贊小屋相關文章

VBA教學:For Next迴圈及Step參數,報表插入空白列 2

三、VBA資料檢查

執行上個步驟程式,在工作表第6列應付帳款是負數,果然在G欄寫入提示文字:「應付帳款負數?」。

一張含有 桌 的圖片

自動產生的描述

四、Step參數用法

既然瞭解了For Next迴圈中step參數的作用,接下來試看看將它設定為2:「Step 2」,迴圈執行內容則是在第i列「Rows(i))的地方,再插入一列並且將列高設定為「5」。

一張含有 文字 的圖片

自動產生的描述

五、程式執行測試

執行結果雖然有插入了空白列並且調整列高,很顯然並非整個報表全都插入空白列,不符合期待,因此再去檢視上個步驟的程式,它是「for I = 3 To 11」並且「step 2」,實際執行次數一定是比報表資料筆數還少,精準而言報表有10筆資料,程式只執行五次,因此不會在每一筆資料都插入空白列。

另外由於Excel在插入空白列之後,當前列以下的範圍都會自動往下一列,也就是原本的列號會加1,因此程式才要設定為「Step 2」,表示等差是2,這個可以參考程式執行前後的變化,或者自己手工於Excel工作表上插入一列,便能體會其中用意。

一張含有 桌 的圖片

自動產生的描述

六、計算變數設定

為了解決上個步驟的狀況,先設定「列數」變數做個簡單計算:「列號=(2+1)+2×10」,這裡因為是從第2列之後開始插入空白列,因此是「2+1」,然後報表總共有10筆資料,每一筆資料下方都要插入一個空白列,因此要再加上「2×10」。

接著再用所計算好的列號設計迴圈:「For i=3 To 列號 Step2」,3到列號等差42迴圈依序插入空白列並調整列高。

一張含有 文字 的圖片

自動產生的描述

七、報表添加空白列

執行程式,很順利地在原來的報表每筆資料都插入了一個空白列,並且適當的調整縮減了列高。注意到通常報表最後一筆就不需要再插入空白列了,這裡程式沒有特別調整,是預設了還會繼續輸入下一筆資料,讀者可以依照自己狀況修改程式代碼。

一張含有 桌 的圖片

自動產生的描述

Excel操作與VBA自動化

很多時候基於報表美觀會想要像這篇文章範例所看到的一樣,在每一筆資料都插入一個空白列,這個在Excel的操作雖然簡單,可是當報表資料有很多筆的時候,要進行如此的操作有點麻煩,或者是需要藉助一些較為複雜進階的指令工具函數公式,而如果是設計VBA程式的話,可以看到只要簡單語句便能實現想要的結果。所以在工作上經常要使用Excel進行資料處理或報表編製,VBA基礎技巧是蠻值得學習的,讀者有興趣參考看看贊贊小屋VBA大全集課程,零基礎入門到進階的完整學習。

每天學習,每天充電:VBA自動化文章合輯

VBA教學:For Next迴圈及Step參數,報表插入空白列 9
歡迎加入Line社群,口袋裡的VBA小教室!