- Tham gia
- 13/6/06
- Bài viết
- 4,790
- Được thích
- 10,299
- Giới tính
- Nam
- Nghề nghiệp
- Giáo viên, CEO tại Bluesofts
'Create UDF run SQL as BS_SQL
C#:
Function MySQL(ByVal SQL As String, Optional ByVal Options As String = "") As Variant
Dim fa As New BSFormulaArray
Dim fi As New BSFormulaInfo
'Function structure:
If fa.Begin Then
'Create array
fi.FunctionName = "MySQL"
fi.OptionStr = Options
fi.HeaderRow = True
'AResult can be:
' SQL statement
' Array
' Recordset
MySQL = fa.Add(fi, SQL)
Else
'Return array that created
MySQL = fa.Result
End If
'Free memory
Set fi = Nothing
Set fa = Nothing
End Function'Run SQL by callback function
C#:
'Hàm này lấy ra mảng để tùy ý tính toán
Function MySQL2(ByVal SQL As String, Optional ByVal Options As String = "") As Variant
Dim fa As New BSFormulaArray
Dim fi As New BSFormulaInfo
Dim arr
'Function structure:
If fa.Begin Then
'Create array
fi.FunctionName = "MySQL2"
fi.OptionStr = Options
fi.HeaderRow = True
'AResult can be:
' SQL statement
' Array
' Recordset
fi.PARAMS = Array(SQL, Options) ' 0- SQL; 1- Options
fi.lpfnOnGetResult = GetCallbackFunction(AddressOf CallbackResult)
'CallbackResult will create array from SQL
MySQL2 = fa.Add(fi, arr)
Else
'Return array that created
MySQL2 = fa.Result
End If
'Free memory
Set fi = Nothing
Set fa = Nothing
End Function
Function CallbackResult(ByVal fi As AddinATools.IBSFormulaInfo, _
ByVal FmlRange As Range, _
ByVal FmlState As AddinATools.BSFmlState, _
AResult As Variant) As Boolean
If FmlState = fsOnCalc Then
'Create recordset by SQL
Dim fa As New BSFormulaArray
Dim rst As Object ' ADODB.Recordset
Set rst = fa.ExecuteQuery(fi.PARAMS(0), fi.PARAMS(1))
'Create Array
Dim arr
arr = fa.GetArrayFromRecordset(rst, True)
Dim ur&, uc&, I&, J&
ur = UBound(arr, 1): uc = UBound(arr, 2)
ReDim arr2(ur, uc + 1) 'Create new column
'Copy array from arr - > arr2
arr2(0, 0) = "No."
For I = 0 To ur
If I > 0 Then
arr2(I, 0) = I 'for column "No."
End If
For J = 0 To uc
arr2(I, J + 1) = arr(I, J)
Next J
Next I
'Modify arr if you want...
AResult = arr2 'return array to function call it "MySQL2"
CallbackResult = True
Set fa = Nothing
rst.Close ' free memory
Set rst = Nothing
Erase arr ' clear array
ElseIf FmlState = fsBeforeUpdate Then
'Do something
End If
End Function(*) Download sourcecode in video: https://drive.google.com/.../17QpbZQjNY33O.../view...
(*) Download Add-in A-Tools: http://bluesofts.net/.../a-tools-truy-van-loc-soan-thao...
