View Full Version : Solved: Making Scripts More Efficient
I have a number of scripts that takes certain actions on specific columns within a worksheet (database). Currently I have my column references hard coded into my scripts. However, each time I expand my database, add or rearrange columns, it renders some of my scripts obsolete until I manually go in and change all the column references.
I've been thinking about creating a function to "Find" the appropriate heading and capture the current column number so that the scripts run regardless of whether a column has been moved, and so that I don't have to continue to manually update my scripts.
Is such an approach reasonable in terms of efficiency or would it have more of a negative effect than its worth?
mikerickson
04-02-2011, 01:03 PM
One approach would be to use named ranges for your column headers.
If you have NameHeader as a named cell, the VB reference Range("NameHeader") will adjust to Inserting and Deleting, while Range("B1") will not.
One approach would be to use named ranges for your column headers.
If you have NameHeader as a named cell, the VB reference Range("NameHeader") will adjust to Inserting and Deleting, while Range("B1") will not.
Hmmm. I can see how that would be more efficient constantly "finding" the new column number. Thanks.
mdmackillop
04-02-2011, 02:13 PM
If efficiency is what you are after, have a look here (http://www.decisionmodels.com/)
If efficiency is what you are after, have a look here (http://www.decisionmodels.com/)
Thanks.
Paul_Hossler
04-02-2011, 02:28 PM
Another way -- I have a 'toolbox' workbook of subs and functions that I use for 'List' type of worksheets.
I just copy the module(s) I need into the workbook in development. and then just call my own little library. Makes reusing a lot easier. When I seen something clever in here in the forums, I just incorporate it into my toolbox.
So I have these for finding the column on a WS by heading and returning the number. Since I usually want to do something with or to the data, the column number is typically used with the other functions that return a range of some sort.
Option Explicit
Sub drv()
MsgBox ListColData("MasterData", "DDD").Address
MsgBox ListColData("MasterData", "AAA", 2, False).Address
End Sub
'take ws name and column header and return colnum number, 0 if not there
Function ListColNum(ws As String, s As String) As Long
ListColNum = 0
On Error Resume Next
ListColNum = Application.WorksheetFunction.Match(s, Worksheets(ws).Rows(1), 0)
On Error GoTo 0
End Function
Function ListColRange(ws As String, s As String, Optional IncludeHeader As Boolean = True) As Range
Dim r As Range
Set ListColRange = Nothing
On Error Resume Next
Set r = Worksheets(ws).Cells(1, ListColNum(ws, s))
On Error GoTo 0
If IncludeHeader Then
Set ListColRange = r
Else
Set ListColRange = r.Cells(2, 1).Resize(r.Rows.Count - 1, 1)
End If
End Function
'returns a range object of the data cells in a col or cols. Optional include headers
Function ListColData(ws As String, ColName As String, _
Optional NumCols As Long = 1, _
Optional DataOnly As Boolean = True) As Range
Dim r As Range
Set ListColData = Nothing
If ListColNum(ws, ColName) = 0 Then Exit Function
If DataOnly And Worksheets(ws).Cells(1, 1).CurrentRegion.Rows.Count = 1 Then
Set ListColData = Nothing
Exit Function
End If
With Worksheets(ws)
Set r = Intersect(.Columns(ListColNum(ws, ColName)), .Cells(1, 1).CurrentRegion)
If DataOnly Then
Set r = r.Cells(2, 1).Resize(.Cells(1, 1).CurrentRegion.Rows.Count - 1, NumCols)
Else
Set r = r.Cells(1, 1).Resize(.Cells(1, 1).CurrentRegion.Rows.Count, NumCols)
End If
End With
Set ListColData = r
End Function
SOmetimes I do need the column number, but I still use the ListColNum function
iStartDateCol = ListColNum ("Master", "S_Date")
.....
rRow.Cells (i, iStartDateCol).Value = #4/1/2011#
Paul
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.