VBA - Opening a file within a macro

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
Hi, when I open a file within a macro, I'm typically hitting F8 so that I can see if all the step are working properly, but when I open a file within the macro, it runs the macro fully, is there anyway to prevent this so that I can just keep hitting F8 to see if I have an errors and the information is working correctly.

[ub Filter()Dim lRow As Long
Dim fd As Office.FileDialog


lRow = ActiveSheet.Range("A500").End(xlUp).Row
If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A500").End(xlUp).Row

Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[2],'[Finance Extract1.xls]Sheet1'!R8C4:R500C4,1,FALSE)"
Selection.AutoFill Destination:=Range("B9:B500")
Range("B9:B500").Select

Set fd = Application.FileDialog(msoFileDialogFilePicker)


'open finance rec
With fd
.AllowMultiSelect = False
.Title = "Please select the file."

.Filters.Clear

If .Show = True Then
txtfilename = .SelectedItems(1)
Workbooks.Open txtfilename
End If]
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,127
.
Hmmm ... as you have learned, the purpose of F8 is to allow you to execute one line at a time.

Another method I've used is to insert a MsgBox after the line I want to test. As it progresses, the MsgBox
is moved down one line at a time.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,127
.
Your example will work.

Or just MsgBox ""

Debug.Print is another method.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,053
Office Version
365
Platform
Windows
Select the line of code after you open the other workbook, hit F9 then step through with F8
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
how do you use debug print and what does it mean
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
even when I use the msg "stop", it is still running through the macro. How do I use debug print to stop this from happening
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,053
Office Version
365
Platform
Windows
Debug.print won't stop it, it simply writes information to the immediate window.

Do you mean that you have the word Stop in the code & it doesn't actually stop ?
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
yes, I have msg "stop", and a message box appears and I click Ok, then it keeps moving through the macro until the next msg "stop" box appears, but I want it to actually stop and then I can click F8 again to move through the macro myself until I'm confident it fully works as it should. Once I'm confident, I will let it run on it's own, but right now I'm just trying to test all this to ensure the macro works properly.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,303
Messages
5,449,556
Members
405,571
Latest member
Flux1234

This Week's Hot Topics

Top