VBA Tips - Como converto números para horas?

Como no Access a exibição de horas é limitada (só vai até 23:59), é uma prática comum usar números Single nos cálculos. A exibição nos relatórios, porém, precisa ser no formato de horas.

Para converter um número Single para uma String no formato de horas, a seguinte função pode ser usada:

 '**************************************************

'Funções para se trabalhar com horas acima de 24h

'************************************************** 

Public Function HrStr (dblHora As Double) As String

'Pega um valor numérico e o converte para Horas/Minutos

'Ex: 123,5 = "123:30"

'Ex: 23,9833333333333 = "23:59"

Dim strHoras As String

Dim strMinutos As String

'Pega as horas (parte inteira)

strHoras = CStr(Fix(dblHora))

'Pega os minutos

strMinutos = Format$(Abs((dblHora - Fix(dblHora)) * 60), "00")

'Verifica se o total de minutos é 60

If strMinutos = "60" Then

strMinutos = "00"

strHoras = CStr(CDbl(strHoras) + 1)

End If

'Concatena os dois

HrStr = strHoras & ":" & strMinutos

End Function

Esta outra função faz o contrário: pega um string no formato de horas e converte para número:

Public Function HrDbl(stHora As String) As Double

'Converte um string de hora (formato (h)hh:mm) para Double

'Ex: "135:30" = 135,5

'Ex: "23:59" = 23,9833333333333

Dim dblHoras As Double

Dim intMinutos As Integer

Dim blnDoisPontos As Boolean, blnNum As Boolean

Dim strNum As String

'Verifica se o sinal de dois pontos ':' está na terceira casa

'da direita para esquerda

If Asc(Left(Right(stHora, 3), 1)) = 58 Then

    blnDoisPontos = True

Else

    blnDoisPontos = False

End If

'Verifica se o resto dos dígitos são numéricos

strNum = Left(stHora, Len(stHora) - 3) & Right(stHora, 2)

If IsNumeric(strNum) = True Then

    blnNum = True

Else

    blnNum = False

End If

'Sai do procedimento se o formato estiver incorreto

If (blnDoisPontos = False) Or (blnNum = False) Then

    MsgBox "Informe a hora no formato hh:mm", vbCritical + vbOKOnly

    Exit Function

End If

'Pega os minutos

If CDbl(strNum) < 0 Then

    intMinutos = CInt(Right(strNum, 2)) * (-1)

Else

    intMinutos = CInt(Right(strNum, 2))

End If

'Pega as horas

dblHoras = Fix(CDbl(Left(strNum, Len(strNum) - 2)))

'Calcula a hora

HrDbl

= dblHoras + (intMinutos / 60)

End Function 

Tags: Microsoft Office, VBA, tips, convert, hour, hora, clock, time

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com

VBA Excel: Criando gráficos com VBA

Talvez deseje aprender a criar gráficos automaticamente através de codificação VBA

Considere o seguinte RANGE abaixo:

Copie e cole o código abaixo:

Range("A1:D5").Select

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:D7"), PlotBy:=xlRows

'ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Softwares Vendidos no Quadrimestre"

With ActiveChart

    .HasTitle = True

    .ChartTitle.Characters.Text = "Softwares Vendidos por Qtr 1"

    .Axes(xlCategory, xlPrimary).HasTitle = True

    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Meses"

    .Axes(xlValue, xlPrimary).HasTitle = True

    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vendas"

End With

Tags: Microsoft Office, excel, chart, automation, gráfico

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com

VBA Excel: Criando gráficos com VBA

Talvez deseje aprender a criar gráficos automaticamente através de codificação VBA

Considere o seguinte RANGE abaixo:

Copie e cole o código abaixo:

Range("A1:D5").Select

Charts.Add

ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:D7"), PlotBy:=xlRows

'ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Softwares Vendidos no Quadrimestre"

With ActiveChart

    .HasTitle = True

    .ChartTitle.Characters.Text = "Softwares Vendidos por Qtr 1"

    .Axes(xlCategory, xlPrimary).HasTitle = True

    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Meses"

    .Axes(xlValue, xlPrimary).HasTitle = True

    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Vendas"

End With

Tags: Microsoft Office, excel, chart, automation, gráfico

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com

VBA Excel - Inserindo novas planilhas (worksheets) na pasta.

Planos de Contas, Agrupamentos, lista de diferentes locais, etc...

Por 'n' motivos precisamos criar pastas no MS Office Excel. Como fazê-lo aparentemente de forma automática?

Aqui abaixo está o modo. Simples e funcional.

Boa diversão!

Dim ws As Worksheet                    ' Cria um objeto "worksheet".

Set ws = Sheets.Add                      ' Adiciona uma nova pasta na planilha.

ws.Name = "NewName"                  ' Atribui um nome a pasta.

Tags: Microsoft Office, Excel, insert worksheet

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com

VBA Tips: Extraindo parte do texto - Por delimitação

É muito comum quando estamos efetuando alguma higienização dos dados, querermos retirar parte de um texto contido em algum campo, por exemplo:

Suponha que deseja acertar a sua base de dados de endereço e para isso necessite corrigir os endereços de acordo com a base disponibilizada. alguns endereços contém a string "- - " que indica a numeração de alcance do CEP, informando onde começa e até onde vai a respectiva numeração. Isso não é nada útil no seu cadastro, pois deseja apenas o nome correto da Rua. Aqui surge a pergunta:

Como faço para que retornar apenas a parte do endereço que desejo, ignorando o restante que não necessito?

AVENIDA PRESIDENTE GETÚLIO VARGAS - - de 1182 a 1732 - lado par

AVENIDA PRESIDENTE GETÚLIO VARGAS 

RUA DA JUVENTUDE - - até 678/679

RUA DA JUVENTUDE 

RUA AMARALINA - - de 1391/1392 ao fim

RUA AMARALINA 

RUA MINISTRO ALFREDO BUZAIDE - - de 547/548 ao fim

RUA MINISTRO ALFREDO BUZAIDE 

Supondo que o nome seja ''ENDERECO", a sintaxe seria: 

BetterCompare ([ENDERECO];" -")

Function BetterCompare (nFrase As Variant, nExclude As Variant) As String

    Dim nSize As Variant

    Dim nInitXtract As Variant

    

    If InStr (1, nFrase, nExclude) <> 0 Then

        Let nSize = Len (nFrase)

        Let nInitXtract = InStr (1, nFrase, nExclude)

        Let BetterCompare = Mid (nFrase, 1, nInitXtract)

    Else

        Let BetterCompare = nFrase

    End If

End Function

Tags: VBA, Tip, deduplicação, higienização, correio, endereço

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com

VBA Tips: Retira Acentos diacríticos

Como retiramos os acentos de um texto, ou de uma base de dados a qual desejamos popular, comparar, deduplicar? A função abaixo resolve bem a nossa necessidade:

Function TiraAcento (Palavra)

    Dim cAcento As String

    Dim sAcente As String

    

    Let cAcento = "àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑ"

    Let sAcento = "aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN"

    Let Texto = ""

    

    If Palavra <> "" Then

        For X = 1 To Len(Palavra)

            Let Letra = Mid(Palavra, X, 1)

            Let Pos_Acento = InStr(cAcento, Letra)

            If Pos_Acento > 0 Then

                Let Letra = Mid(sAcento, Pos_Acento, 1)

            End If

            Let Texto = Texto & Letra

        Next

        Let TiraAcento = Texto

    End If

End Function

Referências:

A.M. Kuchling (19 de julho de 2008). Regular Expression HOWTO (em inglês). Documentação do Python. Python Software Foundation. Página visitada em 24 de julho de 2008.

Aurélio Marinho Jargas. Expressões Regulares - Uma Abordagem Divertida. 3 ed. [S.l.]: Novatec, 2009. 208 p. ISBN 978-85-7522-212-6

Ben Forta. Sams Teach Yourself Regular Expressions in 10 Minutes. [S.l.]: Sams. ISBN 0-672-32566-7

DOT NET Framework Regular Expressions (em inglês). .NET Framework Developer's Guide. Microsoft. Página visitada em 25 de julho de 2008.

Francois Liger; Craig McQueen, Paul Wilton. Visual Basic .NET Text Manipulation Handbook. [S.l.]: Wrox Press. ISBN 1-86100-730-2

Jan Goyvaerts (14 de março de 2008). Example: Regular Expression Matching a Valid Date (em inglês). Página visitada em 25 de julho de 2008.

Jeffrey Friedl. Mastering Regular Expressions. [S.l.]: O'Reilly. ISBN 0-596-00289-0

Larry Wall (4 de junho de 2002). Apocalypse 5: Pattern Matching (em inglês).

Larry Wall e o time de desenvolvimento do Perl 5 (2006). perlre: Perl regular expressions (em inglês).

Lesson: Regular Expressions (em inglês). The Java Tutorials. Sun Microsystems. Página visitada em 25 de julho de 2008.

Mehran Habibi. Real World Regular Expressions with Java 1.4. [S.l.]: Springer. ISBN 1-59059-107-0

Michael Sipser. Introduction to the Theory of Computation. [S.l.]: PWS Publishing. 31–90 p. ISBN 0-534-94728-X

POSIX Regex: Introduction (em inglês) (18 de julho de 2008). Página visitada em 24 de julho de 2008.

Referências

Regular Expressions (em inglês). The Open Group (1997). Página visitada em 19 de julho de 2008.

Russ Cox (Janeiro de 2007). Regular Expression Matching Can Be Simple and Fast (em inglês). Página visitada em 20 de julho de 2008.

Tony Stubblebine. Regular Expression Pocket Reference. [S.l.]: O'Reilly. ISBN 0-596-00415-X

a b Perl 5.10.0 documentation (em inglês). Página visitada em 25 de julho de 2008.

Tags: VBA, Tip, deduplicação, higienização, acento, diacrítico, cedilha

André Luiz Bernardes

A&A® - Work smart, not hard.

VBA Blogs

  

 

bernardes@.com