I created a file with functions in VBA .xla that can be configured as an excel add-in, but after using the functions in any spreadsheet on my machine and sending the spreadsheet to someone else it can not visualize the function return is that #NAME, obs: I set the xla on the machine of the person I sent tbm.
Sub conectaBD()
Dim MySQLdrive As String
If (cn Is Nothing) Then
Set cn = New ADODB.Connection
End If
If Not (cn.State = adStateOpen) Then
MySQLdrive = Get_Driver()
With cn
.ConnectionString = "Driver={" & MySQLdrive & "};Server=" & Server_Name & ";DATABASE=" & DataBase & ";User=" & User_ID & ";Password=" & Password & ";Option=3;"
.Open
End With
End If
End Sub
Function consultaValorPL(cod_Operacao As Integer, data As Date) As Double
Dim SQLStr As String
consultaValorPL = 0#
SQLStr = "CALL listar_pu_por_data('" & Format(data, "yyyy-mm-dd") & "', " & cod_Operacao & ")"
Call conectaBD
Set rs = cn.Execute(SQLStr)
If Not (rs.EOF) Then
consultaValorPL = rs("pl").Value
Else
Exit Function
End If
End Function
Function Get_Driver() As String
Const HKEY_LOCAL_MACHINE = &H80000002
Dim l_Registry As Object
Dim l_RegStr As Variant
Dim l_RegArr As Variant
Dim l_RegValue As Variant
Get_Driver = ""
Set l_Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\.\root\default:StdRegProv")
l_Registry.enumvalues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", l_RegStr, l_RegArr
For Each l_RegValue In l_RegStr
If InStr(1, l_RegValue, "MySQL ODBC", vbTextCompare) > 0 Then
Get_Driver = l_RegValue
Exit For
End If
Next
Set l_Registry = Nothing
End Function
Thank you in advance for the help.