返回列表 上一主題 發帖

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

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

參數內容        參數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 勞退個人 " & _

Macro_SQL_UDF.zip (13.07 KB)

Macro_SQL_UDF.zip (13.07 KB)

輸入2個參數 Source Code。

  1. Option Explicit
  2. Option Base 0

  3. Sub main()
  4.     Call SQL_Insurance_Burden
  5. End Sub

  6. Function Multiply_2_No_Round_Down_4_Up_5(Number1 As Single, Number2 As Single) As Long
  7. ' 目的
  8. ' 2數相乘再做4捨5入
  9.      Multiply_2_No_Round_Down_4_Up_5 = Int(Number1 * Number2 + 0.5)
  10. End Function

  11. Sub SQL_Insurance_Burden()
  12. Const Output_Sht = "輸出表"
  13. Const Input_Sht = "輸出表"
  14. Const Labor_Pension_Personal_rate = 0.1
  15.     Dim lcConnectionString As String
  16.     Dim lcConnectString, lcCommandText As String
  17.     Dim loADODBConnection As ADODB.Connection
  18.     Dim loADODBRecordset As ADODB.Recordset
  19.    
  20.     lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
  21.                             "DBQ=" + ActiveWorkbook.FullName + ";" & _
  22.                             "ReadOnly=True"

  23.                
  24.     lcCommandText = "SELECT 序號, 姓名,  核定本薪,  核定績效 , 薪資總額 , 勞保薪資 , 勞退薪資 , 健保薪資, " & _
  25.                 " 勞退 , 職災, 普通事故, 就業保險, 工資墊償, 全民健保, 全民健保舊, " & _
  26.                 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _
  27.                 "FROM " & "[" & Input_Sht & "$" & "A1:P32]"
  28.                
  29. '    Work
  30. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(0.1)) + ')'  as 勞退個人 " & _

  31. ' 不 Work
  32. '1 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  trim(str(Labor_Pension_Personal_rate)) + ')'  as 勞退個人 " & _
  33. '2 "  '=Multiply_2_No_Round_Down_4_Up_5(' + trim(str(勞退)) + ',' +  Labor_Pension_Personal_rate + ')'  as 勞退個人 " & _

  34.     Set loADODBConnection = CreateObject("ADODB.Connection")
  35.     Set loADODBRecordset = CreateObject("ADODB.Recordset")
  36.    
  37.     Debug.Print lcCommandText

  38.     loADODBConnection.Open lcConnectionString
  39.     loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1



  40.     Sheets(Output_Sht).Select

  41.     Dim r, f As Integer
  42.     r = 1
  43.     For f = 0 To loADODBRecordset.Fields.Count - 1
  44.         Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name
  45.     Next
  46.      
  47.     While Not loADODBRecordset.EOF
  48.         r = r + 1
  49.         For f = 0 To loADODBRecordset.Fields.Count - 1
  50.             Sheets(Output_Sht).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
  51.         Next
  52.         
  53.         loADODBRecordset.MoveNext
  54.     Wend

  55. End Sub
複製代碼

TOP

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]"
複製代碼
懂得發問,答案就會在其中

今日の一秒は  明日にない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

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

TOP

回復 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]"
學海無涯_不恥下問

TOP

是的,版主我試看看。

TOP

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

TOP

回復 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]"

TOP

回復 7# Baracuda


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

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

TOP

        靜思自在 : 站在半路,比走到目標更辛苦。
返回列表 上一主題