CODEWITHSUNDEEP
vbaexcelexcel-2010
OutThere
OutThere

Reputation: 475

Shorter way to refer to cells multiple times in Excel VBA

I use the following code block to set some initial values. I've found my self using this construction a lot and I'm wondering if there's something more concise?

Sheets(AppTab).Select
Cells(StartingRow, Range("AppTransEffDate").Column).Value = FirstPymtDueDate
Cells(StartingRow, Range("AppTransAmt").Column).Value = RegularPymt
Cells(StartingRow, Range("AppActionCode").Column).Value = SchPymtDueActionCode
Cells(StartingRow, Range("AppDescr").Column).Value = TransDescr

If it matters, each of these ranges is one column wide.

Upvotes: 0

Views: 316

Answers (3)

user4039065
user4039065

Reputation:

Use one or more With ... End With statements.

with Sheets(AppTab)
    .Cells(StartingRow, .Range("AppTransEffDate").Column) = FirstPymtDueDate
    .Cells(StartingRow, .Range("AppTransAmt").Column) = RegularPymt
    .Cells(StartingRow, .Range("AppActionCode").Column) = SchPymtDueActionCode
    .Cells(StartingRow, .Range("AppDescr").Column) = TransDescr
end with
'alternate
with Sheets(AppTab)
    with .rows(StartingRow)
        .Cells(1, .Range("AppTransEffDate").Column) = FirstPymtDueDate
        .Cells(1, .Range("AppTransAmt").Column) = RegularPymt
        .Cells(1, .Range("AppActionCode").Column) = SchPymtDueActionCode
        .Cells(1, .Range("AppDescr").Column) = TransDescr
    end with
end with

I don't think the second With .... End With will show as much improvement as the first in this case but isolating the cells you are referencing is more efficient and avoids repeated calls to locate parentage.

Always referencing the parent worksheet explicitly is widely considered 'best practice' and avoid using .Select and .Activate altogether.

Upvotes: 3

user6432984
user6432984

Reputation:

I would use an enumeration to reference the column numbers. Enumerations go at the top of a code module. The next enumeration in the list is increment 1 from the previous. The big advantage of enumerating columns is when you need to reorder your columns; you only have to update your enumeration and your code will run as before. The enumerations also work with intellisense.

enter image description here

Option Explicit

Public Enum Cols
    cnAppTransEffDate = 2
    cnAppTransAmt = 4
    cnAppActionCode = 5
    cnAppDescr
End Enum


Sub FillData()

    ' Some Code

    With Worksheets(AppTab)
        .Cells(StartingRow, cnAppTransEffDate).Value = FirstPymtDueDate
        .Cells(StartingRow, cnAppTransAmt).Value = RegularPymt
        .Cells(StartingRow, cnAppActionCode).Value = SchPymtDueActionCode
        .Cells(StartingRow, cnAppDescr).Value = TransDescr
    End With
End Sub

Upvotes: 4

Slai
Slai

Reputation: 22876

My usual way is to set the first cell of the row range and then to use the Range._Default property

Set r = Sheets(AppTab).Range("A" & StartingRow)
r(, Range("AppTransEffDate").Column) = FirstPymtDueDate

but instead of named ranges, I would use Enum like in Thomas Inzina's answer.

If your named ranges start from row 1, then this might work

Range("AppTransEffDate")(StartingRow) = FirstPymtDueDate

or

Set areas = Sheets(AppTab).Range("AppTransEffDate,AppTransAmt,AppActionCode,AppDescr").Areas

areas(1)(StartingRow) = FirstPymtDueDate
areas(2)(StartingRow) = RegularPymt
areas(3)(StartingRow) = SchPymtDueActionCode
areas(4)(StartingRow) = TransDescr

or maybe Intersect might work too:

Set areas = Sheets(AppTab).Range("(AppTransEffDate,AppTransAmt,AppActionCode,AppDescr) " & StartingRow & ":" & StartingRow).Areas

areas(1) = FirstPymtDueDate
areas(2) = RegularPymt
areas(3) = SchPymtDueActionCode
areas(4) = TransDescr

Upvotes: 0

Related Questions