張貼者:2010年5月29日 上午6:38Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:39
]
同事問我,Excel有沒有函數顯示一個整數的所有因數,例如 12 就顯示「3*4」、16 就顯示「2*2*2*2」。 我翻了一翻說明,只找到最大公因數和最小公倍數的函數。 我想,利用recursion也不難做,於是毅然寫了一個小函數供大家參考。程式已經比我想像中簡單,但大家可以看看有沒有辦法寫得更精簡。
語法:
' 利用recursive function,把一個整數的因數顯示出來。 ' 用法:AllFactors(n) Function AllFactors(iNo As Integer) As String Dim i As Integer Dim sgTemp As Single For i = 2 To iNo - 1 sgTemp = iNo / i If sgTemp = Int(sgTemp) Then AllFactors = Trim(Str(i)) & "*" & AllFactors(Int(sgTemp)) Exit For End If Next i ' 到這一步還沒有得到因數,代表這是一個質數(prime number) If AllFactors = "" Then AllFactors = Trim(Str(iNo)) End If End Function
|
|
執行後,就像這樣:  |
|
|
|
|
張貼者:2010年5月29日 上午6:37Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:38
]
介紹 根據用戶指定的條件,把資料庫內的資料排序及篩選,資料有多少欄也可以。十分實用吧?!
用法很簡單,只要在執行巨集以後,選出檢索欄,巨集會馬上顯示檢索欄中所有獨一無二的值,然後再選要篩選出來的記錄,選項可以多於一個: 
按「篩選」後,畫面上只會顯示合符條件的記錄。很方便呢! 
程式碼 程式碼較複雜和長,不在此刊登,請下載附檔。
使用的指令/方法 - 對話框裡的ComboBox、ListBox、CommandButtons和AddItem。 - Sort及AdvancedFilter - Set
使用的函數/屬性 - CurrentRegion
注意地方 - 資料庫的四周必須為空行或空欄,否則屬性CurrentRegion會算錯。 - 用For Each...Next的時候,如果用作迴圈的範圍的橫向一字形的,你需要多加.Columns在後面,否則迴圈只會跑一次,如: For Each rI In rTemp.Columns cbKeyField.AddItem rI.value Next - 在「選出」欄裡不管全選也好,完全沒選也好,會當作光排序論。
已知問題/蟲 - 沒有
|
|
|
|
|
張貼者:2010年5月29日 上午6:36Administrator ExcelHelp
[
Carson Cheng 已於 2011年1月19日 下午11:57 更新
]
在這個數碼年代,我們日常生活中都要面對許許多多的數字,身份證號碼是其一。 香港身份證號碼後端,有一雙括號,括號內有一個數字(或「A」字),叫「校檢碼(Check Digit)」,許多人都只知其名,而不知其義,這次就讓我介紹一下。 在輸入資料時,如果由人手輸入,錯誤的機會當然大,就算利用條碼 (Barcode) 也難保輸入無誤,所以校檢碼就應運而生。透過一條公式,我們把一組數字轉化成一個校檢碼,如果這組數字內有任何一個數字改變了,公式就會產生一個不一樣的校檢碼(如果這條公式會產生一個跟原來一樣的校檢碼,這條公式就不可以接受)。 當你輸入了一個身份證號碼(包括校檢碼),電腦可以馬上運用公式把括號前端的字母和數字算出一個校檢碼,如果這個校檢碼跟括號內的校檢碼一樣,那就代表正確的機會很大;相反,如果不一致的話,就可以肯定輸入的號碼是錯誤的。 校檢碼的公式不很複雜,用一個例子說明就很容易理解:
而當前方有一個X時,則在累積數加上6:
註:
- 第二個英文字母的代表數字:A為1,B為2,C為3,如此類推。
- 英文字母可以是兩個位,絕大部分人的身份證都只有一個位。
- 校檢碼如果是10,用「A」代表;是11的話,用「0」代表。
示範
程式碼如下: ' HKID 決定輸入裡的香港身份證編號是否正確 ' 輸入:sInput: "AA999999(9)" 或 "A999999(9)" ' 輸出:香港身份證編號是否正確 Function HKID(sInput As String) As Boolean Dim iAcc As Integer ' 累加變數 Dim sCheck As String Dim i As Integer
' 如果sInput只有10位(包括括號),則在前面多加一個空位 If Len(sInput) = 10 Then sInput = " " & sInput ' 如果sInput的文字長度是11,且第一個字母不是X就是空格,且文字中第9和11位的是括號, ' 則繼續運算,否則已經可確定sInput是錯誤的。 If Len(sInput) = 11 And (Left(sInput, 1) = " " Or Left(sInput, 1) = "X") And _ Mid(sInput, 9, 1) = "(" And Right(sInput, 1) = ")" Then ' 起始化 sInput = UCase(sInput) ' 把英文都轉成大草 sCheck = Mid(sInput, 10, 1) ' 校檢碼 iAcc = 0 ' 累加變數 ' 英文部分(第1、2位) ' 第一個字母:如果是X的話,則加上6 If Left(sInput, 1) = "X" Then iAcc = iAcc + 6 ' 第二個字母:A 代表 1; B 代表 2 如此類推,然後乘上8 iAcc = iAcc + (Asc(Mid(sInput, 2, 1)) - 64) * 8 ' 數字部分(第3到8位) ' 第一個數字(由左至右)乘上7,第二個數字乘上6,如此類推 For i = 3 To 8 iAcc = iAcc + Val(Mid(sInput, i, 1)) * (10 - i) Next i ' 校檢碼等於11減去累積數除以11的餘數,會得出一個1至11的數字 iAcc = 11 - iAcc Mod 11 ' 看看這裡算出的校檢碼與輸入的是否一致 ' 如果算出的校檢碼是10的話,則用"A"代表;是11的話,則用0代表。 Select Case iAcc Case 10 HKID = (sCheck = "A") Case 11 HKID = (sCheck = "0") Case Else HKID = (sCheck = Trim(Str(iAcc Mod 10))) End Select Else HKID = False End If End Function
我現在還沒有弄清楚第一個英文字母的計算方法,主要是不知道是否只可以是「X」,如有錯誤,請不吝指正!在此特別謝謝Frankie Wong!
|
|
|
|
|
張貼者:2010年5月29日 上午6:35Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:35
]
簡介 有會員問,如何在Excel上制作一部簡單、最基本的計算機,雖然實用性很低,但用來學VBA則是個非常好的題材。

使用的功能 表格中的按鈕 (Buttons) On Error Goto XXX
使用的函數/屬性 Application.Caller
技巧
- 由於我們的開發平台是Excel,故此可以利用Excel本身的運算功能。我們不用自行運算(所以不用愁先乘除後加減),只要把用戶輸入的按鍵一個一個地用文字方法先存下(下面叫顯示字串),到了用戶按 = 按鈕便把文字轉為公式,答案自然會跑出來。
- 但凡有任何錯誤,例如除零、公式錯誤等,都利用On Error Goto顯示「Error」。
- 所有按鈕都統一執行ProcessKey。命名按鈕時,直接用加減乘除的符號,方便處理,但由於名稱不能以符號為首,故在所有按鈕名稱前統一加上"Button",處理按鍵時把"Button"刪掉即可。
程式
' 處理按鍵 Sub ProcessKey() Dim sButton As String Dim rDisplay As Range ' 起始 On Error GoTo ShowError ' 遇有任何錯誤則顯示「ERROR」 Set rDisplay = Range("Display") ' 顯示屏,也是顯示字串和公式的所在地 sButton = Mid(Application.Caller, 7, 99) ' 看看用戶按了哪個按鈕 ' 例如+鍵,Caller返回"Button+",sButton將是"+" Select Case sButton Case "AC" ' 把顯示字串刪除 rDisplay.Numberformat = "@" ' 把數字格式轉到文字 rDisplay.value = "" Case "CE" ' 把顯示字串右邊的數字(如有)刪除 Do While IsNumber(Right(rDisplay.value, 1)) rDisplay.value = Left(rDisplay.value, Len(rDisplay.value) - 1) Loop Case "=" ' 把顯示字串轉為公式 If rDisplay.value <> "" Then rDisplay.Numberformat = "General" ' 把數字格式轉回通用 rDisplay.formula = "=" & rDisplay.value End If Case Else ' 所有其他按鈕只需加到顯示字串後便可 rDisplay.value = rDisplay.value & sButton End Select Exit Sub
' 有錯誤的話,顯示「ERROR」 ShowError: rDisplay.value = "ERROR" End Sub
' 測試來源字串(的第一個字符)是否一個數字 Private Function IsNumber(sInput As String) As Boolean IsNumber = (sInput >= "0" And sInput <= "9") Or sInput = "." End Function
注意:我沒有使用有千位分隔號的數字格式,這部分留給你自行研究。
|
|
|
|
|
張貼者:2010年5月29日 上午6:32Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:32
]
利用SendKeys陳述式,可以做到一些巨集做不到的東西,例如把菜單拉下、示範如何做某一指令等。
例一:光把檔案菜單拉下 Sub PullDownFileMenu() SendKeys "%F" ' < Alt > + F End Sub
例二:示範如何多加一欄 Sub InsertColumn() ' 「True」代表要等候動作做完才執行下一個指令,這裡我們需要等候,好讓畫面更新 SendKeys "%I", True ' < Alt > + I Pause SendKeys "{down}", True Pause SendKeys "{down}", True Pause SendKeys "~" ' < Enter > End Sub
' 暫停一秒,讓使用者看清楚 Private Sub Pause() Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1) End Sub
例三:一直按著 < Shift >,按 < End >,然後按右鍵,才放開 < Shift >。要用括號把受影響的鍵括著。 SendKeys "+({End}{Right})", True
以下是可以用的程式碼,詳情請參考說明:
鍵 程式碼 BACKSPACE {BACKSPACE} 或 {BS} BREAK {BREAK} CLEAR {CLEAR} DELETE or DEL {DELETE} 或 {DEL} END {END} ENTER (數字鍵) {ENTER} ENTER ~ (tilde) ESC {ESCAPE} 或 {ESC} HELP {HELP} HOME {HOME} INS {INSERT} TAB {TAB} CAPS/SCROLL/NUM LOCK {CAPSLOCK}/{SCROLLLOCK}/{NUMLOCK} PAGE UP/DOWN {PGUP}/{PGDN} RETURN {RETURN} UP/DOWN/LEFT/RIGHT ARROW {UP}/{DOWN}/{LEFT}/{RIGHT} F1 至 F15 {F1} 至 {F15} SHIFT + CTRL ^ ALT %
|
|
|
|
|
張貼者:2010年5月29日 上午6:31Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:32
]
這裡介紹兩個關於信用卡號碼的函數,兩個函數一併使用,就可以決定一個信用卡號碼是否正確。當然,這個範例對初學巨集的人也是一個很好的練習。
Luhn算式 首先介紹一個叫Luhn的算式,它可以決定一系列的數字是否正確。 算法很蠻簡單,從右到左,把單數數位(如個位、百位等)的數值則可以直接累計起來;雙數數數位(如十位、千位)的數值則乘2,如大於9則減9,然後把得出的數字累計起來;。最後累計得來的數值如果是10的倍數,信用卡號碼則為正確。 用6528做個例子:  由於累計數20是10的倍數,也就是說可以給10除盡,故此6528這個數字是符合Luhn算式的。
語法:
' 信用卡號碼是否正確 Function IsValidCreditCardNo(sCreditCard As String) As Boolean Dim iCardNoLen As Integer ' 信用卡號碼長度 Dim iSum As Integer ' 累計用 Dim i As Integer Dim iTemp As Integer ' 起始化 iCardNoLen = Len(sCreditCard) ' 信用卡號碼長度 iSum = 0 ' 用作累計的變數 ' 單數(由右至左)的數位直接加到累計 For i = iCardNoLen To 1 Step -2 iSum = iSum + Mid(sCreditCard, i, 1) Next i ' 雙數(由右至左)的數位乘2後,如大於9則減9,然後加到累計 For i = iCardNoLen - 1 To 1 Step -2 iTemp = Mid(sCreditCard, i, 1) * 2 iSum = iSum + iTemp - IIf(iTemp > 9, 9, 0) Next i ' 如果累計數是10的倍數,則輸入的卡號則為正確 IsValidCreditCardNo = (iSum Mod 10 = 0) End Function |
|
當然,我們知道這四個數字不可能是個信用卡號碼,故此我們需要另外一個函數。
信用卡的種類 下表列出較流行信用卡的種類,以及開首號碼與號碼長度。 
這個函數要寫得簡單,必須知道 Like 的用法,以下是偵測JCB的寫法:
' JCB If (sCreditCard Like "3*" And iCardNoLen = 16) Or _ ((sCreditCard Like "2131*" Or sCreditCard Like "1800*") And _ iCardNoLen = 15) Then CreditCardType = "JCB" End If
注意地方:
- 在計算Luhn函數時,人一般會一個數字一個數字順序計算,但寫程序時,首先整批處理單位數字,然後才整批處理雙位數字,這樣會較簡潔。
- 在Luhn函數裡,不要這樣寫:
If iSum Mod 10 = 0 Then IsValidCreditCardNo = True Else IsValidCreditCardNo = False End If
這樣寫又簡單,又清楚: IsValidCreditCardNo = (iSum Mod 10 = 0)
- 編寫帶有「和 AND」和「或 OR」的條件時,請看清楚括號是否正確,記著:AND的處理優先權比OR高。
- 不要以為輸入一個正確的信用卡號碼就可以在網上騙財!第一,受款公司可能直接連到信用卡公司,不存在的號碼原形畢露;第二,受款公司一定會記錄你付款的時間與當時候你的IP地址,要追查並不困難。
|
|
|
|
|
張貼者:2010年5月29日 上午6:30Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:30
]
Excel 的TextBox不如Access的那樣,可以設定輸入遮罩(Input Mask),以控制用戶輸入的文字。這個技巧就可以模擬輸入遮罩。
在這個例子當中,輸入文字的格式為"EXCEL999A",也就是說,前方要有EXCEL字樣,其後的三個字符需為數字,最後有一個字母。
要注意的是,除了TextBox,我們還要一個Label,用作存放上一次輸入的正確文字,且要把它的Visible值設為False。

程式如下:
' 起始時,把預設的字串(這裡就是"EXCEL")放進TextBox1和Label1。 Private Sub Userform_Initialize() TextBox1.Text = "EXCEL" Label1.Caption = TextBox1.Text End Sub
' 當用戶更改文字方塊時,就要確定新輸入的文字符合規格。 Private Sub TextBox1_Change() ' 把輸入的文字轉到大寫(可有可無) TextBox1.Text = UCase(TextBox1.Text) ' 如果輸入的文字正確的話,則把它記錄在隱藏的label裡; ' 否則,把記錄在label的值(也就是上一次輸入正確的文字)放回到輸入欄 If ValidString(TextBox1.Text) Then Label1.Caption = TextBox1.Text Else TextBox1.Text = Label1.Caption End If End Sub
' 判別文字S是否正確 ' 這裡的程式碼要按你的需要而修改。 ' 這裡的技巧是,就算文字不夠長,也可以使用MID(),不會出現錯誤,這可以把程式碼寫得很簡單。 ' 例如來源文字只有"ABC",MID("ABC",5,1)不會發生錯誤,只會返回空字串 ""。 Private Function ValidString(S As String) ValidString = False If Len(S) >= 5 And Len(S) <= 9 Then If Left(S, 5) = "EXCEL" Then ValidString = IsDigit(Mid(S, 6, 1)) And IsDigit(Mid(S, 7, 1)) And _ IsDigit(Mid(S, 8, 1)) And IsLetter(Mid(S, 9, 1)) End If End If End Function
' 判別S字串是否一個數字,空字串也可以 Private Function IsDigit(S As String) IsDigit = (S >= "0" And S <= "9") Or S = "" End Function
' 判別S字串是否一個字母,空字串也可以 Private Function IsLetter(S As String) IsLetter = (S >= "a" And S <= "z") Or (S >= "A" And S <= "Z") Or S = "" End Function
執行的時候就像用了輸入遮罩:
 |
|
|
|
|
張貼者:2010年5月29日 上午6:28Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:29
]
因工作關係,要把許多檔案裡的每一張工作頁印出來,要人手逐一逐一按到每一張工作表,然後再按列印,費時失事!就算可以先選所有的工作表才列印,但還是蠻麻煩的。
這個小工具很簡單,用了兩個For...Next迴圈,做出「所有」的「所有」的效果。是個不錯的迴圈練習。
由於Workbooks和Worksheets都是物件,所以用 For Each 會比較簡單。
' 列印所有已開啟的工作簿裡的所有工作表 Sub PrintWorkbooks() Dim WB As Object, WS As Object ' Workbooks物件載著所有已打開的工作簿 For Each WB In Workbooks ' Worksheets則載著工作簿WS裡所有工作頁 For Each WS In WB.Worksheets ' 只印出沒有隱藏的工作表,並且不要把自己印出來 ' 我用工作表名稱來識別是否本檔自己 If WS.Name <> "ExcelHelp.net" And WS.Visible Then WS.PrintOut End If Next Next End Sub
' 除了本檔,把所有已開啟的檔案關閉 Sub CloseWorkbooks() Dim WB As Object For Each WB In Workbooks If WB.Sheets(1).Name <> "ExcelHelp.net" Then WB.Close End If Next End Sub
|
|
|
|
|
|
|
張貼者:2010年5月29日 上午6:27Administrator ExcelHelp
[
已更新 2010年5月29日 上午6:28
]
也許你不知道,一個儲存格(單元格/Cell)可以存放32,767個字符,也就是兩三頁紙的長度!不過由1,024個字符開始,Excel便不會換行,以致後段的文字會一直排到超越儲存格的右邊界。 所以如果要輸入大量的文字進一個儲存格的話,就只能在公式欄那裡編輯。但由於公式欄字體大小不受控制,且欄寬太小,編輯工作有一定困難。於是我便寫了這個小工具,把儲存格的文字搬到一個Userform的TextBox裡編輯,編輯後拷回儲存格。不單可以自選字體及字體大小,還可以讓用戶用<Enter>來換行。 在設定Userform時,以下是一些較特別的設定: -TextBox (存放文字的文字框) 的字體和字體大小,與兩個ComboBox兩邊的預設值要一致 -ComboBox的style要設為2 - fmstyleDropDownList,否則用戶便可以輸入不正確的值,導致執行錯誤 -TextBox 的 ScrollBars 設為 2 - fmScrollBarsVertical,那樣便可以有垂直的滾軸 -EnterKeyBehavior 設為 True,那用戶就不用按<Alt>+<Enter>來換行,可以直接用<Enter> 安裝方法: 1)開啟附檔後,選「檔案|另存檔案」 2)儲存格式選「Add-in 增益集」,這個選項在末端 3)關掉附檔 4)隨便打開一個檔案,選「工具|增益集」,確定已勾選 Magglass 5)現在在任何一個儲存格上,按<Ctrl>+q ,文字放大鏡便會跑出來! 取消安裝方法: 1)選「工具|增益集」 2)勾除 MagGlass,選確定 |
|
|
|
|
|