Working with Microsoft Excel tables in VBAThis blog post will be based on working with tables via VBA.They present some unique issues, which has to do with how their structured in the object model.Good or bad, its what we have.These are based on my trial and errors, so hopefully this will help somebody out there.That being said I consider this an intermediate level post.If youre not up to speed with tables, take a look at these http excelandaccess.When looking for information about tables, or addingdeleting data, it can be tricky.Here are some pieces of information to keep in mind, which isnt very obvious, but are cornerstones in working with tables in VBA Deleting the Data.Body.Range data portion of a table will set it to Nothing If table parts i.Accessing the straight Range of a table includes all visible parts i.Tables are List.Objects f youve worked with tables in VBA, you may have noticed that when deleting a tables entire data body, you are still left with a single, blank row.This is because you must have at least one row of data for the table to still be alive.No data rows, no table.You can hide the header and totals row, but you cant get rid of the body.This presents some interesting coding when working with tables, and can be problematic, aka headache inducing.Check the links at the bottom of the post for additional information on these topics.Here are some VBA examples for doing various things with tables in VBA.The file will be available link at bottom of post for download.Microsoft Access Query Tips and Techniques SQL and VBA by Luke Chung, President of FMS, Inc.This paper is featured on Overview.Microsoft Access is the most.MS Paint, the first app you used for editing images, will probably be killed off in future updates of Windows 10, replaced by the new app Paint 3D.Microsoft lists.Common variables used throughout.Public WS As Worksheet.Public lo.Set As List.Object.Public lo.Bji5wnkAPc/hqdefault.jpg' alt='Age Calculation In Microsoft Access' title='Age Calculation In Microsoft Access' />Get As List.Object.Public lo.Test As List.Object.Public r. Cols As Range.Public r.Rows As Range.Public r.Body As Range.Public r.Data As Range.Public r.Header As Range.Public r.Start As Range.Public i.Col As Long.Public i.Row As Long.Public i.Step As Long.Public i.Last. Row As Long.Public i.Row. Cnt As Long.Public i.Col. Cnt As Long.Public s.Msg As String.Public Const s.Sheet.Name As String Sheet.Public Const s.Table.Name As String Table.Sonoma County Fair Sheep Dog Trials In California on this page.Public Const s. Table.Name.As String Table.Public Const NL As String vb.New.Line. Public Const DNL As String vb.New.Line vb. New.Linesourcecode.As you can see I used some constants for testing in this specific workbook.Change these variables for testing in your environment.I tried to keep the naming convention simple.Getting information from your tables.When working with tables via VBA its important to know how to look at them, and thus where to go for information.These are some examples of how to get dimensions information from your tables.Listing columns.This will step through a table and list the column headers in a message box, as well as the Immediate window in the Visual Basic Editor Ctrl G to show it.Sub TableList.ColumnsOn Error Resume Next.Set WS This.Workbook.Worksheetss.Sheet.NameSet lo.Test WS.List. Objectss.Table.NameOn Error Go.To 0.If WS Is Nothing Or lo.Test Is Nothing Then.Msg.Box There was an error setting the variables., vb.Information, ERRORExit Sub.End Ifs.Msg vb. Null.String.For i. Step 1 To lo.Test.List. Columns.Count.Debug. Print lo.Test.List. Columns.Itemi.Step. Names.Msg s.Msg NL i. Step lo.Test.List. Columnsi.StepNext i.Steps.Msg lo. Test.Name column headers NL s.Msg. Rus Windows Mobile 6 Professional Images Pregnancy . Msg. Box s. Msg, vb.OKOnly, COLUMN HEADERSEnd Subsourcecode.Column count.This will not loop through the table, but give a straight count of columns, along with whichwhere the startend columns are.Sub TableCount.ColumnsOn Error Resume Next.Set WS This.Workbook.Worksheetss.Sheet.NameSet lo.Test WS.List. Objectss.Table.NameOn Error Go.To 0.If WS Is Nothing Or lo.Test Is Nothing Then.Msg.Box There was an error setting the variables., vb.Information, ERRORExit Sub.End Ifs.Msg There are lo.Test.List. Columns.Count columns in s.Table.Name. s. Msg s.Msg DNL Starting on column lo.Test.List. Columns1.Range.Column lo.Test. List.Columns1 s.Msg s.Msg NL Ending on column lo.Test.List. Columnslo.Test.List. Columns.Count.Range. Column lo.Test.List. Columnslo.Test.List. Columns.Count Msg.Box s.Msg, vb. Information, UCases.Table.NameEnd Subsourcecode.Row count.Again, no loop, just find out how many rows are in the table and where they are.Sub TableCount.RowsOn Error Resume Next.Set WS This.Workbook.Worksheetss.Sheet.NameSet lo.Test WS.List. Objectss.Table.NameOn Error Go.To 0.If WS Is Nothing Or lo.Test Is Nothing Then.Msg.Box There was an error setting the variables., vb.Information, ERRORExit Sub.End Ifs.Msg There are lo.Test.List. Rows.Count rows in s.Table.Name. s. Msg s.Msg DNL Starting on row lo.Test.List. Rows1.Range.Rows. Msg s.Msg NL Ending on row lo.Test.List. Rowslo.Test.List. Rows.Count.Range. Row.Msg. Box s.Msg, vb.Information, UCases.Table.NameEnd Subsourcecode.Resizing Tables.Resize table based on original.This will basically clear out the manual data and leave the formulas.It gets a row count and uses that to insert rows to its original size.Please note these focus on resizing rows programmatically, not columns, which can be trickier.Sub Resize.Table.Rows Set misc variables.Set WS This.Workbook.Worksheetss.Sheet.NameSet lo.Set WS.List. Objectss.Table.Name Ensure the table has a data range.If lo.Set. Data.Body. Range Is Nothing Then.Msg.Box The source table has no data stucture., vb.Exclamation, ERRORExit Sub.End If Get the number of rows in the tablei.Row.Cnt lo. Set.List.Rows. Count Delete destination table data.If Not lo.Set. Data.Body. Range Is Nothing Thenlo.Set.Data. Body. Range.Delete.End If Resize to source table size.For i.Step 1 To i.Row. Cntlo.Set. List.Rows. Add i.Step.Next i. Step At this point the table is restructured, and the formulas which were there are re populatedEnd Subsourcecode.Resize table based on another tables size in rowsThis utilizes two tables.The second table is only used for its row size and makes the first table the same size.It deletes the Data.Body.Range as well, so all manual data will be erased, leaving only formulas.Sub Resize.Table.Rows. Based.On. Another Set misc variables.Set WS This.Workbook.Worksheetss.Sheet.Name Set two tables, one to set destination and one to resize from sourceSet lo.Set WS.List. Objectss.Table.NameSet lo.Get WS.List. Objectss.Table.Name. 2 Delete destination table data.If Not lo.Set. Data.Body. Range Is Nothing Thenlo.Set.Data. Body. Range.Delete.End If Ensure source table has data range.If lo.Get. Data.Body. Range Is Nothing Then.Msg.Box The source table has no data stucture., vb.Exclamation, ERRORExit Sub.End If Resize to source table size.For i.Step 1 To lo.Get.List. Rows. Countlo.Set.List. Rows. Add i.Step.Next i. Step At this point the table is restructured, and the formulas which were there are re populatedEnd Subsourcecode.Resize table columns.This example will add a single column.While I am very hesitant to code something like this, here is an example.While its easier to add rows, adding columns present some unique problems, which include Where should the columns be inserted Without proper absolute structured formula referencing, this could throw off formulas.Ideally no data should be to the right of the table, so an error wont be thrown.Sub Resize.Table.Cols Set misc variables.Set WS This.Workbook.Worksheetss.Sheet.NameSet lo.Set WS.List. Objectss.Table.Name Ensure the table has a data range.If lo.Set. Data.Body. Range Is Nothing Then.Msg.Box The source table has no data stucture., vb.Exclamation, ERRORExit Sub.End If Get the number of columns in the table, add onei.Col.Cnt lo. Set.Star Documentshome.Keystone STARS.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |