Macro Halts when executing Function/sub 1

[hal8000]

I have a sub that dynamically sets the print area of an Excel sheet. It works perfectly when I run it from the VB editor. I set it up to be run by a function so that I can use it as a macro. I can run the function which runs the sub in the editor and it works fine. When I setup the macro to run it halts. The only message I get is action failed. I did have problems with the sub leaving an Excel process running and have added an End statement to the procedure which fixes the hanging process but seems to cause the macro to halt. If I take the end out, the macro runs but an Excel process is left running. Has this happened to anyone else? Thanks in advance for any ideas.

 

[dyarwood]

Where are you calling this macro from?

You could try quiting Excel within the process calling the function or closing the excel spreadsheet.

 

[hal8000]

I am just running the macro from the Macro setup are in Access. Here is the code. It works correctly when run from the VB editor.

Sub FindUsedRange()
Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Open "F:\My Documents\weeklyrpt.xls"
xl.Worksheets.Select (Sheet2)
Dim LastRow As String
Dim LastCol As Integer
Dim answer As String
Dim colno As Integer
' Find the LAST real row
i = 2
Do While i < 400

If Cells(i, 1) <= 0 Then Exit Do
LastRow = i
i = i + 1
Loop
' Find the LAST real
i = 2
Do While i < 60
If Cells(1, i) = "Comments" Then Exit Do
LastCol = i
i = i + 1
Loop
LastCol = LastCol + 1
answer = colno2colref(LastCol)
Columns("L:BD").ColumnWidth = 8.43
Columns(answer & ":" & answer).ColumnWidth = 56.67
'Debug.Print LastCol, LastRow, answer
'Select the ACTUAL Used Range as identified by the
'variables identified above

ActiveSheet.PageSetup.PrintArea = "$A$1:$" & answer & "$" & LastRow
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close
xl.Quit
Set xl = Nothing
End
End Sub

 

[dyarwood]

Is this a function?

colno2colref(LastCol)

 

[PHV]

Prefix all implicit references to Excel Application by xl. like xl.Cells, xl.Columns, ...
And then, get rid of the End instruction.
To catch all this implicit instantiation of excel, use late binding (CreateObject instead of New) and remove the reference to the excel library object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

BTW, you must have Option Explicit

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hal8000]

Thanks "PHV" that fixed it. I can usually piece together what I need to do but don't always know the why of some of the coding. Thanks again.