Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Excel VBA Debugging Demo (Read 4081 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Excel VBA Debugging Demo
21.08.07 at 10:09:26
Excel VBA Debugging Demo
Some customers ask me to help with VBA code that was manually written or recorded with the macro recorder. In this topic I would like to show how you can debug the VBA code yourself too. It is actually very easy to do.
I need to investigate the following:
A select statement using a to clause is run and the data is expected to be sent to a certain sheet:
select * from mytable to sheet3;
Unfortunately the data is sent to the current sheet. This is the macro code that is run (this was recorded with Excel):
    SQLXL.Sql.setTextFromWorkSheet Range("$A$27"), True
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    With Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = False
	  .Sort = False
	  .StartFromCell = "$A$6"
	  .Transpose = False
	  .SQLInNote = True
    End With
    With SQLXL.Sql.Statements(1)
	  .ShowParametersDlg = False
	  .ShowResultsetDlg = False
    End With

I suspect the problem to be in the line: .StartFromCell = "$A$6". It may be that the location set in the to clause of the select statement is overwritten here with the address A6 of the current sheet. I decide to put a breakpoint on that line so I can see what happens. To put a breakpoint, put the cursor on the line and press F9. VB will color the line red.
Now we want to start the macro and have a look at variables and objects when we arrive at our breakpoint. I decide to run the macro by pressing F8 and I use Shift F8 to step through the macro line by line. You can also run the macro from the macro menu in Excel or from buttons that you may have created on the worksheet.
The execution of my macro has now stopped at the line where we put the breakpoint. The line is colored yellow to show it is the current line of execution. I want to have a look at the .StartFromCell property to see what its value is before the macro assigns "$A$6" to it. We need to create a variable watch.  From the debug menu, select the option to add a watch. A dialog appears where you can enter the watch variable name. I enter: targets(litExcel) as this is the object that has the StartFromCell property. I enter the whole object as a watch as I may also want to inspect the other properties of it.
VB has now added the targets(litExcel) watch. Press the + icon to expand the item and you will see all its properties. The value is set to D12, the location I had just put some results. I add another watch: SQLXL.Sql.Statements(1)
Inspecting its properties I see that the selectto property is set to the correct output location.
I decide to try to run it without the line that sets the StartFromCell property. I add a single quote before the line to comment it out and I start the macro again. I press F8 to start the macro and Shift F8 to step through the macro line by line. In the current investigation I see that the removal of the line does not resolve the issue. I recommended the customer to put the correct location into the macro code instead of in the to clause of the select statement. I now need to figure out how to fix this small issue in SQL*XL. When run interactively the select to clause works fine however in a macro it does not...
Back to top
« Last Edit: 21.08.07 at 10:29:45 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1