Implement Structured Reference in VBA Function

Question

I am using this VBA function, and right now it works fine as the format of my spreadsheet has not changed. But I will be adapting it for another use, and the order is likely going to be changing a bit more often. The table header names that I need for this function will not change their names though. My whole spreadsheet equations already uses structured references, but I am struggling to wrap my head around how exactly I eliminate the remnants of the explicit references to the column position in the VBA code.

The row that states If data(i, 2).Value = expNum Then and

If data(i, 14).Value <> Empty Then 
    curEnergy = data(i, 14).Value`

Are the ones in question. Column 2 corresponds to a column named "Exp #", and Column 14 corresponds to a column name "G (kcal/mol)" I've attached the full function below, just in case there are critical pieces in other sections that I'm not aware of. I would like to replace those references to 2 and 14 with the structured reference or something robust to withstand reordering of column positions.

Function BoltzmannEnergy(expNum As String) As Double
Application.Volatile

Worksheets("Raw Values").Activate
Dim data As Range, curCell As Range
Dim numRows As Integer, arrayCount As Integer, arraySize As Integer
Dim energies() As Double, curEnergy As Double, minEnergy As Double, RT As Double, BoltzTop As Double, BoltzBtm As Double
Dim expFound As Boolean
Const T As Double = 298
Const R As Double = 0.001985

RT = R * T
BoltzTop = 0
BoltzBtm = 0
expFound = False

arraySize = 5
minEnergy = 0
ReDim energies(0 To arraySize)

Set data = Range("RawValues")
arrayCount = 0
numRows = data.Rows.Count

For i = 1 To numRows
    If data(i, 2).Value = expNum Then
        If arrayCount = UBound(energies) - 1 Then
            ReDim Preserve energies(0 To arrayCount + arraySize + 1)
        End If

        expFound = True
        If data(i, 14).Value <> Empty Then
            curEnergy = data(i, 14).Value
            If curEnergy <> 0 Then
                If curEnergy < minEnergy Then
                    minEnergy = curEnergy
                End If
                energies(arrayCount) = curEnergy
                arrayCount = arrayCount + 1
            End If
        End If
    ElseIf expFound = True Then
        Exit For
    End If
Next i

For i = 0 To arrayCount - 1
BoltzTop = BoltzTop + energies(i) * Exp(-(energies(i) - minEnergy) / RT)
BoltzBtm = BoltzBtm + Exp(-(energies(i) - minEnergy) / RT)
Next i

BoltzmannEnergy = BoltzTop / BoltzBtm

End Function

Does the named range you're reading include the column headers? — Tim Williams
– Tim Williams, Commented Jul 19, 2013 at 18:02
No, it does not appear so, but I'm not sure calling it a named range is correct, it's not created in the same way. I don't select the table cells, then edit the name in the box in excel. Instead you give the table itself a name (in the table design tab), and use the structured references for that table name. — J M
– J M, Commented Jul 19, 2013 at 19:52
Even though it's created and maintained differently, it's still a Named range: check under Formulas>>Name Manager and you'll see it there. — Tim Williams
– Tim Williams, Commented Jul 19, 2013 at 19:58
Right I understand that, and it is there, but it does not appear to include the headers due to the way it is created and used. If I type ="RawValues" into a cell it selects only the data, not the headers as well. — J M
– J M, Commented Jul 19, 2013 at 20:00
Range("RawValues").rows(1).offset(-1,0).address() will give you the range with the headers: you can use Derek's approach to find the relevant column positions. — Tim Williams
– Tim Williams, Commented Jul 19, 2013 at 20:02

Derek Cheng · Accepted Answer · 2013-07-19 18:03:40Z

2

If you know that header names will not change you can replace those number with two variables each referring to a find range.columns.

Something like:

Dim intColumn1, intColumn2 As Integer
Dim rngTemp As Range
'assuming your header row is row 1
Set rngTemp = Worksheets("Raw Values").Rows(1).Find(what:="Header_1_value")
'check if you actually found a cell
If Not rngTemp Is Nothing Then
    intColumn1 = rngTemp.Column
End If

'assuming your header row is row 1
Set rngTemp = Worksheets("Raw Values").Rows(1).Find(what:="Header_2_value")
'check if you actually found a cell
If Not rngTemp Is Nothing Then
    intColumn2 = rngTemp.Column
End If

answered Jul 19, 2013 at 18:03

Derek Cheng

5253 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Implement Structured Reference in VBA Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related