Board logo

標題: [發問] VBA使用 SQL 指令，User Defined Function(使用者自訂函數)，如何輸入2個參數。 [打印本頁]
作者: Baracuda    時間: 2012-1-28 15:47     標題: 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 勞退個人 " & _
作者: Baracuda    時間: 2012-1-28 15:49     標題: 輸入2個參數 Source Code。

  1. Option Explicit
  2. Option Base 0

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

  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

  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"

  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 勞退個人 " & _

  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 勞退個人 " & _

  40.     Set loADODBConnection = CreateObject("ADODB.Connection")
  41.     Set loADODBRecordset = CreateObject("ADODB.Recordset")
  42.    
  43.     Debug.Print lcCommandText

  45.     loADODBConnection.Open lcConnectionString
  46.     loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1



  50.     Sheets(Output_Sht).Select

  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

  67. End Sub
複製代碼
作者: kimbal    時間: 2012-1-31 13:54

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    時間: 2012-2-2 12:30

Error Message 參數太少，預期個數1
還是有問題。
我尚不知如何解。
可能用 SQL 查出後再另用 VBA 寫過。
作者: Hsieh    時間: 2012-2-2 14:04

回復 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    時間: 2012-2-4 08:41

是的，版主我試看看。
作者: Baracuda    時間: 2012-2-4 08:44

Hsieh 版主：
我的意思不是要將字串中的常數用變數取代嗎。
但是語法好像只能用欄位。
如果VBA 中設定的變數(or常數)會有問題。
作者: Scott090    時間: 2015-9-24 09:15

回復 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    時間: 2015-9-24 10:22

回復 7# Baracuda


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

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



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)