Excel Programmering Tips

Skriptspråk for Microsoft Excel, Visual Basic, kan du utvide Excel allerede rikelig utvalg av verktøy. Men koding direkte i Visual Basic kan være vanskelig fordi enkelte funksjoner (som sortering) krever å sette flere parametere i en nøyaktig måte.

Du kan unngå hånd-koding helt ved å la makro opptaker skrive koden for deg, men dette betyr ikke alltid netto resultatet du søker. Løsningen er å kombinere det beste fra begge verdener: bruke makroer til å gjøre mesteparten koding og litt av hånden koding for å fullføre jobben.

Sorter Et lite bord

Bruk Visual Basic for å sortere et lite bord. Begynn med å skrive inn følgende tabell i celler G8 til H11 i Ark 1:

Kunde, saldo
Zephyr Wind Machines, 5
Arondale Pulter, -10
Boble Gumballs, 30

Etter inn dataene, velger du den og starte opptak av makro (Developer> Registrer makro). Navn makro MySort. Sorter (Data> Sorter A til Z) de utvalgte data på den første kolonnen, og stoppe opptak makroen.

Replay Macro

Prøv å bruke makro å ty et annet utvalg: kopier og lim kunden bordet til et annet område av regnearket, og velg den nye serien. Replay makroen (Developer> Makroer> MySort), og merke effekten: dataene ble ikke sortert. Se etter problemet i Visual Basic-kode opprettet av makroopptak. Begynn med å navigere til koden: trykk Alt-F8, velg MySort makro, og klikk på Rediger.

Inne i MySort funksjon, oppdager denne uttalelsen:

ActiveWorkbook.Worksheets ( "Ark1") Sort.SortFields.Add Nøkkel. = Range ( "G8") ...

Og en uttalelse noen linjer etter det:

.SetRange Range ( "C9: D12")

Begge disse påstandene er kilden til problemet; de er årsakene til at MySort ikke klarer å sortere det valgte området: Visual Basic makro opptaker hardkodet dataområdet ( "C9: D12" og "G8") for å sortere. Din oppgave er å erstatte hardkodede områder for å gjøre makro slags gjeldende valg.

Bruk markerings Eiendom

Nærmere bestemt parameter Key: = Range ( "G8") forteller Excel til å sortere kunden tabell basert på "kunde" kolonnen, som starter på celle G8. Å erstatte den opprinnelige "kunde" kolonnen med en inne i merkede teksten, se til Utvalg eiendommen av Application objektet. Denne egenskapen refererer til området for tiden valgte cellene. Bytt Key: = Range ( "G8") med denne: Key: = k. Du vil definere k på et øyeblikk.

Den neste hardkodet utvalg for å erstatte det oppstår noen uttalelser ned: .SetRange Range ( "G8: H11"). Metoden .SetRange viser til Excel hele utvalget du vil sortere. Bytt .SetRange Range ( "G8: H11") med denne: ".SetRange r".

Den r variabel, som k (ey) variabel du nettopp skrev, vil komme fra Selection eiendommen. Gå til starten av makro definisjon og legge inn disse linjene:

Dim r, k As Range

Still r = Application.Selection
Sett k = r.Columns (1)

Det første settet oppdrag definerer celleområdet som trengs for .SetRange metoden - hele utvalget. Den andre oppdrag setter området som trengs for den type nøkkel til den første kolonnen i utvalget. Man kan sortere på den andre kolonne ved å endre "1" til en "2."

Test Korrigert Macro

Koden korreksjon er fullført. Test det ved å returnere til regnearket, velger kunden bordet - uansett hvor du plasserte den på arket - og kjører MySort. Kopier denne tabellen til et nytt sett av celler, markerer du cellene, og kjøre MySort på dem igjen. Legg merke til den slags nå fungerer riktig.