Com que frequência obtemos datas em planilhas fornecidas a nós como 12.26.2016/26/12 ou 2016/26/XNUMX (formato do Reino Unido), apenas para saber que a data é inválida ou que não há mês XNUMX? Este artigo explora a fixação de datas com VBA, usando as funções TRIM, LEFT, RIGHT e MID.
O artigo pressupõe que o leitor tenha a faixa Desenvolvedor exibida e esteja familiarizado com o Editor VBA. Se não, por favor, Google “Excel Developer Tab” ou “Excel Code Window”.
O xlsm neste exercício pode ser baixado aqui..
Não é problema nosso!
O melhor lugar para resolver um problema é na fonte. No entanto, nenhuma quantidade de persuasão pode convencer o departamento de folha de pagamento neste caso que 12.26.1994 não é uma data válida (a menos que seja configurado no Painel de Controle do computador para alguns países da Europa Oriental).
Na verdade, podemos provar que não é legível por máquina. Por exemplo, adicionando 7 dias a uma data:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
enquanto…
"=2017-01-01 + 7" = 2017/01/08.
Vamos supor que eles sugiram que não é problema deles.
Formatos de Data
A primeira coisa que temos que verificar é se a data está no formato americano ou internacional.
Nosso exemplo deixa claro que estamos olhando para o uso nos EUA, ou seja, MDY em vez do formato internacional DMY.
Uma vez estabelecida a fonte, precisamos alterar os formatos dos dados para que o Excel possa entendê-los, seja internacionalmente ou nos EUA.
A melhor maneira de fazer isso é alterar a data para aaaammdd, um formato que não precisa de qualificação.
O Processo
Percorreremos cada linha do documento, chamando uma função para “corrigir” a data de acordo com o país de origem. Corrigida a data, calcularemos a idade do Colaborador.
O código
Copie o seguinte código em um novo módulo:
Option Explicit
Sub Main()
Dim strNewFormat As String
Dim strDate As String
Sheets("Main").Range("B4").Select
'Cycle through the sheet rows, using IDNumber as an anchor
'to prevent a premature halt caused by a blank date of birth
Do While ActiveCell > ""
If ActiveCell.Offset(0, 2) > "" Then
strDate = ActiveCell.Offset(0, 2)
'Remove leading or trailing spaces
strDate = Trim(strDate)
'Call the function
strNewFormat = ReformatDate(strDate, "USA")
'Write the result from the function ReformatDate to a new column
ActiveCell.Offset(0, 3) = strNewFormat
'Determine age by subtracting the previous column from today's date
ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
'Convert to intger, thus lopping off decimal places
ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
End If
Range("B" & ActiveCell.Row + 1).Select
Loop
End Sub
Function ReformatDate(sDate As String, sSource As String)
Dim yyyy, mm, dd As String
yyyy = Right(sDate, 4)
If sSource = "USA" Then
mm = Left(sDate, 2)
dd = Mid(sDate, 4, 2)
Else
mm = Mid(sDate, 4, 2)
dd = Left(sDate, 2)
End If
ReformatDate = yyyy & "-" & mm & "-" & dd
End Function
Adicione um botão ao seu formulário e atribua-o ao Sub Main.
Embargo
Antes de adicionar muito código complexo ao seu módulo, lembre-se de que o Excel nem sempre é estável no desenvolvimento de aplicativos importantes e, muitas vezes, não consegue recuperar o próprio código danificado. O resultado pode ser a corrupção de sua única cópia, pois a corrupção ocorre no “Salvar”.
Faça backup com frequência e tenha o uso de uma ferramenta para corrigir Corrupção de arquivos do Excel.
Introdução do autor:
Felix Hooker é um especialista em recuperação de dados em DataNumen, Inc., líder mundial em tecnologias de recuperação de dados, incluindo reparar rar corrupção de arquivo e produtos de software de recuperação SQL. Para mais informações visite www.datanumen.com