小攻城師的戰場筆記

前幾天在 msn 上接到一個小需求，
希望可以找個辦法把 Excel 上輸入的資料轉換為指定的純文字格式。
資料在 Excel 中的內容大概是長成這樣：
 

轉出來的內容很簡單，
就是把欄名拿掉、內容轉存成兩段文字，以兩個半形空白分隔，
其中數字A與數字B必須判斷是正數或負數、補上正負號，
並補齊為15位數的數字，不足的位數就補零。
最後再補上一整串固定長度的A。

所以得到的結果大概要長成這樣：
A01627196021xxx  +000000002330244+000000001761202AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

他覺得這應該很簡單、但是他不會寫，
我也覺得應該很簡單，第一個想到的解決方案就是用 python 寫，
不過被回絕了：「最好可以在不要離開 excel 視窗的前提下就可以完成這件事。」

所以這是我的第一支 VBA。
程式碼如下，有書輔助的情況下，
加上翻書的時間，不到十五分鐘就收工了。



Sub Trans()
    myDir = ThisWorkbook.Path '指定路徑為工作表所在目錄
    ChDrive myDir
    ChDir myDir
    Set myRng = Range("A1").CurrentRegion '定義要抓取的範圍
    
    Dim Cell1 As String
    Dim Cell2 As String
    Dim padding1 As String
    Dim padding2 As String
    Dim padding3 As String
    '每一行最後都有一串活潑的A(用途不明，是開需求的人指定的)
    padding3 = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
    
    Dim filename As String
    '定義檔名為時間
    filename = Format(Now(), "yyyymmddhhmmss") & ".txt"
    '開啟檔案寫入，若路徑中未有該檔案會create一個
    Open filename For Output As #1
    '第一列不抓進來，一直取到最後一列
    For i = 2 To myRng.Rows.Count
        '第一個區段: 表類、股票代碼、年份、季別、會計代碼
        Cell1 = myRng.Cells(i, 1).Text + myRng.Cells(i, 2).Text + myRng.Cells(i, 3).Text + myRng.Cells(i, 4).Text + myRng.Cells(i, 5).Text
        
        '第二個區段: 數字，要補齊 padding
        padding1 = myRng.Cells(i, 6).Text
        padding2 = myRng.Cells(i, 7).Text
        '把數字轉換為15位數數字(不含正負號)
        padding1 = LstrFix(padding1, 15)
        padding2 = LstrFix(padding2, 15)
        '與前一段文字中間空兩格半形空格
        Cell2 = "  " & padding1 & padding2 & padding3
        
        Print #1, Cell1;                                        '加分號表示不換行
        Print #1, Cell2
    Next
    Close #1
    MsgBox ("存檔成功! ")
End Sub

'拿來做數字A & B padding用的函式
Function LstrFix(myData, myLen) As String
    Dim myPad As Integer, myText As String, myPN, newData
    If Mid(myData, 1, 1) = "-" Then
        myPN = "-"                                           '處理數字開頭應出現的正負號
        myData = Mid(myData, 2, myLen)  '負數時取出數字本身(不取負號)
    Else
        myPN = "+"
    End If
        
    '設定要補齊的個數的0
    myPad = myLen - Len(myData)
    For i = 1 To myPad
        myText = myText + "0"
    Next
    
    '最後要送至檔案中的數值：正負號 + 用來補滿15位數的0 + 數字本身
    LstrFix = myPN & myText & myData
End Function



------------------------
寫這個小玩意時看的書：
古川順平，《新 Excel VBA 與資料庫整合實務講座》，博碩，2006。

裡面先教了一些 Excel 的技巧、才開始講 VBA，
可以一次滿足學習 Excel 與 VBA 的願望。

不過我後來還看了這本：
John WalkenBach, "Excel 公式函數與 VBA 整合徹底研究", 博碩, 2006.
好像也是這樣編排……

好處是古川順平這本因為是日本人寫的，所以有介紹一些遠東版才有的函式。
我看到的是全半形轉換，可以使用 StrConv 函式，設定不同的 conversion：
    StrConv(String, vbWide)      '將半形轉換為全形
    StrConv(String, vbNarrow)   '將全形轉換為半形
另一本 John WalkenBach 寫的就沒有翻到囉。

兩本寫得都很淺白，目錄也很方便查閱，
推薦給需要寫 VBA 但是平常又很少寫程式的商科學生～

------------------------
翻書時做的一點小筆記：

* 可以經由快速鍵 Ctrl-Shift-*(8) 找出資料表範圍。

* 資料表中不宜合併儲存格，以免影響排序與統計等功能的正常使用。

* 使用 VBE (Visual Basic Editor) 的方法：
    工具→巨集→Visual Basic編輯器

* 快速鍵：
    Ctrl-J 代表儲存格內的換行符號

* Excel 中不會區分文字大小寫判定，
因此要正確判斷大小寫差異，必須使用 EXACT 函數，
例如：=EXACT(LOWER(A1),A1)

* Excel 預設以筆劃排序中文資料，
若筆劃相同，則以內碼值作為排序依據。

* 為了避免 Excel 當掉，若想要叫出「尋找及取代」對話方塊，
建議不要使用該對話方塊的 show 方法：
    Applicaiton.Dialogs(xlDialogFormulafind).Show
而是改用快速鍵呼叫：
    Application.SendKeys("^f")