What's the best/most efficient way to set Variables for Columns

[Shloime]

I have Excel files with many macros that retrieve or write data across different sheets. I want a reliable way to set variables for specific columns so that:

1. They automatically adjust if columns are added, removed, or rearranged.
2. I can quickly update them if I change a column title (e.g., from "Names" to "First Name").
3. The column variables are accessible across all modules/subs, so I don’t have to redefine them repeatedly.

or example, on "Sheet1" (with VBA code name S1), I have a constant header row (SHr) with column titles (which is not necessarily in row 1). I currently use this code to find the column for "First Name":

S1colFN = Application.Match("First Name", S1.Rows(SHr), 0)

This approach works if "First Name" is moved to a different column. However, I would need to set these variables in each module or sub, so any change in column title (like "First Name" to "Names") means updating each instance manually.

To simplify this, I created a single module with constants for each header row and a function to retrieve the column numbers based on column names:

Public Const NMHDr As Integer = 10

Function ColVar(CV As String) As Integer
    Dim cn As Integer
    Select Case CV
        Case "NMcolHO"
            cn = Application.Match("Holiday", NM.Rows(NMHDr), 0)
        Case "NMcolID"
            cn = Application.Match("ID", NM.Rows(NMHDr), 0)
        Case "NMcolSN"
            cn = Application.Match("Surname", NM.Rows(NMHDr), 0)
    End Select
    ColVar = cn
End Function

the in every Sub i declare the variables i need to use is that like:

Dim NMcolID as integer
NMcolID=colvar("NMcolID")

Is there a more efficient way to manage the column references, making them easier to maintain and update across multiple modules?

 

[dmt32]

Hi
Suggest you consider converting your ranges to Table (ListObjects) which enables you to reference Table Columns By Name

Dave

 

[Shloime]

Hi
Suggest you consider converting your ranges to Table (ListObjects) which enables you to reference Table Columns By Name

Dave

Ok that's an option. But I have work that I don't use tables I can't tell now why

 

[Akuini]

They automatically adjust if columns are added, removed, or rearranged.

Try assigning the header to a named range and refer to the named range wherever you need.

 

[dmt32]

Ok that's an option. But I have work that I don't use tables

That's a shame - make life so much easier

If you just want to get the column number for any worksheet heading then try this update to your approach & see if will work for you

Place in STANDARD module

Function GetColumn(ByVal HeaderName As String, Optional ByVal sh As Object, Optional ByVal HeaderRow As Long = 1) As Long
Dim HeaderArr As Variant, m As Variant
If sh Is Nothing Then Set sh = ActiveSheet
HeaderArr = sh.Rows(HeaderRow).Value2
m = Application.Match(HeaderName, HeaderArr, 0)
GetColumn = IIf(IsError(m), 1, CLng(m))
End Function

Function has three parameters

- HeaderName (string) = the name of the header
- sh (object) - optional - (default is ActiveSheet) = the worksheet object
- HeaderRow (Long) - optional - (default is Row 1) = the row of your headers

to use

Sub TestIt()
Dim NMcolID As Long
NMcolID = GetColumn("ID", S1, 10)

MsgBox NMcolID
 End Sub

Hopefully, this will simply things for you

Dave

 

[Shloime]

That's a shame - make life so much easier

If you just want to get the column number for any worksheet heading then try this update to your approach & see if will work for you

Place in STANDARD module

Function GetColumn(ByVal HeaderName As String, Optional ByVal sh As Object, Optional ByVal HeaderRow As Long = 1) As Long
Dim HeaderArr As Variant, m As Variant
If sh Is Nothing Then Set sh = ActiveSheet
HeaderArr = sh.Rows(HeaderRow).Value2
m = Application.Match(HeaderName, HeaderArr, 0)
GetColumn = IIf(IsError(m), 1, CLng(m))
End Function

Function has three parameters

- HeaderName (string) = the name of the header
- sh (object) - optional - (default is ActiveSheet) = the worksheet object
- HeaderRow (Long) - optional - (default is Row 1) = the row of your headers

to use

Sub TestIt()
Dim NMcolID As Long
NMcolID = GetColumn("ID", S1, 10)

MsgBox NMcolID
 End Sub

Hopefully, this will simply things for you

Dave

if I use this in every sub and I later change the "ID" to say "Client ID" I would need to change it everywhere and the same issue would actually be with tables, What I wrote in the last method I would have this benefit having all header names in 1 place And I'd only need to change there

 

[Akuini]

@Shloime
Did you try my suggestion at post #4?

 

[Shloime]

@Shloime
Did you try my suggestion at post #4?

I didn't try now but I found that dynamic named ranges slows down extremely. I could set the named range up to the last row.

 

[Akuini]

I found that dynamic named ranges slows down extremely.

I wasn't suggesting a dynamic range.
Say, at the moment the header you're talking about is in D2. Create a named range, say, myRange that refers to D2. Its column would be 4:

Debug.Print Range("myRange").Column 'returns 4

Now, try selecting column A and insert a column. Its column is now 5.

Debug.Print Range("myRange").Column 'now returns 5

Also, if you change its value, say from "Names" to "First Name", this won't affect the cell it refers to.
And you can assign Range("myRange").Column to a procedure level variable, for example:

Sub test()
Dim xCol as Long
xCol = Range("myRange").Column
'.....

 

[dmt32]

What I wrote in the last method I would have this benefit having all header names in 1 place And I'd only need to change there

OK clearly not fully understanding your post & what you are trying to do.

Providing the column code names you have shown (e.g “NMcolID”) are consistent throughout your project, then an approach you could consider would be to use these in a Table (Listobjects) which where you can update your Column Names - this will negate the need to hard code any changes. However, if the function is to be used across different sheets you will still need to pass to it which sheet & header row your code is checking against

See if this helps

Add following table to new worksheet & name the sheet Column Names

14-11-2024.xls
	A	B
1	Column Code	Column Name
2	NMcolID	ID
3	NMcolSN	Surname
4	NMcolHO	Holiday
Column Names

• Table is dynamic you can add to your column codes & their column names as required
• Place updated code in standard module

Function GetColumn(ByVal ColumnCode As String, Optional ByVal sh As Object, Optional ByVal HeaderRow As Long = 1) As Long
    Dim tblColumnNames      As ListObject
    Dim ColumnName          As String
    Dim ColumnNamesArr      As Variant, HeaderRowArr As Variant, m As Variant
   
    If sh Is Nothing Then Set sh = ActiveSheet
    HeaderRowArr = sh.Rows(HeaderRow).Value2
   
    On Error GoTo myerror
    Set tblColumnNames = ThisWorkbook.Worksheets("Column Names").ListObjects(1)
    ColumnNamesArr = tblColumnNames.DataBodyRange.Value2
    m = Application.Match(ColumnCode, Application.Index(ColumnNamesArr, 0, 1), 0)
    If IsError(m) Then Err.Raise 744, , "Column Code Not Found"
       
    ColumnName = ColumnNamesArr(m, 2)
   
    m = Application.Match(ColumnName, HeaderRowArr, 0)
    If IsError(m) Then Err.Raise 744, , "Column Name Not Found"
   
myerror:
    GetColumn = IIf(IsError(m), 1, CLng(m))
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Function

Updated function still has three parameters which you will need to pass

• Column Code – this is the variable name you use in each of your subs (e.g. “NMcolID”)
• sh – the worksheet object (default is the activesheet)
• HeaderRow - (default is Row 1)

Your subs will need to be modified like this

Dim NMcolID As Long
NMcolID = GetColumn("NMcolID", S1, 10)

If you omit the last two arguments then code will apply to Activesheet Row 1

I have only lighly tested but hopefully, this approach will achieve what you were looking for.

Dave