I added 3 subs where I suspect the problem might be. The first sub is
called by command button which calls the GetFund_ID function. The
latter connects to the DB and should retrieve the IDs. I also added
the EnsureConnection Sub, though it does not seem to be a problem
because it is used in other codes.
The worst thing is that I cannot trace the bug because if i open the
VBA window, the code works.

-------------------------
Public Sub ImportButton_Click()
Dim i As Integer
Dim Error As Integer
Dim Result As Variant

On Error GoTo Handler:
Error = 0

'retrieve fund_id's from DB

For i = 1 To 50
If Sheet9.Cells(9, i + 1) = "" Then Exit For
Call GetFund_ID(Sheet9.Cells(9, i + 1), Result)
Sheet9.Cells(10, i + 1).Value = Result
Next i

<..importing..

'restore connection with DB. I am using 2 DBs, therefore i have to
restore connection with the default one. The RestoreConnection sub is
almost the same as EnsureConnection, just with different parameters.

RestoreConnection

MsgBox ("The import is finished")

If Error = 1 Then
MsgBox ("Connection with DBfailed. Please try again.")

End If

Exit Sub

Handler:
Error = 1
Resume Next

End Sub


-------------------------------------------------


Public Sub GetFund_ID(RefCol As String, Result As Variant)
Dim strSQL As String

If Application.VBE.MainWindow.Visible Then
Debug.Print "System Report:" & strSQL


strSQL = strSQL & "SELECT Fund_id FROM Funds WHERE Fund_Name like
'%"
strSQL = strSQL & RefCol
strSQL = strSQL & "%'"

EnsureConnection

Dim rs As Recordset: Set rs = OpenRecordsetCache(Cache, Cn, strSQL)
Result = ADODBExcel.FormatRecordset(rs)

End If

End Sub


------------------------------------------

Public Sub EnsureConnection()
Dim SQLString As String

SQLString = "Provider=SQLOLEDB.1;Password=Password1;"
SQLString = SQLString & "Persist Security Info=True;User
ID=User1;"
SQLString = SQLString & "Initial Catalog="
SQLString = SQLString & CStr([DBNAME])
SQLString = SQLString & ";Data Source=ServerName
\DatabaseName;"
SQLString = SQLString & "Use Procedure for Prepare=1;Auto
Translate=True;"
SQLString = SQLString & "Packet Size=4096;Workstation
ID=PCID;Use Encryption for Data=False;"
SQLString = SQLString & "Tag with column collation when
possible=False"

Set Cn = New ADODB.Connection
Cn.ConnectionString = SQLString

Cn.Open

SaveSetting "DBName", "Database", "ConnectionString",
Cn.ConnectionString
End Sub