Punte di programmazione di Excel

July 9

Il linguaggio di scripting per Microsoft Excel, Visual Basic, consente di estendere già ampia serie di strumenti di Excel. Tuttavia, la codifica direttamente in Visual Basic può essere difficile perché alcune funzioni (come l'ordinamento) richiedono l'impostazione diversi parametri in modo esatto.

È possibile evitare mano codifica del tutto lasciando il registratore di macro scrivere il codice per voi, ma questo non sempre netta fa il risultato che cercate. La soluzione è quella di combinare il meglio dei due mondi: l'uso delle macro per fare la codifica di massa e un po 'di codifica manuale per finire il lavoro.

Ordinare un tavolino

Utilizzare Visual Basic per ordinare un tavolino. Iniziare inserendo la seguente tabella in celle del G8 a H11 in Foglio 1:

Nome del cliente, saldo del conto
Zephyr vento Macchine, 5
Scrivanie Arondale, -10
Gumballs Bubble, 30

Dopo aver inserito i dati, selezionarlo e avviare la registrazione della macro (sviluppatore> Registra macro). Nome del MySort macro. Sort (Dati> Ordina A a Z) i dati selezionati sulla prima colonna, e interrompere la registrazione della macro.

Riprodurre il Macro

Provare a utilizzare la macro di ricorrere una diversa gamma: copiare e incollare il tavolo cliente in un'altra area del foglio di lavoro, e selezionare la nuova gamma. Replay della macro (sviluppatore> Macro> MySort), e notare l'effetto: i dati non sono stati ordinati. Cercare il problema nel codice Visual Basic creato da la registrazione di macro. Iniziare la navigazione verso il codice: premere Alt-F8, selezionare la macro MySort, e fare clic sul pulsante Modifica.

All'interno della funzione MySort, notare questa dichiarazione:

ActiveWorkbook.Worksheets ( "Sheet1") Sort.SortFields.Add chiave:. = Range ( "G8") ...

E una dichiarazione di poche righe dopo di esso:

Gamma .SetRange ( "C9: D12")

Entrambe queste affermazioni sono la fonte del problema; sono le ragioni per le quali MySort non riesce a ordinare la gamma selezionata: Visual Basic registratore di macro hard-coded l'intervallo di dati ( "C9: D12" e "G8") per ordinare. Il vostro compito è quello di sostituire gli intervalli fissi per rendere la macro sorta la selezione corrente.

Utilizzare la proprietà di selezione

In particolare, il parametro Key: = Range ( "G8") dice a Excel per ordinare la tabella dei clienti in base alla colonna "cliente", che inizia alle G8 cellulare. Per sostituire quella colonna originale "cliente" con quello all'interno del testo selezionato, guardare alla proprietà Selezione dell'oggetto Application. Questa proprietà si riferisce alla gamma di celle attualmente selezionate. Sostituire Legenda: = Range ( "G8") con questo: Legenda: = k. Potrai definire k in un attimo.

La prossima gamma di hard-coded per sostituire verifica alcune affermazioni giù: .SetRange Range ( "G8: H11"). Il metodo .SetRange indica a Excel la gamma completa che si desidera ordinare. Sostituire .SetRange Range ( "G8: H11") con questo: ".SetRange r".

La variabile R, come la variabile k (ey) appena digitata, verrà dalla proprietà Selection. Vai all'inizio della definizione macro e inserire queste righe:

Dim r, k come gamma

Set r = Application.Selection
Insieme K = r.Columns (1)

La prima assegnazione Set definisce l'intervallo di celle necessarie per il metodo .SetRange - l'intera selezione. La seconda assegnazione imposta l'intervallo necessario per la chiave di ordinamento alla prima colonna della selezione. È possibile ordinare sulla seconda colonna modificando la "1" a "2"

Testare la macro Corretto

La correzione codice è completo. Provatelo restituendo al foglio di lavoro, selezionare la tabella dei clienti - ovunque è stato posizionato sul foglio - e l'esecuzione MySort. Copiare quel tavolo per una nuova serie di celle, selezionare le celle ed eseguire MySort su di loro ancora una volta. Si noti il ​​tipo ora funziona correttamente.