Tämä artikkeli osoittaa, kuinka luoda dynaaminen alue Excel VBA: ssa.
Tietyn solualueen ilmoittaminen muuttujaksi Excel VBA: ssa rajoittaa työskentelyn vain kyseisten solujen kanssa. Ilmoittamalla dynaamiset alueet Excelissä saamme paljon enemmän joustavuutta koodimme ja sen suorittamien toimintojen suhteen.
Viittausalueet ja solut
Kun viittaamme alue- tai soluobjektiin Excelissä, viittaamme niihin yleensä vaaditulla rivillä ja sarakkeissa kovakoodauksella.
Alueominaisuus
Käyttämällä Alue -ominaisuutta alla olevissa esimerkkiriveissä voimme suorittaa tällä alueella toimintoja, kuten muuttaa solujen väriä tai tehdä soluista lihavoituja.
| 12 | Alue ("A1: A5"). Font.Color = vbRedAlue ("A1: A5"). Font.Bold = Tosi |
Solujen omaisuus
Vastaavasti voimme käyttää Solut -ominaisuutta viittaamaan solualueeseen viittaamalla suoraan soluominaisuuden riviin ja sarakkeeseen. Rivin on aina oltava numero, mutta sarake voi olla numero tai lainausmerkeissä oleva kirjain.
Esimerkiksi solun osoitteeseen A1 voidaan viitata seuraavasti:
| 1 | Solut (1,1) |
Tai
| 1 | Solut (1, "A") |
Jotta voimme käyttää soluominaisuutta viittaamaan solualueeseen, meidän on ilmoitettava alueen alku ja alueen loppu.
Esimerkiksi viitealueelle A1: A6 voimme käyttää tätä syntaksia alla:
| 1 | Alue (Solut (1,1), Solut (1,6) |
Voimme sitten käyttää Solut -ominaisuutta suorittaaksesi toimintoja alueella alla olevien esimerkkirivien mukaisesti:
| 12 | Alue (Solut (2, 2), Solut (6, 2)). Fontti Väri = vbPunainenAlue (Solut (2, 2), Solut (6, 2)). Fontti Lihavoitu = Tosi |
Dynaaminen alue muuttujilla
Tietojemme koon muuttuessa Excelissä (eli käytämme enemmän rivejä ja sarakkeita kuin koodatut alueet), olisi hyödyllistä, jos myös koodissamme viittaavat alueet muuttuisivat. Käyttämällä yllä olevaa Range -objektia voimme luoda muuttujia tallentaaksemme käyttämämme Excel -laskentataulukon alueen enimmäisrivi- ja sarakenumerot ja säätää näiden muuttujien avulla Range -objektia dynaamisesti koodin ollessa käynnissä.
Esimerkiksi
| 1234 | Dim lRow kokonaislukunaDim lCol kokonaislukunalRivi = Alue ("A1048576"). Loppu (xlUp) .RivilCol = Alue ("XFD1"). Loppu (xlToLeft). Sarake |
Viimeinen rivi sarakkeessa
Koska laskentataulukossa on 1048576 riviä, muuttuja lRow siirtyy taulukon alareunaan ja käyttää lopetusnäppäimen ja ylänuolinäppäimen erikoisyhdistelmää siirtyäksesi laskentataulukon viimeiselle riville - tämä antaa meille sen rivin numero, jota tarvitsemme alueellamme.
Rivin viimeinen sarake
Vastaavasti lCol siirtyy sarakkeeseen XFD, joka on laskentataulukon viimeinen sarake, ja käyttää lopetusnäppäimen ja vasen nuolinäppäimen erityistä näppäinyhdistelmää siirtyäksesi laskentataulukon viimeiseen sarakkeeseen - tämä antaa meille sarakkeen numero, jota tarvitsemme valikoimastamme.
Siksi saadaksemme koko laskentataulukossa käytetyn alueen voimme suorittaa seuraavan koodin:
| 1234567891011 | Sub GetRange ()Dim lRow kokonaislukunaDim lCol kokonaislukunaDim rng as RangelRivi = Alue ("A1048576"). Loppu (xlUp) .Rivi'Käytä lRowa löytääksesi alueen viimeisen sarakkeenlCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnAseta rng = alue (solut (1, 1), solut (lRow, lCol))'msgbox näyttää alueemmeMsgBox "Alue on" & rng.AddressEnd Sub |
SpecialCells - LastCell
Voimme myös käyttää alueobjektin SpecialCells -menetelmää saadaksemme laskentataulukon viimeisen rivin ja sarakkeen.
| 123456789101112 | AlikäyttöSpecialCells ()Dim lRow kokonaislukunaDim lCol kokonaislukunaDim rng as RangeDim rngBegin As RangeAseta rngBegin = Alue ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnAseta rng = alue (solut (1, 1), solut (lRow, lCol))'msgbox näyttää alueemmeMsgBox "Alue on" & rng.AddressEnd Sub |
Käytetty alue
Käytetty alue -menetelmä sisältää kaikki solut, joissa on arvoja nykyisessä laskentataulukossa.
| 123456 | AlakäyttöalueEsimerkki ()Dim rng as RangeAseta rng = ActiveSheet.UsedRange'msgbox näyttää alueemmeMsgBox "Alue on" & rng.AddressEnd Sub |
Nykyinen alue
Nykyinen alue eroaa UsedRange -alueesta siinä, että se tarkastelee soluja, jotka ympäröivät aloitusalueemme (eli muuttuja rngBegin alla olevassa esimerkissä), ja tarkastelee sitten kaikkia soluja, jotka ovat '' kiinnittyneet '' tai liittyvät ilmoitettuun soluun. Jos rivillä tai sarakkeessa on tyhjä solu, Nykyinen alue lopettaa uusien solujen etsimisen.
| 12345678 | Alivirta -alue ()Dim rng as RangeDim rngBegin As RangeAseta rngBegin = Alue ("A1")Aseta rng = rngBegin.CurrentRegion'msgbox näyttää alueemmeMsgBox "Alue on" & rng.AddressEnd Sub |
Jos käytämme tätä menetelmää, meidän on varmistettava, että kaikki tarvitsemasi alueen solut on yhdistetty ilman tyhjiä rivejä tai sarakkeita.
Nimetty alue
Voimme myös viitata nimettyihin alueisiin koodissamme. Nimetyt alueet voivat olla dynaamisia siltä osin kuin tietoja päivitetään tai lisätään, alueen nimi voi muuttua sisältämään uudet tiedot.
Tämä esimerkki muuttaa alueen nimen "Tammikuu" kirjasimen lihavoituksi
| 12345 | AlaalueNimiEsimerkki ()Dim rng kuin alueAseta rng = Alue ("Tammikuu")rng.Font.Bold = = TottaEnd Sub |
Kuten alla olevasta kuvasta näet, jos alueen nimeen lisätään rivi, alueen nimi päivittyy automaattisesti sisältämään kyseisen rivin.
Jos sitten suoritamme esimerkkikoodin uudelleen, koodin vaikutusalue olisi C5: C9, kun taas ensimmäisessä tapauksessa se olisi ollut C5: C8.
Taulukot
Voimme viitata taulukoihimme (napsauta saadaksesi lisätietoja taulukoiden luomisesta ja käsittelystä VBA: ssa) koodissamme. Kun taulukon tiedot Excelissä päivitetään tai muutetaan, taulukkoon viittaava koodi viittaa sitten päivitettyihin taulukkotietoihin. Tämä on erityisen hyödyllistä, kun viitataan pivot -taulukoihin, jotka on liitetty ulkoiseen tietolähteeseen.
Käyttämällä tätä taulukkoa koodissamme voimme viitata taulukon sarakkeisiin kunkin sarakkeen otsikoiden perusteella ja suorittaa toimintoja sarakkeelle niiden nimen mukaan. Kun taulukon rivit kasvavat tai pienenevät tietojen mukaan, taulukkoalue muuttuu vastaavasti ja koodimme toimii edelleen koko taulukon sarakkeen osalta.
Esimerkiksi:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("toimittaja"). PoistaEnd Sub |
