TÉCNICAS DE EXCEL EM PROGRESS

[Link]
0800 704 3442
 SUMÁRIO

1. Como criar uma janela do Office.....................................................................................4

1.1. Como criar um arquivo novo no Excel.....................................................................4
1.2. Como criar uma nova planilha no arquivo..............................................................4
1.3. Definindo a ordem das planilhas..............................................................................4
1.4. Como abrir um arquivo existente no Excel..............................................................4
1.5. Como selecionar a planilha do Excel.......................................................................5
2. Definindo comandos utilizados na programação Excel.............................................5
3. Configurando a planilha...............................................................................................6
3.1. Definindo o nome da planilha..................................................................................6
3.2. Definindo configurações de impressão....................................................................6
3.3. Ocultando linhas de grade........................................................................................6
4. Desenvolvendo a planilha..............................................................................................7
4.1. Selecionando uma única célula................................................................................7
4.2. Selecionando várias células.....................................................................................7
4.3. Inserindo valores na célula......................................................................................7
4.4. Formatando a célula.................................................................................................7
4.4.1. Cor de preenchimento...............................................................................................7
4.4.2. Bordas........................................................................................................................8
4.4.3. Alinhamento..............................................................................................................8
4.4.4. Estilo de Fonte...........................................................................................................8
4.4.5. Formato de Numeração.............................................................................................9
4.4.6. Tamanho....................................................................................................................9
5. Desenvolvendo ambiente de gráfico.............................................................................9
5.1. Criando gráfico........................................................................................................9
5.2. Tipo de gráfico..........................................................................................................9
6. Configurando o gráfico...............................................................................................10
6.1. Nome.......................................................................................................................10
6.2. Título.......................................................................................................................10
6.3. Legenda...................................................................................................................10
6.4. Definindo cor de fundo para a região do gráfico...................................................10
6.5. Definindo efeitos de fundo para a região do gráfico.............................................10
6.6. Tamanho do gráfico................................................................................................10
6.7. Definindo valores para os eixos.............................................................................11
6.8. Definindo escalas e intervalos de valores..............................................................11
6.9. Aplicando labels de dados......................................................................................11
6.10. Selecionando e formatando labels de dados........................................................11
6.11. Formatando os dados do gráfico..........................................................................11
6.12. Marcadores de valores.........................................................................................12
6.13. Definição do local a ser implementado................................................................12
7. Outras utilidades Excel...............................................................................................12
7.1. Auto-filtro...............................................................................................................12
7.2. Ajuste automático de coluna...................................................................................12
7.3. Ajuste automático de linha.....................................................................................12
7.4. Ajuste manual de coluna.........................................................................................13
[Link]
0800 704 3442
 7.5. Congelamento de Planilha.....................................................................................13
7.6. Mesclar Células......................................................................................................13
7.7. Ocultar colunas......................................................................................................13
7.8. Desenhar linha........................................................................................................13
7.9. Inserir figura...........................................................................................................13
7.10. Inserir Caixa de Texto..........................................................................................13
7.11. Ocultar linhas de grade no gráfico......................................................................13
7.12. Incorporar um documento qualquer dentro de uma planilha Excel....................14
8. Excel avançado.............................................................................................................14
8.1. Criar fórmulas na própria planilha utilizando comandos via Progress................14
8.2. Criar cópia da planilha selecionada......................................................................14
8.3. Proteção de planilha...............................................................................................14
9. Pivot Table....................................................................................................................15
9.1. Sentando registros a serem alimentados na tabela dinâmica................................15
9.2. Criando tabela dinâmica........................................................................................15
9.3. Sentando colunas para inicializar na tabela dinâmica..........................................15
10. Criando Gráfico para Tabela Dinamica..................................................................15
10.1. Selecionando pasta para o Gráfico......................................................................15
10.2. Comando para criar gráfico.................................................................................16
10.3. Nome do Gráfico...................................................................................................16
10.4. Tipo do Gráfico.....................................................................................................16
10.5. Habilitando titulo..................................................................................................16
10.6. Alterando titulo.....................................................................................................16
10.7. Alterando Fonte....................................................................................................16
10.8. Habilitar nome no eixo Z.....................................................................................16
10.9. Alterando nome do eixo Z....................................................................................16

[Link]
0800 704 3442
1. Como criar uma janela do Office

define variable ch<Nome do programa> as component-handle no-undo.

create '<Nome do programa>.Application' ch<Nome do programa>.

ch<Nome do programa>:visible = true.

Onde Nome do programa pode ser: Excel, Powerpoint, Word, Access, etc. Dependendo da
necessidade do programador.

Obs: Ao final do programa, sempre limpar a handle de memória com o seguinte comando:

if valid-handle(Nome da Handle) then

release object Nome da Handle.

1.1. Como criar um arquivo novo no Excel

Declaração de nova aplicação Excel

define variable chWBook as component-handle no-undo.
Declaração de nova planilha Excel
define variable chWSheet as component-handle no-undo.

Adiciona um novo arquivo à aplicação

chExcel:Workbooks:Add().

Obs: Quando criado um novo arquivo, por padrão é criada uma planilha.

1.2. Como criar uma nova planilha no arquivo

chExcel:Sheets:Add.

1.3. Definindo a ordem das planilhas

Move a planilha especificada para após outra planilha

chExcel:Sheets("Nome da planilha que se quer mover"):move(,chChart).

1.4. Como abrir um arquivo existente no Excel

chExcel:Workbooks:Open(Endereço do Arquivo).

[Link]
0800 704 3442
1.5. Como selecionar a planilha do Excel

chWBook = chExcel:Workbooks(1).
chWSheet = chWBook:Sheets:Item(1).

Com a planilha selecionada no handle chWSheet, pode-se iniciar o processo de construção do

arquivo. Em seguida são mostrados comandos para a criação de uma janela no Excel.

2. Definindo comandos utilizados na programação Excel

Para que seja realizada a programação em Excel (VB), devem-se declarar alguns atributos.
Segue abaixo uma lista contendo alguns deles. Outros podem ser encontrados na pesquisa à
Biblioteca do Visual Basic.

&scoped-define xlCenter -4108

&scoped-define xlRight -4152
&scoped-define xlLeft -4131
&scoped-define xlTop -4160
&scoped-define xlEdgeLeft 7
&scoped-define xlContinuous 1
&scoped-define xlEdgeTop 8
&scoped-define xlEdgeBottom 9
&scoped-define xlEdgeRight 10
&scoped-define xlNone -4142
&scoped-define xlLandscape 2
&scoped-define xlInsideVertical 11
&scoped-define xlInsideHorizontal 12
&scoped-define xlPaperA4 9
&scoped-define xlMedium -4138
&scoped-define xlThin 2
&scoped-define xlBottom -4107
&scoped-define xlDataLabelsShowValue 2
&scoped-define xlDisplayGridlines 1
&scoped-define xlIncrementLeft 0
&scoped-define xlIncrementTop 0
&scoped-define xlRadar -4151
&scoped-define xlValue 2
&scoped-define xlDiamond 1
&scoped-define xlmsoTrue 1
&scoped-define msoFalse 0
&scoped-define xlMove 2
&scoped-define msoShapeRectangle 1
&scoped-define msoTrue -1
&scoped-define xlSourceAutoFilter 3
&scoped-define xlXYScatter -4169
&scoped-define xlCategory 1
&scoped-define x1Primary 1
&scoped-define x1Secondary 2
&scoped-define msoGradientHorizontal 1
&scoped-define xlAnd 1
&scoped-define xlNoSelection -4142
&scoped-define xlThick 4
[Link]
0800 704 3442
&scoped-define xlLocationAsObject 2
&scoped-define xlLocationAsNewSheet 1
&scoped-define xlUpward -4171
&scoped-define xlUnderlineStyleNone -4142
&scoped-define msoBringToFront 0

3. Configurando a planilha

3.1. Definindo o nome da planilha

chWSheet:Name = "Nome da Planilha".

3.2. Definindo configurações de impressão

if trim(session:printer-name) <> "" then do:

Orientação da página (Retrato ou Paisagem)

chWSheet:PageSetup:Orientation = {&xlLandScape}.

Tamanho do papel (A4)

chWSheet:PageSetup:PaperSize = {&xlPaperA4}.

Margens
chWSheet:PageSetup:LeftMargin = 2.
chWSheet:PageSetup:TopMargin = 2.

Zoom
chWSheet:PageSetup:Zoom = false.

Tamanho da planilha na folha (total)

chWSheet:PageSetup:FitToPagesWide = 1.
chWSheet:PageSetup:FitToPagesTall = 32767.
end.

3.3. Ocultando linhas de grade

chExcel:ActiveWindow:DisplayGridlines = False.

[Link]
0800 704 3442
4. Desenvolvendo a planilha

Feitos os passos acima, pode-se iniciar a construção da planilha. Alguns dos comandos para
tal procedimento são descritos a seguir.

4.1. Selecionando uma única célula

chWSheet:Cells(linha, coluna):Select.

4.2. Selecionando várias células

chWSheet:Range(“A1:A10”):Select. - Para células de uma coluna apenas.

chWSheet:Range(“A1:C15”):Select. - Para células de várias colunas, de modo
que estejam umas ao lado das outras.
chWSheet:Range(“A1:A10;D12:D15”):Select. - Para células de várias colunas, não
havendo necessidade de estarem lado-a-
lado.
chWSheet:Range(“A”):Select. - Para uma coluna inteira.
chWSheet:Range(“A:C”):Select. - Para várias colunas inteiras.
chWSheet:Range("D10"):select. - Seleciona apenas uma célula. Tem o
mesmo impacto que a função Cells.

Obs: O comando Select serve para selecionar não somente células, mas também planilhas,
gráficos, figuras, entre outros.
Uma vez selecionado o objeto desejado, deve-se utilizar o comando chExcel:Selection para
fazer referência àquele mesmo objeto, podendo então, ser aplicadas funções, como pintar
borda ou escrever.

4.3. Inserindo valores na célula

chExcel:Selection = "Texto 1".

chExcel:Selection = 1.65.

4.4. Formatando a célula

4.4.1. Cor de preenchimento

chExcel:Selection:Interior:Colorindex = 5. - Onde o valor é o número da cor na

paleta definido no Excel.

[Link]
0800 704 3442
4.4.2. Bordas
Grossura
chExcel:Selection:Borders({&xlLados}):Weight = {&xlMedium}.
Estilo de linha (continua, tracejada, …)
chExcel:Selection:Borders({&xlLados}):LineStyle = {&xlContinuous}.

Onde Lados é a definição de quais lados serão aplicados a alteração.

Ex: EdgeLeftTopBottomRight - Dentro, Esquerda, Cima, Baixo, Direita.

Pode-se selecionar os dados desejados, deletando do código os comandos das quais não se
quer.
Obs: Os comandos de Lado devem ser escritos junto.
Ex:
chExcel:Selection:Borders({&xlEdgeLeftTopBottomRight}):Weight = {&xlMedium}.

Não há uma ordem sequencial para defini-los.

4.4.3. Alinhamento

Horizontal
chExcel:Selection:HorizontalAlignment = {&xlCenter}.
Vertical
chExcel:Selection:HorizontalAlignment = {&xlCenter}.

4.4.4. Estilo de Fonte

Negrito
chExcel:Selection:Font:Bold = True.
Itálico
chExcel:Selection:Font:Italic = True.
Sublinhado
chExcel:Selection:Font:Underline = True.
Tipo de Fonte
chExcel:Selection:Font:Name = "Nome da Fonte (Ex:Arial)".

[Link]
0800 704 3442
4.4.5. Formato de Numeração
Percentual
chExcel:Selection:NumberFormat = "0%".
Casas Decimais
chExcel:Selection:NumberFormat = "0,0".
- Podendo ser inseridas quantas casas decimais forem necessárias.

Ambos
chExcel:Selection:NumberFormat = "0,0%".

Separador de milhar e decimal utilizando as configurações regionais do Windows

chExcel:Selection:NumberFormat = "#" +
string(chExcel:Application:International({&xlThousandSeparator})) + "##0" +
string(chExcel:Application:International({&xlDecimalSeparator})) + "00".

É preciso fazer a declaração no início do programa dos pré-processadores

&xlThousandSeparator e &xlDecimalSeparator utilizados neste comando.

&scoped-define xlDecimalSeparator 3
&scoped-define xlThousandSeparator 4

4.4.6. Tamanho

chExcel:Selection:Font:Size = Tamanho da Fonte (Ex: 12).

5. Desenvolvendo ambiente de gráfico

Desenvolvida a planilha, deve-se selecionar as células contendo os dados a serem mostrados

no gráfico. A seguir, os passos descritos abaixo criam e formatam o gráfico.

Declara novo gráfico Excel

define variable chChart as component-handle no-undo.

5.1. Criando gráfico

chChart = chWBook:Charts:Add().

5.2. Tipo de gráfico

chChart:ChartType = Tipo de gráfico - Os tipos podem variar, de acordo com a

necessidade de cada desenvolvimento.
Seguem dois já utilizados em
[Link]
0800 704 3442
 desenvolvimentos: {&xlRadar} e
{&xlXYScatter}.

6. Configurando o gráfico

6.1. Nome

chChart:Name = "Nome do Gráfico".

6.2. Título

chChart:HasTitle = True.
chChart:ChartTitle:Characters:Text = “Título do Gráfico”.

6.3. Legenda

Exibe Legenda
chChart:HasLegend = TRUE.
Indica Posição
chChart:Legend:Position = {&xlBottom}.
Tamanho
chChart:Legend:Width = 497.

6.4. Definindo cor de fundo para a região do gráfico

chChart:PlotArea:interior:ColorIndex = 2.

6.5. Definindo efeitos de fundo para a região do gráfico

chExcel:ActiveChart:PlotArea:Select().
chExcel:Selection:Fill:TwoColorGradient({&msoGradientHorizontal}, 4).
chExcel:Selection:Fill:Visible = True.
chExcel:Selection:Fill:ForeColor:SchemeColor = 14.
chExcel:Selection:Fill:BackColor:SchemeColor = 2.

6.6. Tamanho do gráfico

chChart:PlotArea:Width = "200".
chChart:PlotArea:Height = "200".
chChart:PlotArea:Left = "220".
chChart:PlotArea:Top = "160".

[Link]
0800 704 3442
6.7. Definindo valores para os eixos

Cada série de valores, geralmente determinado por uma coluna selecionada na

planilha, denominado de SeriesCollection, possui atributos diversos, como alterar a grossura
da linha ou do ponto de valor, entre outros.
A lógica a seguir atribui colunas contendo os valores do eixo X.

chChart:SeriesCollection(Série Selecionada. Ex: 1):XValues = chWSheet:Range(Células de

valores para o eixo X).

A lógica a seguir atribui colunas contendo os valores do eixo Y.

chChart:SeriesCollection(Série Selecionada. Ex: 1):Values = chWSheet:Range(Células de

valores para o eixo X).

6.8. Definindo escalas e intervalos de valores

Eixo X
chExcel:ActiveChart:Axes({&xlCategory}, {&xlSecondary}):MaximumScale = 1.2.
chExcel:ActiveChart:Axes({&xlCategory}, {&xlSecondary}):MajorUnit = 0.1.

Eixo Y
chExcel:ActiveChart:Axes({&xlValue}, {&xlSecondary}):MaximumScale = 2.
chExcel:ActiveChart:Axes({&xlValue}, {&xlSecondary}):MajorUnit = 0.1.

6.9. Aplicando labels de dados

chChart:SeriesCollection(1):ApplyDataLabels(0, 0, 0, 0, 0, 0, 1, 0, 0, 0).

6.10. Selecionando e formatando labels de dados

chChart:SeriesCollection(1):DataLabels:Select.
chExcel:Selection:Font:FontStyle = "Negrito".
chExcel:Selection:Font:ColorIndex = 10.

6.11. Formatando os dados do gráfico

chChart:SeriesCollection(1):Select.
Nome
chChart:SeriesCollection(1):NAME = "Nome da série que irá ser mostrada na legenda”.
Cor
chChart:SeriesCollection(1):Border:ColorIndex = 10.
[Link]
0800 704 3442
Tamanho
chChart:SeriesCollection(1):Border:Weight = {&xlThick}.

Estilo de linha
chChart:SeriesCollection(1):Border:lineStyle = {&xlContinuous}.
Estilo de marcador
chChart:SeriesCollection(1):MarkerStyle = {&xlDiamond}.

6.12. Marcadores de valores

Marcadores de valores são pontos onde o valor pode se encontrar. É muito utilizado em
gráficos onde os valores são plotados por retas.

Cor de fundo e borda

chChart:SeriesCollection(1):Points(seq):MarkerBackgroundColorIndex = 3.
chChart:SeriesCollection(1):Points(seq):MarkerForegroundColorIndex = 3.
Tamanho
chChart:SeriesCollection(1):MarkerSize = 8.

Onde seq é o marcador da qual se queira realizar a formatação.

6.13. Definição do local a ser implementado

Define se o gráfico ficará em uma janela separada (padrão) ou se ficará na planilha de dados.
chChart:Location({&xlLocationAsObject}, "Nome da planilha onde será alocado").

7. Outras utilidades Excel

Seguem abaixo outras implementações para melhorar a aplicação Excel.

7.1. Auto-filtro

chExcel:workbooks(1):worksheets(1):range("1:1"):AutoFilter(,,,,).

7.2. Ajuste automático de coluna

chExcel:Selection:EntireColumn:AutoFit.

7.3. Ajuste automático de linha

[Link]
0800 704 3442
chExcel:selection:EntireRow:AutoFit.

7.4. Ajuste manual de coluna

chWSheet:columns("Coluna"):ColumnWidth = Tamanho da coluna.

7.5. Congelamento de Planilha

chExcel:ActiveWindow:FreezePanes = True.

7.6. Mesclar Células

chExcel:selection:MergeCells = True.

7.7. Ocultar colunas

chWSheet:Range("A"):Columns:Hidden = True.

7.8. Desenhar linha

chWSheet:Shapes:AddLine((Início X, Início Y, Fim X, Fim Y):Select.

7.9. Inserir figura

chWSheet:Pictures:INSERT(Caminho da imagem. Ex: “c:\[Link]”):Select.

chExcel:SELECTION:ShapeRange:HEIGHT = 35.
chExcel:SELECTION:ShapeRange:WIDTH = 72.

7.10. Inserir Caixa de Texto

chWSheet:TextBoxes:Add(Início X, Início Y, Largura, Altura):Select().

7.11. Ocultar linhas de grade no gráfico

chExcel:ActiveChart:Axes({&xlValue}, {&xlPrimary}):HasMajorGridlines = False.

chExcel:ActiveChart:Axes({&xlCathegory}, {&xlPrimary}):HasMajorGridlines = False.

[Link]
0800 704 3442
7.12. Incorporar um documento qualquer dentro de uma planilha Excel

chExcel:ActiveSheet:OLEObjects:Add(,"C:\Tmp\[Link]",,False,False,,,,,,).

8. Excel avançado

8.1. Criar fórmulas na própria planilha utilizando comandos via Progress

Para criar formulas de cálculo, o Excel utiliza o seguinte método: a identificação das colunas
(C) são feitas através da célula selecionada. Por exemplo, se a célula selecionada for “E8” e
quiser utilizar valores da célula “B8”, deve-se contar três casas a menos e incluir na fórmula.
Ex: “=AVERAGE(RC[-3]:RC)”. O sinal irá indicar se são contadas casas para a esquerda (-)
ou direita (+). O mesmo ocorre para as linhas (R).

Média
chExcel:Selection = “=AVERAGE(RC[-3]:R[-4]C[-3])”.
Contagem
Adiciona ao contador quantas células estão selecionadas em um intervalo especificado.

chExcel:selection = "=COUNTA(R[-2]C[-5])".
Contagem Condicional
Adiciona ao contador e imprime na célula selecionada o valor da contagem se determinada
função atender aos requisitos da fórmula. Ex: Contar quantas células possuem a letra “J”.

chExcel:selection = "=COUNTIF(R[2]C:[R[-1]C,"J")".
Fórmula Livre
Pode-se criar também formulas com estilo livre.

Ex:
chExcel:Selection:FormulaR1C1 = "=(1 + 9) / 5)".
Nesta mesma fórmula, pode-se realizar referência a células.

8.2. Criar cópia da planilha selecionada

chExcel:Sheets("Planilha a ser copiada"):copy(,handle da planilha que virá depois da planilha

copiada).

8.3. Proteção de planilha

Protege a planilha
chExcel:Sheets("Nome da planilha a ser protegida"):Protect.
[Link]
0800 704 3442
Desabilita Seleção
chExcel:Sheets("Nome da planilha a ser protegida "):EnableSelection = {&xlNoSelection}.
Oculta planilha
chExcel:Sheets("Nome da planilha a ser protegida "):Visible = False.

9. Pivot Table

9.1. Sentando registros a serem alimentados na tabela dinâmica.

DEF VAR (variável) AS CHAR NO-UNDO.

ASSIGN (variável) = "Dados!R1C1:R" + STRING(*numero de linhas dos registros) + "C" + string(números de

colunas dos registros).

*Números de linhas; corresponde ao total de registros que serão listados para criar a tabela dinâmica.
*Números de colunas; corresponde ao total de colunas que serão listados para criar a tabela dinâmica.

9.2. Criando tabela dinâmica.

chWBook:PivotCaches:Add(1,string(variavel)):CreatePivotTable("","Tabela dinamica1",,).

9.3. Sentando colunas para inicializar na tabela dinâmica.

chWbook:ActiveSheet:PivotTables("Tabela dinamica1"):PivotFields("nome da coluna"):ORIENTATION = 1

NO-ERROR.

Obs. Em nome da coluna, deve se escrever o nome igual ao label da coluna dos dados que estão sendo listados.

10. Criando Gráfico para Tabela Dinamica

10.1. Selecionando pasta para o Gráfico.

Caso o gráfico sega na mesma pasta que a tabela dinâmica use o comando abaixo.

chExcel:Workbooks:Item(1):Sheets("Plan2"):Select.

Caso o gráfico sega em uma nova pasta use o comando abaixo.

chWbook:Charts:ADD().
[Link]
0800 704 3442
10.2. Comando para criar gráfico.
chWbook:ActiveSheet:Shapes:AddChart:Select.

10.3. Nome do Gráfico.

chWbook:ActiveSheet:NAME = "Grafico".

10.4. Tipo do Gráfico.

Definição variável.
&scoped-define xl3DColumnClustered 54.

chWbook:ActiveChart:ChartType = {&xl3DColumnClustered}. (Tipo do gráfico)

Obs. Este é um exemplo de gráfico a ser utilizado, caso seja necessário alterar o gráfico deve
se gerar um macro no Excel.

10.5. Habilitando titulo.

chWbook:ActiveChart:HasTitle = True.

10.6. Alterando titulo.

chWbook:ActiveChart:ChartTitle:Characters:Text = "descrição".

10.7. Alterando Fonte.

chWbook:ActiveChart:ChartTitle:Characters:FONT:bold = TRUE.

10.8. Habilitar nome no eixo Z.

Definição variável.
&scoped-define xlValue 2

chWbook:ActiveChart:Axes({&xlValue},1):HasTitle = True.

10.9. Alterando nome do eixo Z.

chWbook:ActiveChart:Axes({&xlValue},1):AxisTitle:Characters:Text = "descrição".

[Link]
0800 704 3442