VBA Code Works When Using F8 but Not When Run Fully

Question

Below is a my called code. It is being called into a code that asks user to open a desired workbook. The called code works properly when I step it through using F8 but it fails to create a proper correlation table when I run the code fully.

Dim nCols As Integer
Dim myRange, myCorrel, c As Range

Range("A1").CurrentRegion.Select
nCols = Selection.Columns.Count

Range("B1").Resize(1, nCols - 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Set myRange = Selection

 ' create correlation table

Application.Run "ATPVBAEN.XLAM!Mcorrel", myRange, _
        "Statistics", "C", True

Selection.Copy

Range("B1").End(xlToRight).Offset(0, 2).Select
Selection.PasteSpecial 

End Sub

My correlation table code fails to capture the data headers. I would receive a bunch of correlation calculated without the original column headers.

Don't do range.select and then use selection.copy instead use range.copy and use paste to required range. — Paresh J, Dec 16 '15 at 04:29
@PareshJ, thanks for the response. I realize the problem is my correlation table code fails to capture data headers. That's what is causing Excel to debug the copy line. WIll need to figure out how to fix the correlation table code. — fastfood123, Dec 16 '15 at 05:11

score 1 · Answer 1 · edited May 23 '17 at 11:53

Unless this,

Application.Run "ATPVBAEN.XLAM!Mcorrel", myRange, _
    "Statistics", "C", True

... changes the selection, then you can avoid relying on what is the current selection altogether. A few nested With ... End With statements can reinterpret your code much the same way you were relying upon the Application.Selection property but referencing everything directly.

Sub vert()
    Dim nCols As Long
    'this is how to declare multiple range objects
    Dim myRange As Range, myCorrel As Range, c As Range

    With Worksheets("Sheet1")   '<~~set this worksheet reference!
        With .Range("A1").CurrentRegion
            nCols = .Columns.Count
            With .Resize(.Rows.Count, nCols-1).Offset(0, 1)  '<~~all of col B over to the right side

                Set myRange = .Cells

                 ' create correlation table
                Application.Run "ATPVBAEN.XLAM!Mcorrel", myRange, _
                        "Statistics", "C", True

                'change formulas to their values
                .Cells = .Value
            End With
        End With
    End With
End Sub

It looks like you take all of column B over to the right end extents of the data and provide some processing with ATPVBAEN.XLAM!Mcorrel. This leaves formulas that you then revert to the formula results (i.e. Range.Value property).

So you can see how using the With ... End With is very much like progressively changing the Selection. The main difference is that it is not going to change due to any outside interference.

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

I am still having the same problem with your new code. Part of my correlation table will go the tab ("Statistics") that I want it to be.but part of it will replace my source data. — fastfood123, Dec 16 '15 at 14:53
It is just too hard to speculate upon without seeing the rest of the code. If it cannot be added to this question through an edit, consider starting a new question which links back to this one. — , Dec 16 '15 at 14:59

VBA Code Works When Using F8 but Not When Run Fully

1 Answers1