Excel VBA Worksheet Range storage location best practices

0 votes
Defining the placement of items in a sheet within a VBA project is one of the problems for which I've never found a great answer. If you've ever had to construct a worksheet using VBA and worksheet functions, you understand why this matters. Running a script only to find out that you had to add two columns of data and missed some range references in your VBA that needed to be updated caused the cell that contained the result of your f-test to move from F20 to H20.

In order to systematically store VBA range references that are simple to update and stay up with the changing spreadsheet, I'm seeking additional suggestions and tried-and-true best practices.

Can someone please help me with this?
Oct 20, 2022 in Others by Kithuzzz
• 38,020 points
399 views

1 answer to this question.

0 votes

Solution

Changing Worksheets' Code Name

Why dim srcSheet as myWorksheet when you can just change the Worksheet's Code Names?

Changing Worksheets Code Names

Enumerate the Columns and Header Rows

For each worksheet, I generate an Enum to enumerate the [First Column], [Last Column], and [Header Row] columns. Enum members can be made hidden by enclosing them in brackets and giving them names with special characters. Here is some sample code. Update the enum, and the code won't fail if I later decide to rearrange the columns, add columns, or move the data.

Right-clicking the Object Browser gives you the option to show hidden members of Object and Enums. show-hidden-members

Enums are available to Intellisense

Intellisense

Demo Code

Public Enum EnumWSDataColumns
    dcOrderDate = 3
    dcRegion
    dcRep
    dcItem
    dcUnits
    dcUnitCost
    dcTotal
    [_dcFirstColumn] = dcOrderDate
    [_dcLastColumn] = dcTotal
    dcHeaderRow = 4
End Enum

Sub DemoEnum()
    Dim r As Long, c As Long
    Dim value As String * 10
    With wsData
        For r = dcHeaderRow To .Cells(.Rows.count, [_dcFirstColumn]).End(xlUp).Row
            For c = [_dcFirstColumn] To [_dcLastColumn]
                value = .Cells(r, c)
                Debug.Print value; "|";
            Next
            Dim n As Long
            n = ([_dcLastColumn] - [_dcFirstColumn] + 1) * (Len(value) + 1)
            Debug.Print
            Debug.Print String(n, "-")
        Next
    End With
End Sub

Output

Output

answered Oct 21, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

VBA Excel Adding pictures to a worksheet

It should work as: ActiveSheet.Pictures.Insert("C:\Work\test_Project\myjpgfile.jpg").Select As that's all the ...READ MORE

answered Jan 3, 2023 in Others by narikkadan
• 63,720 points
412 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,720 points
2,607 views
0 votes
1 answer
0 votes
1 answer

Delete all rows in filtered range Except first filtered row in excel VBA

Use a flag to omit first row Sub ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
2,097 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,079 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,495 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
803 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
976 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
3,537 views
0 votes
1 answer

Excel VBA remove blank rows from specific range

I have tried to avoid .select  Option Explicit Sub CombineData() ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,720 points
1,041 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP