VBA – Ocultando linhas em branco em uma lista no Excel

Fruto da necessidade de alguns colegas do Fórum, coloco aqui uma macro que possibilita ocultar as linhas de uma lista ou tabela no Excel. O efeito pode muito bem ser conseguido através do recurso AutoFiltro, mas, como relatado, algumas vezes usuários menos avançados não conhecem a funcionalidade para produzir o efeito determinado.

Para alguns familiarizados, a rotina é uma adaptação da macro que também existe aqui no blog para excluir linhas de acordo com um critério:

Function OcultaLinhasPorCriterio(ByVal nomePlanilha As String, ByVal linhaInicial As Integer, ByVal linhaFinal As Integer, ByVal colunaCriterio As Integer) As Integer
    Dim linhasOcultadas As Integer
    Dim i As Integer
    linhasOcultadas = 0
    With ActiveWorkbook.Worksheets(nomePlanilha)
        i = linhaInicial
        While i <= linhaFinal
            If IsEmpty(.Cells(i, colunaCriterio).Value) Then
                .Cells(i, colunaCriterio).Rows.Hidden = True
                linhasOcultadas = linhasOcultadas + 1
            End If
            i = i + 1
        Wend
    End With
    OcultaLinhasPorCriterio = linhasOcultadas
End Function

Em míudos:

nomePlanilha é o nome da planilha sobre a qual a macro atuará
linhaInicial é o número da linha pela qual a macro comecará a avaliar.
linhaFinal é o número da linha até a qual a macro fará a avaliação
colunaCriterio é o índice numérico da coluna na qual a macro fará o teste do valor vazio.

A macro abaixo faz a chamada a função apontadando para a panilha de nome “Plan1” avaliando a coluna de índice 5 (E) percorrendo até a última linha ocupada:

Sub OcultarLinhasEmBranco()
    MsgBox OcultaLinhasPorCriterio("Plan1", 2, ActiveSheet.UsedRange.Rows.Count, 5) & " linhas ocultadas"
End Sub

Bom proveito!

VBA – Mostrando uma mensagem durante o cálculo do Excel

A pergunta surgiu em um dós fóruns de Excel do qual participo. Das soluções com as quais me deparei, essa foi a mais interessante e limpa. Siga os seguintes passos:

Na planilha em que será avaliada para cálculo, abra o código VBA da planilha e coloque o seguinte código

Private Sub Worksheet_Calculate()
	UserForm1.Show
End Sub

Crie um novo UserForm chamado UserForm1 e adicione este código ao UserForm:

Private Sub UserForm_Activate()
	Call WaitTimer
End Sub

Crie um novo Módulo VBA e adicione o seguinte código:

Sub WaitTimer()
	If Application.CalculationState <> xlDone Then
		Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
		Procedure:="WaitTimer", Schedule:=True
	Else
		Unload UserForm1
	End If
End Sub

Essas e algumas outras podem ser vistas na seguinte referência:

http://www.eggheadcafe.com/software/aspnet/35770319/vba-message-box-to-inform.aspx

Bom proveito!

VBA – Transferindo os dados de um ListBox para uma Planilha

Por falta de muitos controles no VBA, o ListBox é quase uma unanimidade para fazer a apresentação dos dados de forma tabular. Ele funciona bem, permite adicionar múltiplas colunas, adicionar valores de cabeçalho, mudar o formato da seleção, enfim.

O fato é que, códigos de exemplo para alimentar um listBox com os dados de uma planilha há de monte, mas o contrário nem tanto. Bom, atendendo a pedidos, coloco aqui uma alternativa de código que faz exatamente o trabalho de copiar os dados de um ListBox para uma planilha.

Para não começar totalmente do zero, tomei como base o arquivo do site Ozgrid, disponível neste link:

http://www.ozgrid.com/FreeDownloads/ListBoxTransfer.zip

O código está bem bacana, porém, muito amarrado a planilha que foi produzida, sendo totalmente dependente da sua implementação. Abaixo segue o código com algumas mudanças de tal forma que, possa ser facilmente adaptado para qualquer planilha:

Sub Copiar_ListBox_Para_Planilha(ByVal NomePlanilhaDestino As String, ByRef listBoxControl As MSForms.ListBox)
    Dim iListCount As Integer, iColCount As Integer, iColListCount As Integer
    Dim iRow As Integer
    Dim linhaInicial As Integer
    Dim planilhaDestino As Worksheet
 
    Set planilhaDestino = ThisWorkbook.Worksheets(NomePlanilhaDestino)
    linhaInicial = planilhaDestino.Cells(65536, 2).End(xlUp).Row + 1
 
    If listBoxControl.RowSource <> "" Then
        iColListCount = Range(listBoxControl.RowSource).Columns.Count
    Else
        iColListCount = listBoxControl.ColumnCount - 1
    End If
 
    If listBoxControl.ColumnHeads Then
        iRow = 1
    End If
 
    'faz o loop para todos os itens do ListBox
    For iListCount = 0 To listBoxControl.ListCount - 1
        If listBoxControl.Selected(iListCount) = True Then  'testa se o item está selecionado
            listBoxControl.Selected(iListCount) = False
            iRow = iRow + 1
            'faz o loop pelas colunas
            For iColCount = 0 To iColListCount - 1
                'copia os dados do controle para a planilha
                planilhaDestino.Cells(iRow, iColCount + 1).Value = listBoxControl.List(iListCount, iColCount)
            Next iColCount
        End If
    Next iListCount
 
End Sub

Para fazer a chamada a esse método na mesma planilha disponibilizada, bastaria colocar o seguinte código:

Call Copiar_ListBox_Para_Planilha("Data Transfer Sheet", Me.ListBox1)

Ou seja, ele permite você definir o nome da planilha para qual será copiado o conteúdo e qual o controle ListBox será a fonte dos dados. O código faz várias considerações, prevendo que seus dados sejam baseados em adição manual ou através de RowSource. Copie o código abaixo e faça o teste em um ListBox existente!

Bom proveito!

VBA – Retornando o nome do usuário logado

No VBA, a opção mais trivial para obter o nome do usuário logado na máquina é através de uma chamada a API advapi32.dll.  Nada contra APIs, mas sempre que puder evitar de usá-las, eu prefiro, já que suas chamadas são feitas em baixo nível e requerem um nível de conhecimento bem avançado desta.

Abaixo segue um exemplo que efetua a tarefa sem a necessidade de API no VBA:

'Function que retorna o nome do usuário de rede logado
 
Function UsuarioRede() As String
    Dim GetUserN
    Dim ObjNetwork
    Set ObjNetwork = CreateObject("WScript.Network")
    GetUserN = ObjNetwork.UserName
    UsuarioRede = GetUserN
End Function
 
Sub Mostra()
    MsgBox UsuarioRede
End Sub

O código faz uso do objeto WScript.Network, comumente encontrado nas versões mais atuais do Windows, dispensando referências manuais. Se não me engano, obtive este código em uma edição revista da FórumAccess. Quanto a autoria, ela é do Luiz Cláudio Vieira da Rocha, renomado especialista em Office no Brasil.

Bom proveito a todos!