EXCELer

Nos dois posts anteriores, foi mostrado como se podem acrescentar dados a um comentário e como copiar o conteúdo desse comentário para uma nova folha.
Hoje mostro como se podem efectuar alterações num comentário múltiplo, como no exemplo:








O Código (adaptado do apresentado num forum sobre Excel, em 27-01-2005):

Sub AlterarNoComentario()
    Dim temp1 As String, temp2 As String, temp3 As String, temp4 As String
    ians1 = Application.InputBox("Localizar:", "Localizar & Substituir no Comentário ", "")
    ians2 = Application.InputBox("Substituir:", "Localizar & Substituir no Comentário ", "")
    temp3 = ians1
    temp4 = ians2
    For Each cell In Selection
        temp1 = cell.Comment.Text
        On Error Resume Next
        temp2 = Replace(temp1, temp3, temp4)
        cell.Comment.Text Text:=temp2
    Next
End Sub

No seguimento do post anterior, se pretendermos listar o conteúdo de todos os comentários e os respectivos endereços (células) numa nova worksheet:



podemos utilizar uma peça de código que foi mostrada por Debra Dalgleish.

O Código:

Sub ListComms()
    Dim cell As Range
    Dim sh As Worksheet
    Dim csh As Worksheet
    Set csh = ActiveWorkbook.Worksheets.Add
    csh.Name = "Comments"
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> csh.Name Then
            For Each cell In sh.UsedRange
                If Not cell.Comment Is Nothing Then
                    With csh.Range("a65536").End(xlUp).Offset(1, 0)
                        .Value = sh.Name & " " & cell.Address
                        .Offset(0, 1).Value = cell.Comment.Text
                    End With
                End If
            Next cell
        End If
    Next sh
End Sub

Elaborado a partir de uma peça de código apresentada por David Hager em 01/11/1999:







O Código (adaptado):

Public acVal
Public allVal    ' adicionado

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error Resume Next
    Target.AddComment
    Target.Comment.Text "O valor anterior foi: " & acVal & _
       vbLf & allVal ' modificado/adicionado
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If ActiveCell.Address <> Target.Address Then Exit Sub
    If Target.Value = "" Then
        acVal = ""
        allVal = "" ' adicionado
    Else
        allVal = ActiveCell.Comment.Text ' adicionado
        acVal = Target.Value
    End If
End Sub

Se pretendermos somar determinadas ocorrências, como no exemplo (somar as ocorrências "Mário" e "Amadeu"):



podemos utilizar as Funções SUM() e COUNTIF():

=SUM(COUNTIF(A1:A6;{"*Mário*","*Amadeu*"}))

Nota: o asterisco (*) é um wildcard porque os nomes fazem parte de uma string que contém mais caracteres; os nomes são "case sensitive" ou seja, não é indiferente começarem por letra minúscula ou maiúscula.

Como pode ser facilmente observado, se mudarmos o nome da folha, por exemplo, de SHEET1 para TESTE01, nada acontece na célula que contém a função =NomeLF("F"), ou seja, a célula não é actualizada, apesar da alteração efectuada.
A maneira mais prática de "provocar" essa actualização, de um modo mais ou menos automático, é incluir no código a instrução Application.Volatile, porque o Excel não consegue determinar que é necessário efectuar o recálculo para actualizar a folha.

O Código:

Option Explicit

Public Function NomeLF(strChoice As String) As String
    Dim livro As String
    Dim folha As String
    Application.Volatile ' Esta é a nova linha
    If strChoice = "L" Then
        livro = ActiveWorkbook.Name
        NomeLF = livro
    ElseIf strChoice = "F" Then
        folha = ActiveSheet.Name
        NomeLF = folha
    Else
    End If
End Function

Às vezes temos necessidade de colocar numa célula o nome do livro activo ou da folha activa. Para isso, podemos construir uma UDF para cada situação, a que chamaremos NomeLivro() e NomeFolha(), respectivamente:



Os Códigos:

Public Function NomeLivro()
    Dim livro As String
    livro = ActiveWorkbook.Name
    NomeLivro = livro
End Function

Public Function NomeFolha()
Dim folha As String
folha = ActiveSheet.Name
NomeFolha = folha
End Function


No entanto, se formos um pouco mais elaborados, podemos concentrar ambas as situações numa só UDF, a que chamaremos, p. ex., NomeLF(), sendo que que a sintaxe da Função será, para o livro, NomeLF("L") e para a folha, NomeLF("F"):



O Código:

Option Explicit

Public Function NomeLF(strChoice As String) As String
    Dim livro As String
    Dim folha As String
    If strChoice = "L" Then
        livro = ActiveWorkbook.Name
        NomeLF = livro
    ElseIf strChoice = "F" Then
        folha = ActiveSheet.Name
        NomeLF = folha
    Else
    End If
End Function

Normalmente, quando pretendemos colocar um hyperlink numa determinada célula, escrevemos o endereço na mesma:



No entanto, se quisermos personalizar um pouco, podemos escrever o que entendermos e depois efectuar a inserção do endereço:





Como é fácil verificar, o comentário que aparece mostra o endereço.

Contudo, se quisermos que este comentário também esteja personalizado, podemos optar por inserir o hyperlink do seguinte modo:




O resultado será:

No post anterior, mostrei a utilização da função SUMPRODUCT(). Hoje, para o mesmo exemplo, mostro uma outra via para o mesmo resultado, com a utilização da função SUM(), mas utilizando um Array:



Nota: para a obtenção do Array, ou seja, das chavetas que indicam tratar-se de um Array, utilizar as teclas Ctrl + Shift + Enter.
Nota: sobre a Função SUM() utilizado em Array, ver o meu post de 23-01-2005

Se pretendermos "contar" uma determinada ocorrência, mas que obedeça, por exemplo a um critério, podemos utilizar a Função SUMPRODUCT() ou SOMARPRODUTO().
No exemplo, pretendemos contar o número de vezes em que o nome "joão" aparece na coluna A, obedecendo ao critério de lhe corresponder o algarismo 2 na coluna B:

Utilizando o Código do post anterior com uma pequena alteração, podemos adicionar mais um pouco de código para tentar simular um blink entre o negro e o encarnado no conteúdo da Label:








O Código:

Option Explicit


Private Sub UserForm_Initialize()
    Dim dblTotal As Double
    Dim strTotal As String

    strTotal = Worksheets("Sheet1").Range("A7").Value
    dblTotal = Worksheets("Sheet1").Range("B7").Value
    Me.Label1.Caption = strTotal & " " & dblTotal

End Sub



Private Sub UserForm_Activate()
    Dim iCtr As Long
    For iCtr = 1 To 20
        Me.Label1.ForeColor = &HFF&
        Me.Repaint
        Call Sleep(100)
        Me.Label1.ForeColor = &H80000008
        Me.Repaint
        Call Sleep(100)
    Next iCtr
End Sub


ATENÇÃO - Num módulo à parte, adicionar o seguinte Código:

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


O Código para o blink, foi adaptado do Código mostrado por Dave Peterson num Fórum de Discussão sobre Excel