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 in Others by Kithuzzz
• 20,660 points
34 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 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Language independent way to get "My Documents" folder in VBA Excel 2003

 Hello :)  This code may help you in your ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
87 views
0 votes
1 answer

Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
89 views
0 votes
1 answer
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
104 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,720 points
452 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
2,220 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 in Others by gaurav
• 22,040 points
69 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 in Others by Edureka
• 13,640 points
207 views
0 votes
1 answer
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 in Others by narikkadan
• 37,660 points
67 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