EXCELer

Tópicos relacionados:

• Números dos ID's de Controlo do CommandBar (Microsoft)
• Disable Command bars and controls in Excel 97-2003 (Ron de Bruin)
• Sample macros to return ID for a CommandBar control (Microsoft)
• Lists of Control IDs - Excel 2003 e 2007, para Download (Microsoft)
• Create menu for use in international environment distributed as xla (VbaExpress)

Clique aqui em:

Boas festas!!! e faça o dowload do postal de boas festas!!!

Feliz Ano de 2009 para todos!!!

Por defeito, o Excel aloca a RAM para 16 níveis de anulações ("undo"). O ajustamento do Registry pode mudar o valor por defeito, para o valor que seja especificado.

Para ver como fazer, clique: AQUI

Válido para o Excel 2000, 2002, 2003 e 2007.

Este add-in adiciona uma nova aba (Tab) na barra do Microsoft Office Excel 2007.
Os comandos nesta aba (Tab) dão-lhe acesso fácil ao conteúdo livre do Office Online que ajuda, muito rapidamente, a aprendizagem do Excel 2007.

Novo Add-In para o Excel 2007 - Clique aqui

Questão (adaptada):

“ Como faço para o Excel me dar o endereço da célula que possui o maior valor numa determinada coluna?”

Tomemos o seguinte exemplo:

Se pretendessemos apenas saber qual o maior valor encontrado, então utilizariamos a seguinte fórmula:

=MÁXIMO(A1:A9)

No exemplo, o valor mais elevado é o 7.

Então, o que se pretende, é obter uma fórmula que nos diga qual a célula que contém o valor mais elevado, ou seja, “A3”.

A Função CORRESP() procura por um valor num array e devolve a posição relativa desse valor.

Por sua vez, a Função ENDEREÇO() devolve a representação em texto do endereço de uma determinada célula.

Assim, a fórmula a utilizar, será a seguinte, combinando as três Funções descritas:

=ENDEREÇO(CORRESP(MÁXIMO(A1:A9);$A$1:A9;0);1)

ou seja, devolve

Pode, igualmente, utilizar-se uma UDF, como, por exemplo, esta apresentada num newsgroup por Ron Rosenfeld:

A sintaxe é a seguinte: =MaxAdr(rng), em que rng é o Range que se pretende utilizar:

Function MaxAdr(rng As Range)
    Dim c As Range
    Dim MaxNum As Double

    MaxNum = Application.WorksheetFunction.Max(rng)

    For Each c In rng
        If c.Value = MaxNum Then
            MaxAdr0 = c.Address
            Exit Function
        End If
    Next c
End Function

Tópicos relacionados:

• MAX() Function
• MATCH () Function
• ADDRESS() Function
• Find address of cell containing maximum value - Joseph Rubin's Exceltip.com
• Address of the max value in range - Joseph Rubin's Exceltip.com

Há dias, foi-me colocada a seguinte questão (adaptada):

“ Quando trabalhava com o Lotus123 fazia /Range/ Name/Create para uma zona seleccionada e mais tarde poderia utilizar esse nome para imprimir, apagar, formatar etc. Como é que eu faço isso agora, imprimir por exemplo, sem ter que estar sempre a definir a área de impressão? “

A minha resposta no Forum do EXCELer, foi a seguinte:

“ Defines um nome para o teu Range, ou seja:
a   -   Marcas a área pretendida
b - Escolhes de seguida: INSERIR>NOME>Definir, digitas o nome pretendido, adicionas e dás OK.

c   -  Depois, vais ao Campo Name Box (ou Caixa de Nome, em português) e clicas no dropdown para veres o nome. Verificarás que o Range fica imediatamente marcado.

Para imprimires, vais a FICHEIRO>IMPRIMIR, marcas o item Selecção e dás OK.”

A Imagem do que se pretende:

a -

b -

c -

Resultado:

Para a impressão:

Ou seja, podemos definir os Ranges que quisermos e, como no exemplo, efectuarmos as diversas impressões, sem ter que estar sempre a definir as áreas de impressão.

Tópicos relacionados:

• Excel Range-Name Roundup - Charley Kyd - Excel MVP
• Working with Named Ranges in Excel - Chip Pearson - Excel MVP
• Define or Change a Named Range - VBA Express
• Defined Names - JKP Application Development Services - Excel MVP
• Use Range Names in Formulas - Contextures - Debra Dalgleish Excel MVP
• Using Range Names in Formulas - University of Wyoming

E fui ao 2009 MVP Open Day, em Madrid, no dia 3 deste mês de Outubro!!!!

Um espectáculo!!!

E apreciei a iniciação dos novos MVP’s na Comunidade Microsoft!!!!

Eis a Microsoft MVP Lead Ibérica, Cristina Herrero, a “armar” mais um “cavaleiro MVP”…

E a foto dos MVP’s presentes, para a posteridade!…

Foi, como não podia deixar de ser, um momento inesquecível, que irá perdurar na minha memória!!!

Ainda a propósito do post anterior, foi colocada a questão de encontrar uma data que esteja entre a Data inicial e a Data final (por exemplo, 14/03/2007-11/05/2008) e depois partir daí para a contagem dos dias até à data final (que poderá estar também entre uma data inicial e final mais abaixo).

A imagem do que se pretende:

Ou seja,

Campos a preencher:

- Data de início (será 14/03/2007) - D2
- Data de fim (será 11/05/2008) -D4
- Capital (será 1000) - E2

Resultado pretendido:

De 14/03/2007 a 30/06/2007 - 109 dias à taxa de 0,1058 = 31,60
De 01/07/2007 a 31/12/2007 - 184 dias à taxa de 0,1107 = 55,80
De 01/01/2008 a 11/05/2008 - 132 dias à taxa de 0,1120 = 40,50
-------------------------------------------------- Total de juros: 127,90

Então, com a utilização da função PROCV(), poderemos chegar ao resultado pretendido.

Assim, teremos:

Em F2:=SE(D2="";ARRED(C2*((B2-A2)+1)/365*$E$2;2);ARRED(PROCV(D2;$A$2:$C$4;3)*((B2-D2)+1)/365*$E$2;2))

Em F3:=SE(D3="";ARRED(C3*((B3-A3)+1)/365*$E$2;2);ARRED(PROCV(D3;$A$2:$C$4;3)*((B3-D3)+1)/365*$E$2;2))

No Forum do EXCELer, foi apresentada a seguinte questão (adaptada):

“A tabela abaixo representa valores de taxas de juros aplicáveis nas datas indicadas, isto é, entre as datas indicadas aplicam-se as taxas que estão à frente.
      A                  B                  C
1 01/01/07    30/06/07       0,1058
2 01/07/07    31/12/07       0,1107
3 01/01/08    30/06/08       0,1120
4 01/07/08    31/12/08       0,1120

(Em que A= Data de Início
Em que B= Data de Fim
Em que C= Taxa a Aplicar)

Se um documento for emitido em 14/03/07 e contar juros até 11/05/08, é possível obter o valor dos juros na totalidade.

Eu pretendia obter no final valores parcelares, assim:

De 14/03/2007 a 30/06/2007 - 109 dias à taxa de 0,1058 = 31,60
De 01/07/2007 a 31/12/2007 - 184 dias à taxa de 0,1107 = 55,80
De 01/01/2008 a 11/05/2008 - 132 dias à taxa de 0,1120 = 40,50 “

Representemos então, o que se pretende:

Para calcularmos o número de dias entre as duas datas que contêm a mesma taxa de juro a aplicar, utilizaremos a seguinte fórmula:

Em E1: =(B1-A1)+1

Em E2: =(B2-A2)+1

Em E3: =(B3-A3)+1

(A formatação destas células deverá ser “Número”, sem casas decimais)

E agora, para calcularmos os valores dos juros, de forma parcelar, teremos:

Em F1: =ARRED(C1*E1/365*1000;2)

Em F2: =ARRED(C2*E2/365*1000;2)

Em F3: =ARRED(C3*E3/365*1000;2)

(A formatação destas células deverá ser “Número”, com 2 casas decimais)

Tópicos relacionados:

• A Função ARRED() - Microsoft Office Online
• As Funções mais usadas em Excel – ISCTE
• Manual do Excel - www.aprendercomastics.net
• Round Function - Ted French (About.com)

 Tópicos relacionados:

• Type Statement - Visual Basic for Aplications Reference
• Managing State in Microsoft Excel VBA - by Bill Dawson
• VBA Functions - The SpreadSheet Page - J. Walk

Por mail, fizeram-me a seguinte pergunta (adaptada): "...gostava de incluir a informação ao utilizador quais as medidas a cortar de uma barra para maior aproveitamento, por exemplo: tenho 5 cortes com 112, 1 com 30, 2 com 4 etc etc.. as barras têm 6 metros e a intenção era tirar o maior aproveitamento no corte, para as sobras serem o menor possivel, isto é, escolher valores cuja soma fosse o mais perto possivel dos 6 metros, a mensagem ao utilizador fosse por exemplo: de uma barra cortas 5 com 112 e uma com 30, na outra cortas tantas com tanto e outras ou outra com tanto etc..."

Bom, vejamos a imagem da possível resolução do problema:

As fórmulas:

Em E5: =ARRED.PARA.BAIXO((C1/C5);0)

Em E6: =SE(C6="";"";ARRED.PARA.BAIXO((I5/C6);0))

Em E7: =SE(C7="";"";ARRED.PARA.BAIXO((I6/C7);0))

Em I5: =C1-(C5*E5)

Em I6: SE(C6="";"";I5-(C6*E6))

Em I7: SE(C7="";"";I6-(C7*E7))

Mas, e se não quisermos, no exemplo, cortar as 5 barras da medida 112, mas apenas 4? Como faríamos para, aceitando a alteração, harmonizar o número de cortes relativos às outras medidas?

Vejamos então, o exemplo do que agora referi:

Eis as fórmulas, referentes à coluna I:

Em I5: =SE(F5="";C1-(C5*E5);C1-(C5*F5))

Em I6: =SE(F6="";SE(C6="";"";I5-(C6*E6));SE(C6="";"";I5-(C6*F6)))

Em I7: =SE(F7="";SE(C7="";"";I6-(C7*E7));SE(C7="";"";I6-(C7*F7)))

Tópicos relacionados:

• A Função ROUNDDOWN [Português: ARRED.PARA.BAIXO]

• Excel 2007 ROUNDDOWN Function - About.Com

• Introduction to Computing - School of Computer Science & Information Systems

Se pretendermos utilizar a indicação Option Explicit (Option Explicit Statement), a mesma deve aparecer num módulo antes de todos os procedimentos. Quando a Option Explicit aparece num módulo, devem declarar-se explicitamente todas as variáveis usando as indicações Dim, Private, Public, ReDim, ou Static. Se tentarmos usar uma variável tipo “undeclared”, ocorre um erro na compilação. Se não se utilizar a indicação Option Explicit, todas as variáveis “undeclared” são do tipo Variant, a menos que o tipo seja especificado de outra maneira, por exemplo, com uma indicação de DefType. Por norma, é de utilizar a indicação Option Explicit para evitar incorrecções de digitação do nome de uma variável existente.

Nas opções de VBA, encontra-se uma checkbox, chamada “Require Variable Declaration”:

Seleccionando esta checkbox, fazemos com que o VBA insira a linha “Option Explicit”, no topo de qualquer módulo que criamos. Esta indicação força-nos a declarar todas as variáveis antes de as referirmos no nosso código.

Tópicos relacionados:

• Option Explicit Statement (Visual Basic)- MSDN Microsoft

• Visual Basic For Aplications - Heat Transfer Today – R. J. Ribando 26/06/07

• Option Explicit - Microsoft.com

SAVE THE DATE

Introduzido em 1992, os Reddick VBA (RVBA) Naming Conventions, foram publicados num grande número livros e revistas. São utilizados por milhares de programadores em todo o mundo, para denominar objectos dentro de seu código de VBA.

Estes RVBA Naming Conventions têm sido suplementados agora pelas RVBA Coding Conventions. A finalidade dos Reddick VBA (RVBA) Naming Conventions é fornecer um guia para denominar objectos no Visual Basic for Applications (VBA). Ter convenções é valioso em todo o projecto de programação. Quando são utilizados, o nome do objecto informa sobre o significado do objecto. Estas convenções tentam fornecer uma maneira de estandardizar esse significado pelo corpo de programadores de VBA.

Os objectivos das convenções de Coding Reddick VBA (RVBA), são fazer com que o código:

· Seja mais legível: As convenções permitem que um leitor compreenda o significado do código com menos esforço.

· Tenha melhor manutenção: O código pode mais confiantemente ser mudado face aos erros e realçar a sua funcionalidade.

· Dê maior confiança: O código tem maior probabilidade de se executar como seria esperado.

· Seja mais eficiente: O código executa mais rapidamente ou consome menos recursos.

Para saber mais, aceder a:

• RVBA Naming Conventions
  
• RVBA Coding Conventions
  
• Microsoft Consulting Services convenções de nomenclatura para o Visual Basic

Pode fazer o download aqui do "The Reddick VBA (RVBA) Naming Conventions, Version 6.01".

Esta função, por defeito, apresenta uma mensagem numa caixa de diálogo, a qual tem um botão "OK". No entanto, esta função pode apresentar outros botões e/ou um icon na caixa de diálogo.

Exemplo:

O código, para este exemplo, será:

Sub Mensagem()
    msg = MsgBox("Pretende Continuar?", vbInformation + vbYesNoCancel)
End Sub

No entanto, para obtermos este resultado, poderemos utilizar os chamados valores dos botões de argumento, como no seguinte exemplo de código:

Segue-se um quadro com o nome das principais Constantes, o seu Valor e a respectiva Descrição:

Tópicos relacionados:

• Ten Tips for Microsoft Office VBA Library Development
• Visual Basic – Messages and data input/output
• Understanding the MsgBox command in Visual Basic

Se pretendermos que uma determinada célula contenha o nome da folha (nome esse que se encontra no tabulador dessa folha), como no exemplo:

podemos utilizar o seguinte segmento de código, ligado à folha em causa:

Private Sub Worksheet_Activate()
    On Error Resume Next
    Range("C6").Value = ActiveSheet.Name
End Sub

Se pretendermos repetir um footer (rodapé) em diversas folhas, a partir de uma determinada folha, podemos efectuar da seguinte forma:

1º - Criamos o nosso footer, como habitualmente (Ver > Cabeçalho/Rodapé)

2º - Seleccionamos a folha que contém o footer (parent worksheet) e a seguir CTRL-Click no(s) tabulador(es) que queremos que venham a conter aquele footer (child(ren) worksheet), ou seja, marcamos um grupo.

3º - Na barra de ferramentas, escolhemos Ficheiro > Configurar Página

4º - Desmarcar o grupo e verificar se o footer já se encontra nas outras worksheets.

Tópicos relacionados:

• Grouping/UnGrouping WorkSheets - University of Wisconsin
• Topics: Dynamic WorkSheets Tab Names - Allen Wyatt's Excel Tips

Há dias, foi-me perguntado qual o método para se poder imprimir, de uma só vez, Ranges provenientes de duas WorkSheets.

Uma das possibilidades, é utilizar uma mistura de Excel (definição das áeras de impressão nas duas WorkSheets) e de VBA, no sentido de, a partir daquela definição, imprimir de uma só vez, mas em duas folhas distintas, os Ranges pretendidos.

Exemplo:

Sheet1:

Sheet2:

Para definir as respectivas áreas de impressão:

1 - "marcar" o Range pretendido

2 - File>PrintArea>Set Print Area

3 - A definição das áreas de impressão deve ser efectuada para as duas Sheets.

E, agora, para a impressão dos Ranges definidos, o código VBA:

Tópicos relacionados:

• Array Functions - Alan Beban
• Array Formulas - Extreme VB Talk

Enquanto criamos objectos (barra de Ferramentas - Desenho), se mantivermos premida a tecla "SHIFT", estaremos a efectuar o que se chama "constrangimento".

Os efeitos são os seguintes:

• Os botões "linha" e "seta", desenham linhas perfeitamente horizontais ou verticais, ou linhas diagonais com incrementos exactos de 15º (0º, 15º, 30º, etc...)
• o botão "rectângulo", desenha quadrados perfeitos
• o botão "oval", desenha círculos perfeitos

Exemplo:

Tópicos relacionados:

• Create spiffy graphics in Excel 2007 (Alguns também para Excel 2003)
• Pictures & Graphics

Recebido por mail (adaptado):

"A minha duvida é a seguinte:
Tenho umas macros num documento de excel para executarem algumas funções, entre as quais a de gravar uma imagem em formato MDI.
Acontece que o ficheiro principal está num servidor assim como as pastas onde gravo essas imagens.
Tenho atalhos em varios computadores da rede para esse ficheiro.
1ª - Qual a possibilidade de puder trabalhar no mesmo ficheiro ao mesmo tempo nos tres computadores?
2ª- Após a instalação de uma impressora nova que serve dois desses computadores as macros deixaram de funcionar convenientemente, pois só num
dos computadores é permitido executar a macro correctamente. No outro computador para quando chega à parte de gravar  a imagem .MDI.
As macros são as seguintes ou melhor parte delas.
________________________________________________
Sheets("M D").Select
    Range("A1").Select
      Application.Dialogs(xlDialogPrinterSetup).Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ActivePrinter = "Microsoft Office Document Image Writer em
Ne01:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        ActivePrinter:="Microsoft Office Document Image Writer em Ne01:", _
        Collate:=True, _
        PrintToFile:=True, _
        PrToFileName:="\\Hdl\cir\Stocks\Coz\Mapas\Mapa de  " &
Format(Now, "dd-mmm-yy h-mm-ss") & ".mdi"
__________________________________________________

Sheets("Extemporanea").Select
  Application.Dialogs(xlDialogPrinterSetup).Show
  ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ActivePrinter = "Microsoft Office Document Image Writer em
Ne01:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
        ActivePrinter:="Microsoft Office Document Image Writer em Ne01:", _
        Collate:=True, _
        PrintToFile:=True, _
        PrToFileName:="\\Hdl\cir\Stocks\Coz\Extemporaneas\Extemporânea

Quando a macro chega a [[Application.ActivePrinter = "Microsoft Office Document Image Writer em Ne01:"]] pára, mas só num computador, se alterar para
[[Application.ActivePrinter = "Microsoft Office Document Image Writer em Ne00:"]], fica a funcionar nesse computador e dá erro no outro.
Serão as portas  da impressora?? Como faço para remediar a coisa??
Já tentei retirar  [[em Ne00 ou em Ne01]], mas tambem dá erro."

Comentário:

Como possível solução, inserir, no início das macros, o seguinte código:

Dim sStr As String

sStr = Application.ActivePrinter
Application.EnableEvents = False
On Error Resume Next
Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne02:"
If Err.Number = 1004 Then
    Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne01:"
    Err.Clear
End If

Tópicos relacionados:

• Change the default printer using VBA in Microsoft Excel (Exceltip.com)
• PrintOut Method (Excel Help Forum)
• Changing printers in Excel (VBA Express)

C. Moreira, enviou-me uma folha de cálculo, na qual pretendia o seguinte:

Alterar o mês em A1 e o valor em B1. Se o mês for Janeiro, Fevereiro ou Março, as células D1, D2 e D3 assumem o valor de B1, caso contrário ficam com os valores que apresentam.

Apresentou então o exemplo:

E forneceu a solução, ou seja, com a utilização da chamada "referência circular".

Vejamos, então, como foi feito:

Em D1, digitou: =IF(A1="Janeiro";B1;D1)

Em D2: =IF(A1="Fevereiro";B1;D2)

Em D3: =IF(A1="Março";B1;D3)

Depois, fez o seguinte:

Como se pode ver, sempre que em A1 se coloque o mês, o valor transfere-se para a coluna D, para a célula que corresponde ao mês digitado.

  Comentário:

O único problema da referência circular é que, se for aberto outro workbook antes deste, aparece, por defeito, o facto de não haver referência circular. Logo, quando se abre o 2º workbook (que tem a tal referência circular) vai aparecer sempre uma mensagem de aviso...

Claro que há uma maneira de resolver o problema e fazer com que a referência circular fique por defeito, mesmo que se abra outro qualquer workbook primeiro: é colocar a seguinte macro no próprio workbook:

Private Sub Workbook_Open()
    Application.Iteration = True
End Sub

Deste modo, sempre que o workbook seja aberto, vai ler, à abertura, a macro acima e, assim, activa referência circular.

Tópicos relacionados:

• Circular References (Decision Model)



• Change formula recalculation, iteration, or precision (Microsoft Office On-line)



• Utilizar iterações e Alteração máxima em cálculos (Microsoft Portugal)



• Circular References In Excel (I Get It)



• Microsoft Excel > Auditing > Circular References (BetterSolutions)

 Clicar no banner

O TechDays está a chegar!

11-14 de Março 2008 - Centro de Congressos de Lisboa

Assim, teremos que digitar, na célula B1 fa Folha 1, a seguinte fórmula: =VLOOKUP(A1;Sheet2!$A$1:$B$10;2)

=IF(B1="";"";IF(RIGHT(B1;1)=1;"";IF(VLOOKUP($A$1;Sheet2!$A$1:$B$10;2)=$B$1;$B$1-1)))

=IF(B2="";"";IF(VALUE(RIGHT(B2;1))=1;"";IF(VLOOKUP($A$1;Sheet2!$A$1:$B$10;2)=$B$1;$B$1-VALUE(RIGHT(B2;1)))))

Tópicos relacionados:

• Learning VLOOKUP in Excel

• VLOOKUP Function

• Excel RIGHT Function (Ted French)

• Value Function

Carlos Rondão, criou vários jogos em ambiente Excel.

Um deles é o famoso TETRIS:

Podem baixá-lo aqui

Do mesmo autor, um jogo tipo SLOT VIDEO MACHINE:

Podem baixá-lo aqui