Formatar texto numérico com zeros à esquerda

Sempre aparace uma necessidade de colocar zeros à esquerda em textos que representam números.

Exemplo clássico é transformar o número que represente um mês, em forma de string, com o zero antes.

Tipo: 
Dim sMes AS String
Dim sAno AS STring

sAno = "" & YEAR( sAno )

sMes =  "" & Month( NOW() )      ' Resultará em 2019 1, enquanto que o desejável seria 201901.

Quase todo mundo tem uma função que faz isso. Eis a minha:



Function strZero(iNumero As Variant, iTam As Integer) As String

 Dim strTemp As String

  strTemp = String(iTam, "0")

 strZero = Right(strTemp & Trim(iNumero), iTam)

End Function

'
' Exemplo
'

Dim sMes AS String
Dim sAno AS STring

sAno = "" & YEAR( sAno )

sMes =  strZero(  Month( NOW() ), 2)   

' sMes = 201901.

OU

msgbox  "" & YEAR( now() ) & strZero(  Month( NOW() ), 2)   

Exemplo de uso: 

VBA: Pegar a última linha preenchida

Muitas vezes precisamos escrever em uma planilha, linha a linha, sou seja, quando for colocar um novo conteúdo na planilha, precisamos que o mesmo ocorra uma linha após a última linha preenchida.

Para isso, precisamos saber qual  a última linha?

Uso a função fUltimaLinha para isso. Sintaxe:

wsPlanilha  = Planilha em que se deseja obter a última linha
iColuna       = Número da coluna que será avaliada

Retorna um valor LONG. ( mais de 32 mil linhas ).

Exemplo:

DIM iUltimaLinha AS LONG

iUltimaLinha = fUltimaLinha( Sheets("PlanilhaDestino"), 1 ) + 1

sheets("PlanilhaDestino").Cells( iUltimaLinha, 1) = "Teste!"
sheets("PlanilhaDestino").Cells( iUltimaLinha, 2) = "Novo Teste!"


Function fUltimaLinha(wsPlanilha As Worksheet, iColuna As Integer) As Long
   Dim iLinha As Long
 
   iLinha = wsPlanilha.Cells(Rows.Count(), iColuna).END(xlUp).Row
   fUltimaLinha = iLinha
End Function

Função para limpar uma planilha, via VBA

Quando trabalhamos com uma planilha que receberá novo conteúdo, muitas vezes precisamos apagar o conteúdo atual da mesma. Há diversas formas de fzer isso. Criei essa função genérica para limpar qualquer planilha.


Ela recebe como parâmetros:
wsDestino = Planilha que se deseja limpar
Exemplo de uso:
LimpaPlanilha Sheets("Buffer_1"), "A2", "F"
Neste caso, vai limpar uma planilha chamada Buffer_1, a partir da célula A2 até a célula F. Para saber a última linha e fechar o Range em F, verifico qual é a última linha preenchida observando o conteúdo da coluna A.


sCelulaInicial = Célula a partir da qual iniciaremos a limpeza. Passar no formato "A3", ou seja, endereço de célula baseado em Letra + Número da linha
scolunaFinal  = Letra da coluna final para formar um RANGE que se deseja limpar. Passar somente a letra da coluna

Sub LimpaPlanilha(wsDestino As Worksheet, sCelulaInicial As String, sColunaFinal As String)

Dim wsPlanAtual As Worksheet

Dim bAtualUpate As Boolean

'

' Preserva o status de screenpudating atual

'

bAtualUpate = Application.ScreenUpdating

'

' Preserva a planilha atual

'

Set wsPlanAtual = ActiveSheet

'

' Desliga o screenupdating.

'

Application.ScreenUpdating = False

'

' Ativa a planilha que será "limpa"

'

wsDestino.Activate

'

' Monta o RANGE que será "limpo"

'

wsDestino.Range("" & sCelulaInicial & ":" & sColunaFinal & wsDestino.Cells(Rows.Count, 1).END(xlUp).Row).Select

'

' Apaga o conteúdo

'

Selection.Clear

'

' Reativa a planilha antes da chamada da função

'

wsPlanAtual.Activate

'

' Restaura o ScreenUpdating anterior à chamada da função

'

Application.ScreenUpdating = bAtualUpate

'

' Libera memória com o conteúdo da variável de apoio

'

Set wsPlanAtual = Nothing

End Sub

Solicitar ao usuário um nome e local para gravar um arquivo, em VBA

Uma função muito utilizada por mim é a que eu solicito um nome/local de arquivo para o usuário. Em VBA, isso é fácil:

Uso sTexto para mandar uma mensagem para o usuário. E uso sTipo para saber se é para Ler um arquivo ou é para Gravar, ou seja, se passar Ler, o arquivo já deverá existir no diretório apontado pelo usuário. Se passar Gravar, o usuário deverá informar o nome/local do arquivo.

Na prática, posso usar assim:

Dim sArquivo As String
 
  sArquivo = fRetornaArquivo("Informe a base de dados", "Ler")
 
  If sArquivo <> "" Then
     exit sub
  end if
  ...

Function fRetornaArquivo(sTexto As String, sTipo As String) As String
'
' Abre o arquivo
'
Dim CancelProcedure As Integer
Dim FileToOpenTxt As String

CancelProcedure = MsgBox(sTexto, vbOKCancel)

If CancelProcedure = 2 Then
   GoTo Suspende:
End If

If sTipo = "Ler" Then
   FileToOpenTxt = Application.GetOpenFilename("Planilhas (*.*), *.*")
Else
   FileToOpenTxt = Application.GetSaveAsFilename("Planilhas (*.*), *.*")
End If

fRetornaArquivo = FileToOpenTxt

Suspende:
   Exit Function

End Function

Executando uma macro a cada 'n' minutos.

Recentemente tive necessidade de utilizar um meio de executar uma rotina no Excel a cada 5 segundos. Fuçando, cheguei no Application.OnTime.

Para um exemplo simples, vou utilizar um botão para marcar o início da atividade e outro botão para marcar o fim.

Quando clicar em Iniciar, quero que uma rotina, chamada AtualizarHora seja executada a cada 3 segundos.

Clicando em Parar, quero que a rotina deixe de ser executada.

Eis os códigos:
'
' Utilizaremos uma variável pública para controlar a execução da rotina desejada.
'
Public bExecutar As Boolean

Sub Botão1_Clique()
'
' Iniciar contagem de tempo gravando a hora atual em B2. Como vamos iniciar, apagamos qualquer
' vestígio que tenha na C3 e D3
Cells(2, 2) = Time()
Cells(3, 2).Clear
Cells(4, 2).Clear
'
' Libera a execução
'
bExecutar = True
'
' Aqui é onde programamos o Excel para executar nossa rotina
'
Application.OnTime EarliestTime:=Now + TimeValue("00:00:03"), Procedure:="AtualizaHora"
End Sub



Sub AtualizaHora()
'
' Se execução estiver liberada, reprograma a rotina
'
If bExecutar Then
   Application.OnTime EarliestTime:=Now + TimeValue("00:00:03"), Procedure:="AtualizaHora"
End If

Cells(3, 2) = Time()                                       ' Grava a hora atual
Cells(4, 2) = Cells(3, 2) - Cells(2, 2)             ' Apresenta quantidade de tempo já decorrido
End Sub

Sub Botão2_Clique()
'
' Parar contagem
'
bExecutar = False                                         ' Ajusta variável de controle para falso
'
' Reprograma a rotina para ser executada imediatamente
'
Application.OnTime EarliestTime:=Now, Procedure:="AtualizaHora"

End Sub

Executar uma rotina ao abrir uma planilha do Excel

Segue uma dica, capturada na internet, para executar uma rotina ao abrir uma pasta de trabalho do Excel (workbook): 

Public Sub Workbook_Open()

  Call ROTINA_XXXX

End Sub

Respondendo ao Max - Performance em planilha Excel

Recentemente um amigo, Max, me questionou quanto à problemas de performance no Excel. Qual a capacidade máxima? Qual o tamanho, em MB, máximo para uma planilha?

Fuçando no mundo da web e na minha experiência, o problema de tamanho x performance de uma planilha, embora relacionados, leva-nos a uma dedução:

O tamanho de uma planilha está limitado à capacidade de memória RAM disponível e aos recursos do computador.

Apesar de, no 2007 e 2010, o Excel suportar 1.048.576 linhas por 16.384 colunas, não será somente o tamanho ou o número de linhas que irá trazer problemas de performance, mas, adicionalmente, a quantidade de cálculos que uma planilha possui.
Isso porque, a cada enter em uma célula, o Excel dispara um evento que irá provocar a execução de todas as fórmulas existentes e, enquanto estas não forem concluídas, o Excel irá ficar aguardando.

Geralmente, em grandes planilhas, a melhor dica é limpar a memória de programas que podem ser desligados e tirar o cálculo automático de fórmulas. Isso é feito indo em Arquivo/Opções/Fórmulas/Opções dé Cálculo e marcar em Manual.

Quando desejar atualizar os cálculos, basta pressionar F9.

Claro que acresentar tabelas dinâmicas, gráficos, links para outros workbooks, implicará em mais penalizações na performance.

Definido bordas no Excel, via VBA

Mais uma: para definir as bordas de um conjunto de células, com o VBA.

Nesse exemplo, marcamos uma região e colocamos as bordas externas.

Range("B10:I15").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Excel: Ordenando células via VBA

Como ordernar uma faixa de célua, usando VBA?

Digamos que temos uma planilha com dados de fornecedores e não sabemos qual é a linha final. Uma vez importado, queremos realizar uma ordenção, pelo nome do fornecedor, que está na coluna 3

Dim wbAtual AS Workbooks
Dim iLinhaF AS integer

set wbAtual = ActiveWorkBook

iLinhaF = wbAtual.WorkSheets("Fornecedores").Cells( Rows.Count, 3).End( xlUp).Row

wbAtual.WorkSheets("Fornecedores").Range("A2:F" & iLinhaF).Select

Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, Matchcase:= False, Orientation:=xlTopBottom


Pronto!

Redirecionando páginas em PHP

Ao tentar redirecionar uma página em php para outra, me deparei com um erro do tipo: Cannot modify header information - headers already sent by"

A solução que eu encontrei na WEB e compartilho com todos é:

Bem, estou começando com o PHP e, para ajudar a outros que como eu, tem a web como ajuda, resolvi publicar essa solução.

Gravando fórmulas via VBA em células

Essa dica de hoje me valeu a manhã de trabalho. Estou montando uma planilha que calcula horas-extras. Criei uma rotina em que lê os dados de uma planilha e sai escrevendo-os em outra planilha, totalizando-os ao final, de acordo com o departamento.

No final, me deparei com um erro no seguinte código:
pQuadro.Cells(C16).Formula = "=soma( C" & iLinhaInicio & ":C" & iLinhaAtual - 1 & ")"

O desejo aqui é que ele grave na célula C16 a fórmula: =SOMA(C3:C15) e efetivamente calcule esses valores.

O problema é que o conteúdo de C16 está aparecendo justamente =SOMA(C3:C15) e não o resultado dessa fórmula. Se eu pressiono F2 e enter, aí sim, ele calcula.

Depois de muito navegar, a solução que encontrei ( desculpe-me, perdi o site) é simples!
Basta substituir SOMA por SUM, pois o Excel utiliza fórmulas EXCEL em português, mas o VBA usa fórmulas em INGLÊS. É substituir e funcionar!

Tão simples, mas tão útil, que resolvi postar!