[發問] VBA使用 SQL 指令，User Defined Function(使用者自訂函數)，如何輸入2個參數。

Baracuda

參數內容        參數1：為欄位內容，參數2：為設定的常數
VBA 中設定引用        設定引用的是 Microsoft ActiveX Data Objects 2.0 Library
已經試過的內容        直接引用常數時可以 Work
        =Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _

        指定參數(宣告為常數)不Work
        Const Labor_Pension_Personal_rate = 0.1
        =Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _
        錯誤訊息：參數太少，預期個數1
參考內容        Excel 巨集魔法書  李潛瑞著 page99
問題背景        想要在 Excel VBA中使用SQL來計算勞健保及勞退的個人負擔金額       
            lcCommandText = "SELECT 序號, 姓名,  核定本薪,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
                        " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
                        "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _

Baracuda

1. Option Explicit
   
2. Option Base 0
   
3. 
   
4. Sub main()
   
5.     Call SQL_Insurance_Burden
   
6. End Sub
   
7. 
   
8. Function Multiply_2_No_Round_Down_4_Up_5(Number1 As Single, Number2 As Single) As Long
   
9. ' 目的
   
10. ' 2數相乘再做4捨5入
    
11.      Multiply_2_No_Round_Down_4_Up_5 = Int(Number1 * Number2 + 0.5)
    
12. End Function
    
13. 
    
14. Sub SQL_Insurance_Burden()
    
15. Const Output_Sht = "輸出表"
    
16. Const Input_Sht = "輸出表"
    
17. Const Labor_Pension_Personal_rate = 0.1
    
18.     Dim lcConnectionString As String
    
19.     Dim lcConnectString, lcCommandText As String
    
20.     Dim loADODBConnection As ADODB.Connection
    
21.     Dim loADODBRecordset As ADODB.Recordset
    
22.    
    
23.     lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
    
24.                             "DBQ=" + ActiveWorkbook.FullName + ";" & _
    
25.                             "ReadOnly=True"
    
26. 
    
27.                
    
28.     lcCommandText = "SELECT 序號, 姓名,  核定本薪,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
    
29.                 " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
    
30.                 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _
    
31.                 "FROM " & "[" & Input_Sht & "$" & "A1:P32]"
    
32.                
    
33. '    Work
    
34. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _
    
35. 
    
36. ' 不 Work
    
37. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _
    
38. '2 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  Labor_Pension_Personal_rate + ')'  as 勞退個人 " & _
    
39. 
    
40.     Set loADODBConnection = CreateObject("ADODB.Connection")
    
41.     Set loADODBRecordset = CreateObject("ADODB.Recordset")
    
42.    
    
43.     Debug.Print lcCommandText
    
44. 
    
45.     loADODBConnection.Open lcConnectionString
    
46.     loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1
    
47. 
    
48. 
    
49. 
    
50.     Sheets(Output_Sht).Select
    
51. 
    
52.     Dim r, f As Integer
    
53.     r = 1
    
54.     For f = 0 To loADODBRecordset.Fields.Count - 1
    
55.         Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name
    
56.     Next
    
57.      
    
58.     While Not loADODBRecordset.EOF
    
59.         r = r + 1
    
60.         For f = 0 To loADODBRecordset.Fields.Count - 1
    
61.             Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
    
62.         Next
    
63.         
    
64.         loADODBRecordset.MoveNext
    
65.     Wend
    
66. 
    
67. End Sub

複製代碼

kimbal

Baracuda 發表於 2012-1-28 15:49

試試把sql換成這個樣子

1.         lcCommandText = "SELECT 序號, 姓名,  核定本薪,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
   
2.                 " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
   
3.                 "  '=Multiply_2_No_Round_Down_4_Up_5(' + 勞退 + '," + Trim(Str(Labor_Pension_Personal_rate)) + ")'  as 勞退個人 " & _
   
4.                 "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

複製代碼

Baracuda

Error Message 參數太少，預期個數1
還是有問題。
我尚不知如何解。
可能用 SQL 查出後再另用 VBA 寫過。

Hsieh

回復 4# Baracuda
你的意思不是要將字串中的常數用變數取代嗎?
    lcCommandText = "SELECT 序號, 姓名,  核定本薪,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
                " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(" & Labor_Pension_Personal_rate & ")) + ')'  as 勞退個人 " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

Baracuda

是的，版主我試看看。

Baracuda

Hsieh 版主：
我的意思不是要將字串中的常數用變數取代嗎。
但是語法好像只能用欄位。
如果VBA 中設定的變數(or常數)會有問題。

Scott090

回復 7# Baracuda

純屬討論及個人看法

VBA ADO SQL 好像並不支援變數運算，
UDF也只是在結果欄位放入計算式而不是ADO會直接計算
這個題目，個人的看法是ADO的支援能力問題而不是 UDF 裡有幾個參數

主欄位也一樣不能用變數計算：
隨意舉例 "核定本薪"
Const aa=2

可以計算； 核定本薪 * 直接常數 2
lcCommandText = "SELECT 序號, 姓名,  核定本薪 * 2 as bb,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
                " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

可以計算； 核定本薪 *  " &  aa  & " ，變數已在成立  lcCommandText 字串時轉化了 aa 為 2，2 是常數不是變數
lcCommandText = "SELECT 序號, 姓名,  核定本薪 *  " &  aa  & " as bb,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
                " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

不會計算； 核定本薪 * aa  的變數 aa 是 lcCommandText 字串的一部分， ADO沒支援字串內的變數
lcCommandText = "SELECT 序號, 姓名,  核定本薪 * aa as bb,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
                " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
                "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _
                "FROM " & "[" & Input_Sht & "$" & "A1:P32]"

Scott090

回復 7# Baracuda


    假設，
1. Excel VBA ADOSQL 並不支援 變數
2. SQL只是可以技巧性的在生成的欄位放入計算式，此計算式可以演算原始欄位資料

那就在 Excel 儲存格 放入計算式來呼叫 VBA內的 UDF，
此自訂函數就較有寬廣的運作空間了