Add a new column in excel without changing the VB code

Question

I create a VB program to automatically update a Gantt chart for a group project. But now the team wants to add a new colum. The problem is that adding a new column will change my code and make it unusable. Rows can be added without changing the code, but I would have to update all my code if new colums are added. How can I add a column without chaning my VB code?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim StartDate_Row As Integer
Dim Total_Weeks As Integer
Dim Date_Week_Column As Integer
Dim Number_of_Weeks As Integer
Dim Date_Week_Column_Color As Integer


StartDate_Row = 10
Date_Week_Column = 8

Range("H9:AN25").Interior.Color = xlNone

Do

For Total_Weeks = 1 To 33

   If Cells(StartDate_Row, 5).Value = Cells(8, Date_Week_Column).Value Then

    Date_Week_Column_Color = Date_Week_Column

        For Number_of_Weeks = 1 To Cells(StartDate_Row, 6).Value
            If Cells(StartDate_Row, 7).Value = 25 Then
                Cells(StartDate_Row, Date_Week_Column_Color).Interior.Color = RGB(204, 255, 299)
            End If
            If Cells(StartDate_Row, 7).Value = 50 Then
                Cells(StartDate_Row, Date_Week_Column_Color).Interior.Color = RGB(153, 255, 204)
            End If
            If Cells(StartDate_Row, 7).Value = 75 Then
                Cells(StartDate_Row, Date_Week_Column_Color).Interior.Color = RGB(102, 255, 178)
            End If
            If Cells(StartDate_Row, 7).Value = 100 Then
                Cells(StartDate_Row, Date_Week_Column_Color).Interior.Color = RGB(50, 200, 100)
            End If
            If Cells(StartDate_Row, 7).Value = 0 Then
                Cells(StartDate_Row, Date_Week_Column_Color).Interior.Color = RGB(149, 179, 215)
            End If


            Date_Week_Column_Color = Date_Week_Column_Color + 1
        Next Number_of_Weeks

    End If

    Date_Week_Column = Date_Week_Column + 1

Next Total_Weeks
Date_Week_Column = 8



StartDate_Row = StartDate_Row + 1
Loop While (Not IsEmpty(Cells(StartDate_Row, 5)))



End Sub

It may actually all come down to just adjusting the Date_Week_Column = 8 to match the right column after inserting some extra columns. Also If Cells(StartDate_Row, 7) the 7th column is hardcoded currently - it may need to be adjusted as well. — user2140173
– user2140173, Commented Oct 2, 2014 at 10:32
could you give screenshot of your database in excel. than it would be easier to recreate the scenario — ZAT
– ZAT, Commented Oct 2, 2014 at 10:36

Tony Dallimore · Accepted Answer · 2014-10-02 19:15:02Z

Tom's suggestion is a possibility but is a lot of bother for the user for every run of your macro.

Possible technique 1

I never refer to columns or rows by numbers for two reasons:

Columns and rows may move as you have discovered.
Someone reading your code must know what column 5 or row 6 means.

It is better to use constants. Fo example:

Const ColXxxx As Long = 5
Const RowYyyy As Long = 8

If Cells(StartDate_Row, ColXxxx).Value = Cells(RowYyyy, Date_Week_Column).Value Then

I do not know what your rows and columns are so I have used ColXxxx and RowYyyy as names. You would replace my names with names that tells the reader what the row and column are.

Code like this takes a little longer to write but (1) it is self documenting and (2) if a column or row moves, you only need to change the Const statement to fix the problem.

Note: I have used data type Long. Data type Integer defines a 16-bit variable which requires special (slow) processing on 32-bit and 64-bit computers.

Possible technique 2

Technique 1 requires the user to tell the programmer that they want to add a column or move a row. If they forget to tell the programmer before runnibg the macro with an amended worksheet, the macro may damage the worksheet beyond repair.

Another technique is to search row 1 for the know column headings and record where there are. Pehaps you have a column heading "Start Date". "Start Date" can be in column 5 for one run of the macro and in column 6 for the next and you code will work just as it should.

If this technique is interesting, I will add example code.

Tom · Accepted Answer · 2014-10-03 08:14:35Z

The answer in short is that you must change your code. This becomes a bigger and bigger issue the larger and more complicated your code becomes. Therefore where possible try to think dynamically about the methods you use to specify cell locations.

E.g. You could build into your script a user input which allows the user to specify the column which contains the required information. That way if the user adds more columns to the table, the script will still run correctly (provided the user selects the correct column).

'ask user to select the column containing the data which they would like to utilise

Dim colRng As Range

On Error Resume Next
    Set colRng = Application.InputBox("Select a cell from anywhere in the column which contains the programme start dates.", Default:="", Type:=8)
On Error GoTo 0

If colRng Is Nothing Then
    'notify user that the process cannot continue as selection was invalid
    MsgBox "You must select a cell to enable this process to continue!"
    'exit the sub
    Exit Sub
End If

'output the column number (as selected by the user)
debug.print colRng.column

Hope that helps!

nekomatic · Accepted Answer · 2014-10-03 12:18:39Z

0

If you define a named range on your worksheet (using Formulas > Define Name), that name will be updated to point to the same cell(s) even if rows and columns are inserted or deleted.

In your code you can use (for example) MySheet.[MyRangeName].Row to get the row number of the first row of the range named MyRangeName, and so on.

answered Oct 3, 2014 at 12:18

nekomatic

6,2841 gold badge23 silver badges27 bronze badges

Collectives™ on Stack Overflow

Add a new column in excel without changing the VB code

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related