VBA – Passagem de parâmetros por valor e por referência (ByVal, ByRef)

As estruturas para criação de funções do VBA, Sub e Function possibilitam o envio de informações para uso destas. Estas são infomadas em seu cabeçalho, entre parênteses e logo após o nome da função. São normalmente conhecidas como parâmetros ou argumentos da função.

Exemplo:

Public Sub Soma(x As Long, y As Long)
... código
End Sub

No código acima, uma Function poderia ter sido utilizada no lugar da Sub. As variáveis declaradas no topo da função devem ser informadas em sua chamada. A partir disso, a código interno terá acesso aos valores destas variáveis e fazer uso destes para suas operações.

Existem duas formas de passar valores para funções no VBA. São elas ByVal (por valor) e ByRef (por referência).

Caso a forma não seja informada, o VBA considera por padrão que os valores são passados em modo ByVal. Ou seja, o código acima poderia ser escrito desta forma sem alterar suas funcionalidades:

Public Sub Soma(ByVal x As Long, ByVal y As Long)
... código
 End Sub

Mas o que quer dizer por valor ou por referência?

Na passagem de parâmetro por valor, ao iniciar a execução, a função faz uma cópia dos valores passados para serem utilizados nas operações. Supondo que o valor passado para a função fosse uma variável criada por você. Uma cópia deste valor seria efetuada durante a execução da função, não alterando em nada o valor original de sua variável. Para entender melhor, veja o exemplo de código abaixo:

Public Sub Teste()
'cria a sua variável
 Dim var As Long
'atribui um valor a ela
var = 2
'chama a função Dobro passando a variável para esta
Call Dobro(var)
'mostra o valor da variável após a execução do código
MsgBox var
End Sub
Public Sub Dobro(ByVal x As Long)
x = x * 2
End Sub

A função teste cria uma variável do tipo numérica na linha 3 a atribui o valor ‘2’ a ela na linha 5. Em seguida, é chamada a função valor que recebe o valor da variável var e o multiplica por 2. Em seguida o valor da variável é mostrado na linha 9. Veja o resultado da execução do código:

byvalbyref1

Note que por mais que tenhamos passado o valor por parâmetro para a função Dobro, a variável var permaneceu com seu valor original. Isso aconteceu porque como explicado, ao iniciar sua execução, a função cria uma cópia em memória do valor da variável passado por parâmetro, deixando seu valor original intacto.

Esse problema pode ser facilmente resolvido utilizando uma Function ao invés de uma Sub e retornar o valor no final de final da execução. Mas se precisássemos alterar ou mesmo retornar mais de um valor? É isso que queremos contornar e é nesse ponto que entra o ByRef. Veja o código alterado abaixo:

Public Sub Teste()
'cria a sua variável
 Dim var As Long
'atribui um valor a ela
var = 2
'chama a função Dobro passando a variável para esta
Call Dobro(var)
'mostra o valor da variável após a execução do código
MsgBox var
End Sub
Public Sub Dobro(ByRef x As Long)
x = x * 2
End Sub

Note que alteramos a passagem do parâmetro de ByVal para ByRef. Veja o resultado da execução da função Teste:

byvalbyref2

O que aconteceu desta vez? Quando a declração de uma variável passada como parâmetro de uma função é feita bom ByRef, o VBA ao invés de efetuar uma cópia como o ByVal, faz um referência ou ligação à variável que foi passada. No caso do código acima, a mudança da forma de passagem do parâmetro para ByRef faz com que exista um vínculo entre a variável x na função Dobro e a variável var na função Teste.

Ou seja, qualquer alteração feito no valor x dentro da função será automaticamente refletida na variável var.

Como você pode passar mais de uma parâmetro as funções no VBA, esta se torna uma forma de poder retorna vários valores com uma única função. Veja o código alterado abaixo:

Public Sub Teste()
'cria as variáveis
 Dim var1 As Long
 Dim var2 As Long
 Dim var3 As Long
 'atribui valores
var1 = 1
 var2 = 2
 var3 = 3
 'chama a função Dobro
Call Dobro(var1, var2, var3)
'mostra o valor das variáveis após a execução do código
MsgBox var1 & vbNewLine & var2 & vbNewLine & var3
End Sub
 
Public Sub Dobro(ByRef x As Long, ByRef y As Long, ByRef z As Long)
x = x * 2
y = y * 2
z = z * 2
End Sub

Veja que todos as valores das variáveis foram alterados. Apenas para informação, o operador ‘&’ é utilizado para concatenar textos eo ‘vbNewLine’ causa uma quebra de linha, similar a um Enter no Word.

As formas de passagem de parâmetros podem ser utilizadas tanto para Sub com para Function. No caso da Function, o valor de retorno tradicional não sofre nenhuma alteração. Porém, é bom frisar que parâmetros passados em funções de planilha não sofrem alterações mesmo se passados como ByRef.

Conclusão
Vimos que o VBA disponibiliza dois tipos de passagem de parâmetros para funções, ByVal e ByRef. A forma padrão é ByVal e nela, uma cópia do valor passado por parâmetro é utilizada na execução da função. Com ByRef, uma referência à variável é passada para a função, fazendo com que todas as alterações feitas sejam refletidas na variável original.

Utilizando o recurso de passagem de parâmetros para funções e conhecendo suas forma de trabalhar, é possível resolver a grande maioria de nosso problemas na construção de aplicativos em VBA.

Dicas – Sistema calcula chances de conseguir emprego

Fonte: Dica dada pela MetroPoint

Trabalhador pode consultar seu grau de empregabilidade

Que está em busca de emprego acaba de ganhar uma ferramenta que permite calcular as chances de consguir uma vaga no mercado de trabalho. O sistema, batizado de Termômetro Nacional do Emprego, está disponível no endereço eletrônico www.termometrodoemprego.sp.gov.br.

Para consultar seu grau de empregabilidade, o trabalhador precisa reposder a algumas questões pessoais como idade, sexo, grau de instrução, estado civil e número de filhos, além de informação profissionais, como experiência e renda mensal.

A ferramenta também fornece uma estimativa média de remuneração e calcula o tempo que o trabalhor deverá permanecer no novo emprego.

Acesse o site do Termômetro Nacional do Emprego clicando no link abaixo:

www.termometrodoemprego.sp.gov.br

Termômetro Nacional do Emprego
Termômetro Nacional do Emprego

VBA – Criando ComboBox Encadeados

Outra dúvida muito comum na comunidade VBA. ComboBox Encadeados quer dizer que em dois ou mais ComboBoxes, ao atualizar os dados de um ComboBox, os outros são afetados. Isso é útil quando os dados entre eles estão relacionados, o que quase sempre é verdade.

No exemplo deste post, a relação dos dados será baseada na tradicional Produto e Categoria. A pasta de trabalho deve estar disposta em duas planilhas da seguinte forma:

Planilha de Categorias

Planilha de Produtos

É preciso que os dados da coluna NomeDaCategoria na planilha de Produtos estejam compatíveis com os da planlha Categorias, ou o filtro não funcionará.

Após isso, crie um UserForm com a seguinte “cara”:

Estrutura do Formulário ComboBox Encadeados

Para que o código funcione sem precisar alterar nada, nomeie os campos da seguinte forma:

LabelCategorias
ComboBoxCategorias
LabelProdutos
ComboBoxProdutos

Em seguida, adicione o seguinte código o formulário:

Private Sub ComboBoxCategorias_Change()
    Call CarregaProdutos(Me.ComboBoxCategorias.List(Me.ComboBoxCategorias.ListIndex))
End Sub
 
Private Sub UserForm_Initialize()
    Call CarregaCategorias
End Sub
 
Private Sub CarregaCategorias()
    Dim linha As Integer, coluna As Integer
    linha = 2
    coluna = 1
    Me.ComboBoxCategorias.Clear
    With Sheets("Categorias")
        Do While Not IsEmpty(.Cells(linha, coluna))
            Me.ComboBoxCategorias.AddItem .Cells(linha, coluna).Value
            linha = linha + 1
        Loop
    End With
End Sub
 
Private Sub CarregaProdutos(ByVal Categoria As String)
    Dim linha As Integer, colunaProduto As Integer, colunaCategoria As Integer
    linha = 2
    colunaProduto = 1
    colunaCategoria = 2
    Me.ComboBoxProdutos.Clear
    With Sheets("Produtos")
        Do While Not IsEmpty(.Cells(linha, colunaProduto))
            If .Cells(linha, colunaCategoria).Value = Categoria Then
                Me.ComboBoxProdutos.AddItem .Cells(linha, colunaProduto).Value
            End If
            linha = linha + 1
        Loop
    End With
End Sub

Execute o formulário e selecione uma categoria no primeiro ComboBox. Veja que no ComboBox de produtos são mostrados apenas aqueles que pertecem à categoria selecionada:

ComboBox Encadeados em Funcionamento

Obviamente existem várias formas de fazer o mesmo trabalho. Invente a sua, ou, faça o download do exemplo no link abaixo:
😉

https://www.tomasvasquez.com.br/forum/download/file.php?id=2150

Bom proveito!

VBA – A diferenças entre InputBox e Application.InputBox

Introdução

O VBA disponibiliza além da tradicional função InputBox, um método de nome Application.InputBox. A principal questão é, já que existem duas, qual se deve utilizar?

A principal diferenção entre elas é que, a função InputBox é uma maneira simples de capturar textos introduzidos pelo usuário. Já o método Application.InputBox é capaz de verificar automaticamente o tipo de dados que deve ser inserido na caixa de texto do caixa de diálogo mostrada.

Estrutura do Application.InputBox

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

Prompt

String necessário. A mensagem a ser exibida na caixa de diálogo. Pode ser uma seqüência de caracteres, um número, uma data ou um valor Boolean (o Microsoft Excel converte o valor automaticamente em um String antes de exibi-lo).

Title

Variant opcional. O título da caixa de entrada. Se esse argumento for omitido, o título padrão será “Entrada”.

Default

Variant opcional. Especifica um valor que aparecerá na caixa de texto quando a caixa de diálogo for inicialmente exibida. Se esse argumento for omitido, a caixa de texto será deixada vazia. Esse valor pode ser um objeto Range.

Left

Variant opcional. Especifica uma posição x para a caixa de diálogo em relação ao canto superior esquerdo da tela, em pontos.

Top

Variant opcional. Especifica uma posição y para a caixa de diálogo em relação ao canto superior esquerdo da tela, em pontos.

Help File

Variant opcional. O nome do arquivo de ajuda para essa caixa de entrada. Se os argumentos HelpFile e HelpContextID estiverem presentes, um botão Ajuda aparecerá na caixa de diálogo.

Help ContextId

Variant opcional. O número de identificação do contexto do tópico da Ajuda em HelpFile.

Type

Variant opcional. Especifica o tipo de dados retornado. Se esse argumento for omitido, a caixa de diálogo retornará texto. Pode ser um dos valores abaixo ou a soma deles.

clip_a04[1]

Exemplo

Note que a única diferença desta para a função InputBox, é a existência do último parâmetro, o Type. É este que permite fazer validações de tipos simples do VBA para retorno da função. Veja o código de exemplo:

1
2
3
4
5
Public Sub TesteAppInputBox()
Dim num As Long
num = Application.InputBox("Digite um valor numérico", "Application.InputBox", "Valor numérico", , , , , 1)
MsgBox "O valor digitado foi: " & num
End Sub

No código, os parâmetros Left, Top, HelpFile e HelpContextId foram omitidos por não serem necessários para este exemplo. O importante neste código é o parâmetro Type que foi informado. Conforme a tabela, o valor 1 significa que o Application.InputBox espera que um valor numérico seja inserido pelo usuário. Execute a função e veja o resultado:

clip_d76[1]

Informe um valor de texto qualquer na caixa de entrada e clique em OK para ver o resultado:

clip_28a[1]

Veja que não necessário nenhuma verificação via código VBA. O Applcation.InputBox somente precisa da informação sobre o tipo de dado que deve ser esperado pelo usuário. Informe um valor numérico qualquer e clique novamente em OK:

clip_0e6[1]

Você pode usar a soma dos valores permitidos para Type. Por exemplo, para uma caixa de entrada que possa aceitar tanto texto como números, defina o parâmtro Type como 1 + 2.

Comentários

Use InputBox para exibir uma caixa de diálogo simples para que você possa inserir informações a serem usadas em uma macro. A caixa de diálogo tem um botão OK e um botão Cancelar. Quando você escolhe o botão OK, InputBox retorna o valor inserido na caixa de diálogo. Quando você clica no botão Cancelar, InputBox retorna False.

Quando Type é 0, InputBox retorna a fórmula na forma de texto — por exemplo, “=2*PI()/360”. Se houver quaisquer referências na fórmula, elas serão retornadas como referências de estilo A1. (Use ConvertFormula para fazer a conversão entre estilos de referências).

Quando Type é 8, InputBox retorna um objeto Range. Você precisa usar a instrução Set para atribuir o resultado a um objeto Range, como mostrado no exemplo seguinte.

1
Set meuRange = Application.InputBox(prompt := "Exemplo", type := 8)

Se você não usar a instrução Set, a variável será definida com o valor no intervalo em vez do próprio objeto Range.

Se você usar o método InputBox para pedir uma fórmula ao usuário, você terá que usar a propriedade FormulaLocal para atribuir a fórmula a um objeto Range. A fórmula de entrada deverá estar no idioma do usuário.

O método InputBox difere da função InputBox porque permite validação seletiva da entrada do usuário e aceita objetos, valores de erro e fórmulas do Microsoft Excel. Observe que Application.InputBox chama o método InputBox e InputBox sem qualificador de objeto chama a função InputBox.