Hello! I was testing 2 diferet ways one I got but the problem is that only runs with the makro not with the Function (taht is the goal) and the oder It's with ADO that I don't really know but I think it goes to the write file but don't take the information.
I post the 2 tries and an example of the database that is inside a excel file in oder hard drive (c:\ for example)
EXAMPLE that runs with makro (easy way) there is a mixture with german and spansh sorry

Originally Posted by
Ken Puls
Can you post a set of basic example workbooks? Take out anythind confidential and we'll see if we can come up with an elegant solution.

Code:
Function PerdidaPresion(ByVal Dichte As Single, ByVal Geschwindigkeit As Single, ByVal Viskositat As Single, ByVal Parametro As String) As Double
Dim RutaActual, NombreFichero, fic As String
Dim FilaInicial, FilaFinal, fil As Integer
Dim a, b, w, d As Single
Dim solucion, Beta As Double
'calculo el nombre de la base de datos ubicada en la misma carpeta
RutaActual = "C:\Dokumente und Einstellungen\glopez\Desktop\DATOS"
NombreFichero = "KATALOG2.xls"
fic = RutaActual & "\" & NombreFichero
'intento abrir la BD. si no puedo, lanzo una excepcion
On Error GoTo GestionErrores
'abro la BD
Workbooks.Open fic
a = -1
b = -1
FilaInicial = 1
FilaFinal = 256
'busco el parametro en la col1 y extraigo a y b de las cols 2 y 3
For fil = FilaInicial To FilaFinal
If Cells(fil, 1).Value = Parametro Then
a = Cells(fil, 5).Value
b = Cells(fil, 6).Value
Beta = Cells(fil, 7).Value
d = Cells(fil, 4).Value
Exit For
End If
Next
ActiveWorkbook.Close
'si no se encontraron a y b. aplico la formula alternativa
If (a = 0) And (b = 0) Then
MsgBox Beta
MsgBox d
solucion = (((1 - Beta) / (Beta ^ 2)) * (0.72 + ((49 * Beta * Viskositat) / (Dichte * Geschwindigkeit * d * 10 ^ (-6))))) * Dichte * ((Geschwindigkeit ^ 2) / 2) / 10 ^ (5)
PerdidaPresion = solucion
Exit Function
End If
MsgBox a
MsgBox b
'si encuentro a y b aplico la formula principal
solucion = ((((a + ((b * Viskositat) / (Dichte * Geschwindigkeit * 10 ^ (-6)))) * Dichte * ((Geschwindigkeit ^ 2) / 2))) / 10 ^ (5))
PerdidaPresion = solucion
Exit Function
GestionErrores:
MsgBox "No se pudo abrir " & NombreFichero, , "Error " & Err.Number
Exit Function
End Function
Code:
Sub probarFormula()
Dim res As Double
res = PerdidaPresion(800, 2, 0.01, "122/90")
Cells(2, 3).Value = res
End Sub
THE SECOND TRY I would like to do in this way or another that you know
Code:
Function PerdidaPresion(ByVal Dichte As Single, ByVal Geschwindigkeit As Single, ByVal Viskositat As Single, ByVal Name As String) As Double
Dim RutaActual, NombreFichero, fic As String
Dim FilaInicial, FilaFinal, fil As Integer
Dim a, b, w, d As Single
Dim solucion, Beta As Double
'Geschwindigkeit=velocity Viskositat= viscosity dichte= density
'dimensions
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim recCampo As ADODB.Field
Dim strDB, strSQL As String
Dim i As Long
'going to the file in oder folder
strDB = "C:\Dokumente und Einstellungen\glopez\Desktop\DATOS\KATALOG2.xls"
'conecting
Set datConnection = New ADODB.Connection
Set recSet = New ADODB.Recordset
datConnection.Open "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & strDB
'consulta SQL
'strSQL = "SELECT * FROM [NuestroRango]"
strSQL = "SELECT * FROM [$A1:Q1000]"
'opening the recordset(I don't know if is correct
recSet.Open strSQL, datConnection, adOpenStatic
'Copy dates
a = -1
b = -1
FilaInicial = 1
FilaFinal = 200
'For fil = FilaInicial To FilaFinal
' If Cells(fil, 1).Value = Name Then
' a = Cells(fil, 4).Value
' b = Cells(fil, 5).Value
'Beta = Cells(fil, 6).Value
'd = Cells(fil, 3).Value
'Exit For
'End If
' Next
For fil = FilaInicial To FilaFinal
If Cells(fil, 1).Value = Name Then
a = Cells(fil, 4).Value
b = Cells(fil, 5).Value
Beta = Cells(fil, 6).Value
d = Cells(fil, 3).Value
Exit For
End If
Next
'closing
recSet.Close
datConnection.Close
'close objects
Set recSet = Nothing
Set datConnection = Nothing
'If a & b is 0 I take the oder constant and operate
If (a = 0) And (b = 0) Then
MsgBox Beta
MsgBox d
solucion = (((1 - Beta) / (Beta ^ 2)) * (0.72 + ((49 * Beta * Viskositat) / (Dichte * Geschwindigkeit * d * 10 ^ (-6))))) * Dichte * ((Geschwindigkeit ^ 2) / 2) / 10 ^ (5)
PerdidaPresion = solucion
Exit Function
End If
MsgBox a
MsgBox b
'If I find a & b I make the operation
solucion = ((((a + ((b * Viskositat) / (Dichte * Geschwindigkeit * 10 ^ (-6)))) * Dichte * ((Geschwindigkeit ^ 2) / 2))) / 10 ^ (5))
PerdidaPresion = solucion
Exit Function
GestionErrores:
MsgBox "No se pudo abrir " & NombreFichero, , "Error " & Err.Number
Exit Function
End Function
Code:
Sub probarFormula()
Dim res As Double
res = PerdidaPresion(800, 2, 0.01, "20/20")
Cells(1, 3).Value = res
End Sub
EXAMPLE OF DATA BASE
name name W D a b Beta
20/20 20/20 20 20 3 4 0.25
25/25 25/25 25 25 5.211 4 0.25
32/25 32/25 32 25 7.422 4 0.3151739
32/28 32/28 32 28 9.633 4 0.28444444
36/28 36/28 36 28 11.844 4 0.31640625
38/25 38/25 38 25 0 0 0.3638196
40/23 40/23 40 23 0 0 0.40312421
40/25 40/25 40 25 0 2.85 0.37869822
40/28 40/28 40 28 0 2.85 0.34602076
40/32 40/32 40 32 0 2.85 0.30864198
42/36 42/36 42 36 3.9 2.85 0.28994083
45/18 45/18 45 18 3.9 2.85 0.51020408
45/32 45/32 45 32 3.9 2.85 0.34154158
45/36 45/36 45 36 3.9 2.85 0.30864198
Thanks's for your help!!
Bookmarks