EXCELer

John Walkenbach, um guru do Excel, teve a amabilidade de anunciar este modesto blog no seu mais do que conhecido (e apetecido) J-Walk Blog:



Obrigado, John!

Por vezes, temos necessidade de enviar dados de uma folha do Excel para um determinado documento do Word.

O exemplo a seguir, mostra um botão de comando que, ao ser pressionado, envia os dados do Range A1:B3 para um novo documento do Word.



O Código:

Private Sub CommandButton1_Click()

    Dim MSWord As Object

    Worksheets("sheet1").Range("A1:B3").Copy

    Set MSWord = CreateObject("Word.Application")
    MSWord.Visible = True
    MSWord.ScreenUpdating = True

    With MSWord

        With .Documents.Add
            .Range.Paste
        End With

        .WindowState = wdWindowStateMaximize
    End With
End Sub

A Função que apresento hoje, mostra a data e a hora da última alteração que uma folha teve:




O Código:

Public Function DataHora(s As String) As String
    Application.Volatile

    s = "Última alteração à folha efectuada em: " & Date & "  hora:" & Time

    DataHora = s

End Function

Dave Peterson, deu a conhecer uma UDF (Função definida pelo utilizador) que mostra, numa célula, o conteúdo de um comentário de outra célula.

No exemplo, a Função chama-se GetComment():



O Código:

Public Function GetComment(FCell As Range) As Variant
    Application.Volatile
    
    Set FCell = FCell(1)
    
    If FCell.Comment Is Nothing Then
        GetComment = ""
    Else
        GetComment = FCell.Comment.Text
    End If
    
End Function

Uma outra abordagem para a inclusão de comentários num determinado Range de células: o exemplo tem a ver com a apresentação, no comentário, da data e da hora da modificação do conteúdo da célula, ou seja, sempre que uma célula do Range é alterada, a data e a hora são actualizadas.



O Código:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If InRange(Target, Range("A1:A10")) Then
        With Target
            .ClearComments
        End With

        With Target.AddComment
            .Visible = False
            .Text "Última alteração em: " & Date & "  hora:" & Time
        End With
    End If
End Sub

Function InRange(rng1, rng2) As Boolean
    InRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                InRange = True
            End If
        End If
    End If
End Function

Para inserir o mesmo Comentário em várias células de um determinado Range:





Pode utilizar-se (adaptado ao/aos Ranges pretendidos) o seguinte Código:

Private Sub Worksheet_Change(ByVal Target As Range)

' Macro para criar um Comentário numa célula

Dim rng1 As Range
Dim MyComment
Dim Cell As Range
MyComment = "Comentário:" & Chr(10) & "Isto é um Teste!"

Set rng1 = Intersect(Range("a1:a5"), Target)

    
    If Not rng1 Is Nothing Then

        For Each Cell In rng1.Cells
           Cell.ClearComments
           Cell.AddComment
           Cell.Comment.Visible = False
           Cell.Comment.Text Text:=MyComment
        Next
    Else
        Cancel = True
    End If

End Sub



NOTA: o Código deve ser colocado na própria folha - tecla direita do rato no tabulador da folha e escolher "Ver Código".

Por vezes, quando importamos dados de outros ficheiros ou aplicações, aparecem-nos caracteres indesejáveis, como seja o caracter de parágrafo, ou outro não imprimível.
Uma maneira de apagar este tipo de caracteres, será com a utilização da Função CLEAN() ou, em português, LIMPARB():

Existe um novo Blog sobre Excel e VBA. Chama-se My Excel and VBA Blog

No post anterior, mostrei como se altera a posição do sinal menos (-).

Passo agora a mostrar duas novas fórmulas que, igualmente, para além de efectuarem a mudança de posição do sinal menos (-), transformam o valor/texto em valor/valor e, assim, susceptível de se poder efectuar a soma pretendida:



Fórmula em B1 (com copy e paste para B2:B4):

=IF(ISNUMBER(--A1);--A1;IF(AND(RIGHT(TRIM(A1))="-";ISNUMBER(-SUBSTITUTE(A1;"-";"";1)));-SUBSTITUTE(A1;"-";"");A1))

ou

Fórmula em B1 (com copy e paste para B2:B4):

=IF(ISNUMBER(A1);A1;SUBSTITUTE(A1;"-";"")*-1)

Se pretendermos trocar a posição do sinal menos (-), podemos utilizar a seguinte fórmula, com a combinação das Funções IF(), ISNUMBER() e SUBSTITUTE().

De notar a inclusão do sinal menos (-) antes da Função SUBSITUTE():

Há um novo Blog de Excel em português: ExcelSheet

Para resolver o problema do mês de Janeiro, por forma a não incluir as células em branco pertencentes ao Range, podemos optar por, pelo menos, mais duas fórmulas diferentes, a partir da Função SUMPRODUCT():

No dia 2 de Fevereiro, apresentei um post com uma fórmula que junta as funções Sumproduct()e Text(), para verificar a quantidade de ocorrências contidas num determinado Range.
No entanto, a fórmula apenas funciona correctamente (problema só em relação a "Jan") se o Range estiver completamente preenchido; se houver células em branco, estas são igualmente contadas como se contivessem a ocorrência "Jan", o mesmo não acontecendo com outras ocorrências, como se pode verificar no exemplo seguinte:



A fórmula a executar, para que a contagem das ocorrências seja correcta (não contando, por isso, com as células vazias como fazendo parte da ocorrência), é a seguinte, agora com a incorporação da Função IsBlank():

Em Excel Pragma, foi apresentada uma Função definida pelo utilizador, a qual, porque prática, aqui apresento, a título de exemplo:



O Código:


Public Function ConcSep(InCells As Range, Sep As String) As String
    Dim OutStr As String
    Dim Cell As Variant
    
    OutStr = ""
    If InCells Is Nothing Then Exit Function
    On Error Resume Next
    For Each Cell In InCells
        OutStr = OutStr & Cell.Value & Sep
    Next Cell
    OutStr = Left(OutStr, Len(OutStr) - Len(Sep))
    ConcSep = OutStr
End Function

Se pretendermos preencher uma coluna com a sequência de uma série iniciada na coluna adjacente, digitam-se os dois primeiros números sequenciais na coluna que se pretende preencher:



marcam-se as duas células e no canto inferior direito faz-se um duplo clique.

O resultado é o preenchimento sequencial da coluna, tomando por base a coluna adjacente anterior:



Esta Dica foi retirada de Office Tips and Hints Blog

Se pretendermos ver a quantidade de ocorrências contidas num determinado Range de células (no exemplo, o Range é A1:A5 e está formatado como data na forma dd-mmm e as ocorrências pretendidas são "Jan" e "Fev"), podemos utilizar a seguinte fórmula:

Como é sabido, o Excel apenas dá a possibilidade de incluir 3 formatações condicionais:



Mas, com um pouco de código VBE, é possí­vel incluir muitas mais formatações condicionais. Para tanto, basta clicar com a tecla direita do rato no tabulador da folha:



escolher "Ver Código" e escrever o seguinte:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    With Target
        Select Case LCase(.Value)
        Case Is = "teste1": .Interior.ColorIndex = 5
        Case Is = "teste2": .Interior.ColorIndex = 6
        Case Is = "teste3": .Interior.ColorIndex = 7
        Case Is = "teste4": .Interior.ColorIndex = 8
        Case Is = "teste5": .Interior.ColorIndex = 9
        Case Else
            .Interior.ColorIndex = xlNone
        End Select
    End With
End Sub


O resultado será, por exemplo ( de notar que a formatação condicional, neste caso, abrange toda e qualquer célula da coluna A):