How do you Debug without an error message?

[aquixano]

The following macro imports data from a .csv file and replaces the existing information on the worksheet “ImportedData.” I’ve done similar imports in other macros without issue. Stepping through this code in an attempt to debug it, it gets to the line “Selection.ClearContents” and the macro appears to end. I say “appears” because it doesn’t give an error message, it just clears the contents of the worksheet, but doesn’t highlight the next line of code “WL = ActiveWorkbook.Name.” My first thought was I’d created an endless loop, but I don’t get the hour glass, and if you click “Step Into” again it doesn’t beep it actually starts over at the beginning. The code up to that point looks pretty vanilla to me – does anyone see something that could cause this behavior / how can I debug this type of problem.

Sub Import()
Dim strColumn As String
Dim lngLastRow As Long
Dim WL As String
Dim path As String
Dim startdate As Date
Dim TOSexport As String
Dim findit As String

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
   ' .ScreenUpdating = False
End With

With ThisWorkbook.Sheets("Scratch Pad")
    .Columns("A:A").Value = ThisWorkbook.Sheets("ImportedData").Columns("B:B").Value
    .Columns("B:B").Value = ThisWorkbook.Sheets("ImportedData").Columns("A:A").Value
End With

ThisWorkbook.Sheets("ImportedData").Select
strColumn = "B"
lngLastRow = ThisWorkbook.Sheets("ImportedData").Cells(ThisWorkbook.Sheets("ImportedData").Rows.Count, strColumn).End(xlUp).Row

ThisWorkbook.Sheets("ImportedData").Rows("$6:$" & lngLastRow).Select

'****************************
Selection.ClearContents '*******Macro "ends" at this point - if stepping through, after this step when you click Step Into again the marco starts over
'****************************

WL = ActiveWorkbook.Name

ChDrive Range("StoreDrive").Value
path = Range("StoreDirectory").Value

startdate = Date
startdate = Application.InputBox("Enter date to be imported", "Confirm Date", FormatDateTime(startdate, vbShortDate), Type:=1)

TOSexport = Format(startdate, "yyyy-mm-dd") & "-QuoteZ.csv"

findit = Dir(TOSexport)
If Len(findit) <> 0 Then
    MsgBox "Export Watchlist and rerun update"
Else
    Workbooks.OpenText Filename:=path + TOSexport, Origin _
        :=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1)), TrailingMinusNumbers:=True

Columns("A:Y").Select
Selection.copy

Workbooks(WL).Activate
Sheets("ImportedData").Activate
Columns("B:Z").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
lngLastRow = ThisWorkbook.Sheets("ImportedData").Cells(ThisWorkbook.Sheets("ImportedData").Rows.Count, strColumn).End(xlUp).Row

ThisWorkbook.Sheets("ImportedData").Range("$A$5").Value = "=INDEX('Scratch Pad'!A:B,MATCH(ImportedData!B5,'Scratch Pad'!A:A,0),2)"
'****************************
ThisWorkbook.Sheets("ImportedData").Range("$A$5:$A$" & lngLastRow).Select
Selection.FillDown

Application.DisplayAlerts = False
Workbooks(TOSexport).Close
Application.DisplayAlerts = True
Sheets("Interactive Allocation").Select

With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End If
End Sub

(fyi – I’ve attached the file containing this code and it should run through the error point if you want to test it, however, without the import file and setting up the necessary file directories it won’t run to completion.)
Thanks for any help.
aquixano

[shg]

Is the correct range selected when that line executes?

[aquixano]

Yes. The variable LngLastRow makes the range dynamic since the number of rows that the input may have can vary, but it is selecting the second row of data through the last line of data as intended.

[shg]

Before that line executes, what does this return in the Immediate window?

? typename(selection)

[aquixano]

? typename(selection) returns
Range

[aquixano]

I don't know if this is of any help, but I thought if an error was occurring without displaying an error message I might be able to trick it with "On Error Resume Next." I put this line immediately before the line where the code stops - didn't make any difference - the macro still starts over when F8 is pressed.

[shg]

I'm blanking on anything that would cause this. If no one has a better suggestion. you might reboot, and failing that, repair Excel.

Oh -- before doing the repair, set the VBE to Break on all errors and see what happens.

[aquixano]

Well, it looks like there's good reason for you blanking - some corruption may have crept into my workbook. I took the problematic code and copied it into a new workbook. After duplicated enough sheetnames etc. for it to run, it did run without issue in the new workbook. I hate to completely recreate what I already have - guess I'll try and iteratively copy stuff from the old workbook to a new one until the problem recurs and then just recreate that part. First I think I'll try saving it as an XLSX file, stripping out the modules and then copy the VBA back in - see if I can get lucky that way. Any suggestions for a better approach?

btw - I've never used "Break on all errors" - is that a line of code that I have to insert? I didn't see it on the Debug menu, so I'm not sure how to invoke it. Thanks for the assistance, shg.

[shg]

Any suggestions for a better approach?

AppsPro CodeCleaner

"Break on all errors"

In the VBE, Tools > Options > General, Error Trapping

[aquixano]

Thank you.

[shg]

You're welcome. I should have suggested CodeCleaner (or the manual equivalent) earlier.

[aquixano]

Post Mortem – During the process of recreating the workbook, I discovered the problem – it wasn’t corruption per se, but apparently some conflict with the following UDF I found on the web:

Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function

I was using this UDF in a conditional format to highlight whether a formula had been overwritten with a value since the last data import. Those cells were included in the “Selection.ClearContents” command. Removing that conditional format allowed the macro to run through completion. I have modified that macro to clear the conditional formatting at the start and then reapply it before the End Sub. While it appears to work, I’m not overwhelmed with confidence using potentially problematic code/UDF. With this lone exception, the UDF seems fine to me. Removing the CF and just leaving it off wouldn’t be the end of the world; however I do like having hard coded values highlighted. Do you know another away to test if a cell is a formula / do you think my initial solution of deleting & reapplying the CF is reasonable? Thanks again for your assistance.

[shg]

CF is super-volatile, and should be used judiciously.

In my Book.xtlx template, I have about 10 styles compared to the default 60-odd, and use them (and almost always only them) in every workbook. They allow me to see at a glance what's what on a worksheet. Most are shown below:

Row\Col	A	B	C	D
1	Label	Label	Normal	Normal
2	Input	Formula	Normal	Validated
3	Input	Formula	Normal	Remark
4	Input	Formula	Normal	Model
5	Input	Formula	Normal	Normal
6	Input	Formula	Normal	Normal

"Normal" to me means unused.

EDIT: Good job sorting it out.

[aquixano]

Shg – CF has been problematic for me too in the past, usually around copying / inserting creating duplicate CF rules - I do find the functionality very handy but I’m not opposed to learning a new way either. Is there some way to use predefined formats in the Book.xtlx file to mimic CF functionality? I’ve done some searching but haven’t located any posts that mention this possibility.

I guess I’m not following your template range you posted – for instance Col A and B appear to be the same. Thanks for the coaching.

[shg]

Is there some way to use predefined formats in the Book.xtlx file to mimic CF functionality? I’ve done some searching but haven’t located any posts that mention this possibility.

No. My point is, I use styles to delimit content. So style "Label" means that the cell is not used in calculation, it's locked (because it's nothing a user should modify), and it's visible elements are light gray fill and a blue font.

"Input" is unlocked, white fill, and black font.

"Validated" is the same as input with bold italic font.

"Formula" is locked and a slightly darker gray.

"Do Not Use" is a patterned fill, which the board doesn't support, so an example is not shown.

Etcetera.

I guess I’m not following your template range you posted – for instance Col A and B appear to be the same.

Col A has a label and a bunch of inputs. Col B has a label and a bunch of formulas. The cell contents in the example are their styles; it was just to illustrate that appearance is set by style (just as you should do in Word), and style indicates type of content. The point is to support doing a mental assay of a sheet at a glance. So, for example,

Row\Col	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Sales	Comm Rate	Delta											Link
4	0	2%	2%
5	500,000	10%	8%
6	1,000,000	20%	10%
7	1,500,000	25%	5%
8	2,000,000	20%	-5%
9
10	Sales		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
11	Person 1		200,000	200,000
12	Person 2				300,000
13	Person 3				50,000	175,000
14	Person 4					75,000	250,000	150,000
15	Person 5							75,000	150,000	150,000	150,000
16
17	Commissions		DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse	DoNotUse
18	Person 1	DoNotUse	4,000	4,000	0	0	0	0	0	0	0	0	0	0
19	Person 2	DoNotUse	0	0	22,000	0	0	0	0	0	0	0	0	0
20	Person 3	DoNotUse	0	0	5,000	17,500	0	0	0	0	0	0	0	0
21	Person 4	DoNotUse	0	0	0	7,500	50,000	30,000	0	0	0	0	0	0
22	Person 5	DoNotUse	0	0	0	0	0	15,000	36,250	37,500	37,500	0	0	0

No need to guess which cells are what.