I tend to write inefficient code, as an example one of my databases creates an Excel workbook with two sheets one displaying open issues, the other closed. Both sheets are identical in appearance and hence coded identical, the only difference is the query to generate the data. Up until recently I basically repeated the code twice over in the same routine running each in turn with a separate query. But it results in a horrendously big function that is a nightmare to maintain.
So I decided to create a function to produce the content of the worksheet and call it twice with a different query, like below:
This worked a treat hence I won't print the hundreds of lines of code.
Within the function CreateIssuesSheet. I noticed again I had repetition, the are 4 main "collections" of columns each with the same format of header and again in my usual inefficiency these were repeated in the sub routine 4 times over. I saw an opportunity to do a similar thins as for the worksheet and create a subroutine which takes a given range and formats it. However this time it fails with error "Object Required" on line 1460.
The call to the subroutine is shown below
The routine itself is here
Can anyone advise where I am going wrong?
So I decided to create a function to produce the content of the worksheet and call it twice with a different query, like below:
Code:
'Create worksheet 1
870 Call CreateNamedQDF("qdfIssueListByProjectForReportOpen", strSQLIssuesOpen)
872 Set wshOpen = wbk.Sheets(1)
874 wshOpen.Activate
880 Call CreateIssueSheet("qdfIssueListByProjectForReportOpen", wshOpen, "Open and New")
'Create worksheet 2
890 Call CreateNamedQDF("qdfIssueListByProjectForReportClosed", strSQLIssuesClosed)
892 Set wshClosed = wbk.Sheets(2)
894 wshClosed.Activate
900 Call CreateIssueSheet("qdfIssueListByProjectForReportClosed", wshClosed, "Closed")
This worked a treat hence I won't print the hundreds of lines of code.
Within the function CreateIssuesSheet. I noticed again I had repetition, the are 4 main "collections" of columns each with the same format of header and again in my usual inefficiency these were repeated in the sub routine 4 times over. I saw an opportunity to do a similar thins as for the worksheet and create a subroutine which takes a given range and formats it. However this time it fails with error "Object Required" on line 1460.
The call to the subroutine is shown below
Code:
1450 Set rngSelection = wsht.Range("A7:AB7")
1460 FormatSubHeadings (rngSelection)
The routine itself is here
Code:
20 rngSelection.Font.Bold = True
30 With rngSelection.Interior
40 .Pattern = xlSolid
50 .PatternColorIndex = xlAutomatic
60 .ThemeColor = xlThemeColorDark1
70 .TintAndShade = -0.349986266670736
80 .PatternTintAndShade = 0
90 End With
100 With rngSelection.Borders(xlEdgeLeft)
110 .LineStyle = xlContinuous
120 .ColorIndex = 0
130 .TintAndShade = 0
140 .Weight = xlMedium
150 End With
160 With rngSelection.Borders(xlEdgeTop)
170 .LineStyle = xlContinuous
180 .ColorIndex = 0
190 .TintAndShade = 0
200 .Weight = xlMedium
210 End With
220 With rngSelection.Borders(xlEdgeBottom)
230 .LineStyle = xlContinuous
240 .ColorIndex = 0
250 .TintAndShade = 0
260 .Weight = xlMedium
270 End With
280 With rngSelection.Borders(xlEdgeRight)
290 .LineStyle = xlContinuous
300 .ColorIndex = 0
310 .TintAndShade = 0
320 .Weight = xlMedium
330 End With
Can anyone advise where I am going wrong?