Debug code in workbook open or activate events

  • Thread starter Thread starter Guest
  • Start date Start date
Clara,

To debug the workbook open event, you can use the Stop statement at the
point where you want to start debugging. For example:

Private Sub Workbook_Open()

Stop 'this will suspend code execution and open the debugger. like
setting a breakpoint.

'your code here..
MsgBox "Hello, World"

End Sub

Save and close the workbook. Then re-open it. When the workbook open code
executes, it will suspend execution at the point where you put the Stop
statement and take you to the VBE.

To debug the activate event, you can use breakpoints. In the VBE, put the
cursor on the line of code where you want to start debugging, then press F9.
Go to the Excel application window and switch to a different workbook then
back again to the one you want to debug and it will trigger the activate
event.
 
Go to the VBE

Look at the Project explorer to the left. It shows your workbooks in a tree
like formation.

Find the sheet if it is a Worksheet event and double click the sheet...
If it is a workbook event, double click the workbook icon.

This should atleast bring up the code... then go through the code by
pressing F8 to see where you get your problem.
 
Courtesy of Randy Birch

Option Explicit

Private Declare Function SQLDataSources Lib "odbc32.dll" _
(ByVal hEnv As Long, _
ByVal fDirection As Integer, _
ByVal szDSN As String, _
ByVal cbDSNMax As Integer, _
pcbDSN As Integer, _
ByVal szDescription As String, _
ByVal cbDescriptionMax As Integer, _
pcbDescription As Integer) As Long

Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
(ByVal HandleType As Integer, _
ByVal InputHandle As Long, _
OutputHandlePtr As Long) As Long

Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
(ByVal EnvironmentHandle As Long, _
ByVal dwAttribute As Long, _
ByVal ValuePtr As Long, _
ByVal StringLen As Long) As Long

Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
(ByVal HandleType As Integer, _
ByVal Handle As Long) As Long

Private Const SQL_MAX_DSN_LENGTH As Long = 32
Private Const SQL_MAX_DESC_LENGTH As Long = 128
Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT As Long = 1
Private Const SQL_NULL_HANDLE As Long = 0
Private Const SQL_HANDLE_ENV As Long = 1
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Const SQL_IS_INTEGER As Long = (-6)

Private Sub GetUserSystemDSN()
Dim hEnv As Long
Dim sServer As String
Dim sDriver As String
Dim nSvrLen As Integer
Dim nDvrLen As Integer
Dim lstDSNs As String

If SQLAllocHandle(SQL_HANDLE_ENV, _
SQL_NULL_HANDLE, hEnv) <> 0 Then

If SQLSetEnvAttr(hEnv, _
SQL_ATTR_ODBC_VERSION, _
SQL_OV_ODBC3, _
SQL_IS_INTEGER) <> 0 Then


sServer = Space$(SQL_MAX_DSN_LENGTH)

Do While SQLDataSources(hEnv, _
SQL_FETCH_NEXT, _
sServer, _
SQL_MAX_DSN_LENGTH, _
nSvrLen, _
sDriver, _
SQL_MAX_DESC_LENGTH, _
nDvrLen) = SQL_SUCCESS

lstDSNs = lstDSNs & Left$(sServer, nSvrLen) & vbNewLine

sServer = Space$(SQL_MAX_DSN_LENGTH)

Loop

End If 'If SQLSetEnvAttr

Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)

End If 'If SQLAllocHandle

MsgBox lstDSNs

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top