Sunday, July 20, 2008

Add a new column to Excel Table using VBA

Add Columns to Excel List Object using VBA / Add a new column to Excel Table using VBA

As discussed in previous blog posts, one of the ‘sleek’ advantage that a listobject gives us is to treat it as a datasource/ a data table. Here we can experience the same in adding a new column, which gets data from existing columns

Sub Add_ListColumn_2_ExistingTable()

Dim oWS As Worksheet ' Worksheet Object

Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

Dim oLst As ListObject ' List Object

Dim oLC As ListColumn ' List Column Object

On Error GoTo Disp_Error

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

oWS = ActiveSheet

If oWS.ListObjects.Count = 0 Then Exit Sub

oLst = oWS.ListObjects(1)

oLC = oLst.ListColumns.Add

oLC.Name = "Total Price"

oLC.DataBodyRange = "=[Price]*[Availability]"

If Not oLC Is Nothing Then oLC = Nothing

If Not oLst Is Nothing Then oLst = Nothing

If Not oWS Is Nothing Then oWS = Nothing

' --------------------

' Error Handling

' --------------------

Disp_Error:

If Err <> 0 Then

MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")

Resume Next

End If

End Sub



Excel List before Column Addition
Excel List after Column Additon (Note the column values are generated automatically)
The new column ‘Total Price’ is added to the existing Table and all the rows are calculated without any hassles

Refer (http://vbadud.blogspot.com/2008/07/convert-range-to-listobject-using-excel.html) for creating ListObjects in Excel

4 comments:

  1. rajesh, 365media4:11 AM

    i have tried the code what u have provided. It says compiler error .. like "Invalid use of property" in

    oLst = oWS.ListObjects(1)

    ...

    ReplyDelete
  2. Rajesh

    Can you try the following code

    Sub Add_ListColumn_2_ExistingTable()

    Dim oWS As Worksheet ' Worksheet Object

    Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

    Dim oLst As ListObject ' List Object

    Dim oLC As ListColumn ' List Column Object

    On Error GoTo Disp_Error

    ' ---------------------------------------------

    ' Coded by Shasur for www.vbadud.blogspot.com

    ' ---------------------------------------------

    oWS = ActiveSheet

    If oWS.ListObjects.Count = 0 Then Exit Sub

    Set oLst = oWS.ListObjects(1)

    Set oLC = oLst.ListColumns.Add

    oLC.Name = "Total Price"

    oLC.DataBodyRange = "=[Price]*[Availability]"

    If Not oLC Is Nothing Then Set oLC = Nothing

    If Not oLst Is Nothing Then Set oLst = Nothing

    If Not oWS Is Nothing Then Set oWS = Nothing

    ' --------------------

    ' Error Handling

    ' --------------------

    Disp_Error:

    If Err <> 0 Then

    MsgBox Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples"

    Resume Next

    End If

    End Sub

    ReplyDelete
  3. Anonymous4:25 AM

    HI...THIS IS VAISHALI...

    CAN IT BE DONE WHERE DATA TO BE FILLED COMES FROM DATABASE??

    ReplyDelete
  4. Anonymous5:19 AM

    can anyone help me how to add additional column between two columns in excel with out changing VBA code and to modify itself automatically in VBA code excel

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group