Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » vba-listy-bunky » Tabulka-jako-tabulka-VBA-Excel
Microsoft Excel logo

Tabulka jako tabulka VBA Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak pracovat s tabulkou jako tabulka využitím VBA v Excel

Úvodem

V tomto článku se podíváme na práci s tabulkou jako tabulka využitím VBA. Pro přehlednost je článek rozdělen na kapitoly:


Vybírat oblasti SELECT

Předpokládám, že máte k dispozici tabulku jako tabulka pod názvem TabJakNaExcel. Aneb ať mi inspirátoři (kopírovači) dělají reklamu. ;)

Celá tabulka

ListObjects("TabJakNaExcel").Range.Select

Sloupec jen data v dané tabulce a daném sloupci:

ListObjects("TabJakNaExcel").ListColumns(4).DataBodyRange.Select

Sloupec jen data v dané tabulce a daném sloupci bez využití ListObjects

Range("TabJakNaExcel[Id]").Select

Celý sloupec 2

ListObjects("TabJakNaExcel").ListColumns(2).Range.Select ActiveSheet.ListObjects("TabJakNaExcel").ListColumns(2).Range.Select

Jedna buňka

ListObjects("TabJakNaExcel").DataBodyRange(3, 2).Select ListObjects("TabJakNaExcel").ListColumns(2).Range(2).Select ListObjects("TabJakNaExcel").ListRows(4).Range(1).Select

Řádek

ListObjects("TabJakNaExcel").ListRows(4).Range.Select

Celý součtový řádek

ListObjects("TabJakNaExcel").TotalsRowRange.Select

Čtvrtý sloupce v součtovém řádku

ListObjects("TabJakNaExcel").TotalsRowRange(4).Select

Označení celého záhlaví

ListObjects("TabJakNaExcel").HeaderRowRange.Select

Jedna položka záhlaví

ListObjects("TabJakNaExcel").HeaderRowRange(3).Select Tabulka jako tabulka - Microsoft Excel VBA

Vytvořit tabulku jako tabulka

Vytvoření tabulky je možné několik způsoby:

  • označím oblasti
  • označím buňku a využít CurrentRegion

Praktické ukázky

' 1 ListObjects.Add(xlSrcRange, Range("A1:D10"), , xlYes).Name = "TabulkaPavel" '2 ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A1:C4"), _ xlListObjectHasHeaders:=xlYes ).Name = "TabulkaJakNaExcel" '3 ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("A1").CurrentRegion, _ xlListObjectHasHeaders:=xlYes ).Name = "TabulkaJakNaExce_2" '4 ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Selection.CurrentRegion, _ xlListObjectHasHeaders:=xlYes ).Name = "TabulkaJakNaExcel_3"

Tipy

hlavička

xlListObjectHasHeaders:=xlYes xlListObjectHasHeaders:=xlNo

Předpokládám, že data v označené oblasti mají záhlaví, a jméno které se tabulce jako tabulka přiřazuje neexistuje.

Úpravy tabulky jako tabulka

Úprava tabulka

Rozsah

ListObjects("TabJakNaExcel_4").Resize Range("$F$3:$I$15")

Řádky / Sloupce

Přidání sloupce

ListObjects("TabJakNaExcel_4").ListColumns.Add ' na pozici ListObjects("TabJakNaExcel_4").ListColumns.Add Position:=2 ' rovnou přidat jméno přidavanému sloupci ListObjects("TabJakNaExcel_4").ListColumns.Add.Name = "aaa"

Přidání řádky

ListObjects("TabJakNaExcel_4").ListRows.Add ' řádek na určitou pozici ListObjects("TabJakNaExcel_4").ListRows.Add Position:=1

Smazání sloupce

ListObjects("TabJakNaExcel_4").ListColumns(2).Delete ' Info ' Maže se jen v tabulce !!

Smazání řádky

ListObjects("TabJakNaExcel_4").ListRows(2).Delete ListObjects("TabJakNaExcel_4").Range.Rows("4:6").Delete

Design tabuky

Grafika

Z před připravených schémat:

ListObjects("TabulkaJakNaExcel_5").TableStyle = "TableStyleMedium1"

Tip

  • TableStyleLight21
  • TableStyleDark10

Nastavení defaultního designu pro tvorbu tabulek

ActiveWorkbook.DefaultTableStyle = "TableStyleMedium5"

Úpravy sloupců - první

ListObjects("TabulkaJakNaExcel_5").ShowTableStyleFirstColumn = True ListObjects("TabulkaJakNaExcel_5").ShowTableStyleFirstColumn = False

Úpravy sloupců - poslední

ListObjects("TabulkaJakNaExcel_5").ShowTableStyleLastColumn = True ListObjects("TabulkaJakNaExcel_5").ShowTableStyleLastColumn = False

Úpravy sloupců - pruhování

ListObjects("TabulkaJakNaExcel_5").ShowTableStyleColumnStripes = True ListObjects("TabulkaJakNaExcel_5").ShowTableStyleColumnStripes = False

Úpravy řádky - pruhování

ListObjects("TabulkaJakNaExcel_5").ShowTableStyleRowStripes = True ListObjects("TabulkaJakNaExcel_5").ShowTableStyleRowStripes = False

Záhlaví tabulky - zobrazit / skrýt

ListObjects("TabulkaJakNaExcel_5").ShowHeaders = True ListObjects("TabulkaJakNaExcel_5").ShowHeaders = False

Záhlaví tabulky - filtrování

ListObjects("TabulkaJakNaExcel_5").ShowAutoFilterDropDown = False ListObjects("TabulkaJakNaExcel_5").ShowAutoFilterDropDown = True

Zobrazit součtový sloupec

ListObjects("TabulkaJakNaExcel_5").ShowTotals = True ListObjects("TabulkaJakNaExcel_5").ShowTotals = False

Řazení

Zrušit řazení

ListObjects("JakNaExcel_7").Sort.SortFields.Clear

Nastavení řazení podle konkretního sloupce:

ListObjects("JakNaExcel_7").Sort.SortFields.Add Key:=Range("JakNaExcel_7[id]"), SortOn:=xlSortOnValues, Order:=xlDescending

přehledněji

ListObjects("JakNaExcel_7").Sort.SortFields.Add _ Key:=Range("JakNaExcel_7[id]"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending

Tip

  • Order:=xlDescending
  • Order:=xlAscending

Včetně deklarace

Dim ws As Worksheet Set ws = ActiveSheet Dim tbl As ListObject Set tbl = ws.ListObjects("JakNaExcel_7") Dim sortcolumn As Range Set sortcolumn = Range("JakNaExcel_7[id]") With tbl.Sort .SortFields.Clear .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending .Header = xlYes .Apply End With

Tip

  • podle barev
  • podle ikonek

Filtrování

Zrušit filtr

ListObjects("JakNaExcel_9").Range.AutoFilter

Jedno kriterium

ListObjects("JakNaExcel_9").Range.AutoFilter Field:=1, Criteria1:="2"

Dvě kriteria

ListObjects("JakNaExcel_9").Range.AutoFilter Field:=1, _ Criteria1:="=2", Operator:=xlOr, Criteria2:="=5"

Více tipů na filtrování je v přípravě:

Výpočty

Výpočty - velikost tabulky jako tabulka

Msgbox ActiveSheet.ListObjects("T_JakNaExcel").ListRows.Count Msgbox ActiveSheet.ListObjects("T_JakNaExcel").ListColumns.Count

Připravit součty

ListObjects("T_JakNaExcel").ShowTotals = True

Nastavit - Průměr

ListObjects("T_JakNaExcel").ListColumns("Vek").TotalsCalculation = xlTotalsCalculationAverage

xl - konstanty

  • xlTotalsCalculationNone
  • xlTotalsCalculationAverage
  • xlTotalsCalculationCount
  • xlTotalsCalculationCountNums
  • xlTotalsCalculationMax
  • xlTotalsCalculationMin
  • xlTotalsCalculationSum
  • xlTotalsCalculationStdDev
  • xlTotalsCalculationVar

Práce s daty v tabulce

Přečíst data

Hodnota = ListObjects("TabJakNaExcel").DataBodyRange(2, 4).Value MsgBox (Hodnota)

Upravit data * Vložit data

ListObjects("TabJakNaExcel").DataBodyRange(2, 4).Value = "Nova hodnota" ListObjects("JakNaExce_42").ListRows(4).Range(1).Value = 88883 ListObjects("JakNaExce_42").ListRows(4).Range(1) = 83473

Přepsat data v tabulce

S využitím deklarace proměnných a SETováním

Dim ws As Worksheet Dim tbl As ListObject Set ws = ActiveSheet Set tbl = ws.ListObjects("JakNaExce_42") With tbl.ListRows(3) .Range(1) = "pokus 1" .Range(2) = "pokus 2" End With

Přidat řádek a doplnit data

S využitím deklarace proměnných a SETováním

Dim ws As Worksheet Dim tbl As ListObject Dim NovyRadek As ListRow Set ws = ActiveSheet Set tbl = ws.ListObjects("JakNaExce_42") Set NovyRadek = tbl.ListRows.Add With NovyRadek .Range(1) = "pokus 1" .Range(2) = "pokus 2" End With

Více úprav najednou

For Each Když chci například změnit design více tabulek jako tabulka:

' pro konkretní list Dim ws As Worksheet Dim tabulky As ListObject ' smyčka pro více tabulek For Each tabulky In ActiveSheet.ListObjects ' Požadované úpravy tabulky.TableStyle = "TableStyleMedium3" Next tabulky

Změnit design všech tabulek v sešitě

Dim ws As Worksheet Dim tabulka As ListObject For Each ws In ActiveWorkbook.Worksheets For Each tabulka In ws.ListObjects ' Požadované úpravy tabulka.TableStyle = "TableStyleMedium3" Next tbl Next ws ' více v přírpavě

Ostatní

Ostatní.

Ostatní

Existuje tabulka

Dim tbl As ListObject With Worksheets("Data") For Each tbl In .ListObjects If tbl.Name = "JakNaExcel" Then MsgBox ("Existuje") Exit For End If Next tbl End With

Přes funkci, inspirováno na stackoverflow:

'https://stackoverflow.com/questions/38097134/vba-excel-check-if-a-particular-table-exist-using-table-name Sub callTableExists() MsgBox tableExists("Table1", "Shapes") End Sub Function TableExists(tableName As String, sheetName As String) As Boolean Dim targetSheet As Worksheet Set targetSheet = Worksheets(sheetName) Dim tbl As ListObject With targetSheet For Each tbl In .ListObjects If tbl.Name = tableName Then TableExists = True Next tbl End With End Function

Doplním, v případě, že na něco zajímavého narazím.

Související články

Související články:


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor ke stažení zdarma je v přípravě.


Závěrem

Máte zajímavý tip na práci s práci s tabulkou jako tabulka využitím VBA kódu v Excel. Můžete využít komentáře.

Článek byl aktualizován: 11.05.2019 06:33

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 739

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.