Why does this xls vba select fail sometimes - no change in the spreadsheet?

Question

Can someone tell me if I'm coding this poorly. This select seems to fail sometimes. By this I mean I haven't changed the xls itself but I may just examine the data and then rerun the calling routine and it may fail with an error of "select method of range class fails"

My goal with this routine is to change the color of a column of data. From row 1 through the last row used. When this runs

cnum = 16 <-- which is the column number I want to highlight

lrow = 1418 <-- which is the last row with data

Sub HighlightColumn(colname As String, sheet As Worksheet, color As Long)

Dim cnum As Integer
Dim lrow As Long
Dim lcol As Integer
Dim r As Range


  lcol = sheet.UsedRange.Columns.Count
  lrow = RowCount(sheet)

'get column number cnum with the name colname

  For i = 1 To lcol
      If (sheet.Cells(1, i).Value = colname) Then cnum = i
  Next i

'create range

   Set r = sheet.Range(sheet.Cells(1, cnum), sheet.Cells(lrow, cnum))
   r.Select  'this is the statement that fails

'set the color

    Selection.Interior.color = color


End Sub

This may help https://stackoverflow.com/questions/17559677/select-range-in-a-particular-sheet-in-excel-vba — twoleggedhorse, Sep 26 '17 at 22:32
This too https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — twoleggedhorse, Sep 26 '17 at 22:34
cnum could be declared as Long, rather than Integer(Integer is problematic and has little value as a datatype in VBA). — MacroMarc, Sep 26 '17 at 23:42

score 2 · Answer 1 · answered Sep 27 '17 at 00:52

2

The "Range.Select" method needs the worksheet where selection is being made to be visible.

'create range

Set r = sheet.Range(sheet.Cells(1, cnum), sheet.Cells(lrow, cnum))

' add the following command and r.Select below will work just fine
sheet.Activate

r.Select  'this is the statement that fails

'set the color

However, why not just

r.Interior.color = color

and be done with it?

answered Sep 27 '17 at 00:52

thanks and yes you are right on the simple approach. For some reason i thought i tried that and ran into an issue so i was expeimenting – Cooper5114 Sep 29 '17 at 00:36

Why does this xls vba select fail sometimes - no change in the spreadsheet?

1 Answers1