Propósito

✔ Programação GLOBAL® - Quaisquer soluções e/ou desenvolvimento de aplicações pessoais, ou da empresa, que não constem neste Blog devem ser tratados como consultoria freelance. Queiram contatar-nos: brazilsalesforceeffectiveness@gmail.com | ESTE BLOG NÃO SE RESPONSABILIZA POR QUAISQUER DANOS PROVENIENTES DO USO DOS CÓDIGOS AQUI POSTADOS EM APLICAÇÕES PESSOAIS OU DE TERCEIROS.

.: Vitrine

Carregando artigos...

Views

Mostrando postagens com marcador reference. Mostrar todas as postagens
Mostrando postagens com marcador reference. Mostrar todas as postagens

Excel Tips - Funções de Busca e Referência - Lookup and reference functions

Inline image 1

Para utilizarmos bem as funções do MS Excel precisamos ao menos saber que existem e conhecermos onde as podemos aplicar.

O que segue são algumas destas que podemos utilizar extensivamente, agora com um pouco mais de conhecimento.

Tenham em mente que elas estão expressas em inglês, escolhi assim para que pudéssemos ter proveito delas tanto funcionalmente no MS Excel quanto na programação VBA. Sei que na maioria das empresas a instalação está em português, o que pessoalmente acho péssimo, nestes casos poderá procurar pela referência de como escrevê-la em Português.

Ahh e não reclamem, aproveitem para usar o Google Translate se precisarem.

Funções de Busca e Referência


ADDRESS
Returns a reference as text to a single cell in a worksheet

AREAS
Returns the number of areas in a reference

CHOOSE
Chooses a value from a list of values

COLUMN
Returns the column number of a reference

COLUMNS
Returns the number of columns in a reference

HLOOKUP
Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX
Uses an index to choose a value from a reference or array

INDIRECT
Returns a reference indicated by a text value

LOOKUP
Looks up values in a vector or array

MATCH
Looks up values in a reference or array

OFFSET
Returns a reference offset from a given reference

ROW
Returns the row number of a reference

ROWS
Returns the number of rows in a reference

RTD
Retrieves real-time data from a program that supports COM automation

TRANSPOSE
Returns the transpose of an array

VLOOKUP
Looks in the first column of an array and moves across the row to return the value of a cell



Tags: Function, Excel, Tips, functions, Busca, Referência, Lookup, reference

Nenhum comentário:
Tags: , , , , , , ,

VBA Excel | Usando a API do Google Maps

VBA Excel - Usando a API do Google Maps para retornar a distância em Km entre dois lugares - Google Maps distance function
Qual pode ser a utilidade de uma função que faça isso, retorne a distância em quilômetros, entre 2 pontos geográficos?

Este post pode ser uma ferramenta interessante para acompanhar a distância percorrida pelos materiais de construção da fonte até o local de destino, permitindo calcular o custo do trajeto;

Se você estiver engajado em algum processo ambiental, também poderá utilizar essa funcionalidade para calcular o impacto ambiental em carbono por tonelada km;

Enfim, poderá usar a sua criatividade para torná-la funcional.

Considere este post como sua primeira incursão no mundo Google, fazendo com que a nossa planilha MS Excel retorne informações a partir da API do GMAPS (Google Maps).
Já lhes adianto: Antes que desejem submeter listas enormes de consulta ao servidor GMAPS. Somente são permitidas 2.500 pesquisas ao servidor num período de 24 horas.

A função personalizada para o MS Excel trabalha passando dois nomes de lugar - ou códigos postais - e encontra a rota mais curta entre eles usando o planejador de rotas do Gmaps . Chamaremos de Km_Distance e a sintaxe para usá-la será:

= Km_Distance (origem, destino)

Ela retornará um valor da distância entre os dois locais em quilômetros e se qualquer tipo de erro ocorrer então a função retorna 0.
Duas nota de advertência:
1) Os termos de uso do GMaps determinam que só estão autorizados a usar a sua API com o seu mapa. Assim, caso deseje fazê-lo sem seguir tais especificações de uso da API, faça-o por sua conta e risco (por isso demonstro o mapa acima).
2) O Gmaps geralmente lhe dará uma rota com base num caminho que pode não ser o mais adequado ao seu propósito. Caso descubra um modo de acertarmos isso, por favor entre em contato para melhorarmos este post.

 Divirta-se:


Function Km_Distance (Origin As String, Destination As String) As Double
    ' Requires a reference to Microsoft XML, v6.0
    ' Draws on the stackoverflow answer at bit.ly/parseXML
    
    Dim myRequest As XMLHTTP60
    Dim myDomDoc As DOMDocument60
    Dim distanceNode As IXMLDOMNode
    
    Let Km_Distance = 0
    
    ' Check and clean inputs
    On Error GoTo exitRoute
    
    Let Origin = Replace(Origin, " ", "%20")
    Let Destination = Replace(Destination, " ", "%20")
    
    ' Lendo os dados XML da API do Google Maps.
    Set myRequest = New XMLHTTP60
    
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    
    ' Tornando o XML legível por usar o XPath
    Set myDomDoc = New DOMDocument60
    
    myDomDoc.LoadXML myRequest.responseText
    
    ' Obtendo o valor da distância entre os nós.
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then Km_Distance = distanceNode.Text / 1000

exitRoute:
    ' Tidy up
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

Para que utilize este código com sucesso, certifique-se de colocar o código acima num módulo da planilha.

Defina a referência a biblioteca Microsoft XML, v6.0 - Ferramentas / Referências / Microsoft XML, v6.0

 Ahhh, e sim, esta função pode ser utilizada com qualquer produto da suíte MS Office.


Leia também:

eBook: Série DONUT PROJECT 2015: Projetos e Códigos de Visual Basic for Applications - Autor: André Luiz Bernardes

eBook: Série Top 10 Funções: Top 10 Funções VBA para o Microsoft Excel - Autor: André Luiz Bernardes

eBook: Série Funções Poderosas: 13 Funções Poderosas no MS Excel - Autor: André Luiz Bernardes

eBook: Série Visual Basic For Application: Criando Logs de acesso: Dicas e Códigos de Visual Basic for Applications - Autor: André Luiz Bernardes

eBook: Série VBA Tips: Rastrei seus Dashboards, Scorecards, Reports, Relatórios, Planilhas e Aplicações - Dicas e Códigos - Autor: André Luiz Bernardes

eBook: Série Data Science: Big Data, Como? - Autor: André Luiz Bernardes

eBook: Série Smarter Analytic: 5 Previsões de Big Data - Autor: André Luiz Bernardes


Comente e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

✔ Brazil SFE®✔ Brazil SFE®´s Facebook´s Profile Google+  Author´s Professional Profile  ✔ Brazil SFE®´s Pinterest       ✔ Brazil SFE®´s Tweets
9 comentários:
Tags: , , , , , , , , , , ,

VBA Excel - 00.10 - Referenciando-se as planilhas - Workbooks



Modos de referenciar-se a um Worbook:



Tags: VBA, Excel, Series, reference, referenciando, workbook, worksheet


Nenhum comentário:
Tags: , , , , , , , , , , , ,

VBA Excel - 01.10 - Referenciando planilha - Active - Workbook ativo



Vamos relembrar um artigo antigo, agora revisado. Veremos 10 formas para referenciar workbooks e worksheets usando o VBA no MS Excel.

workbooks = Arquivo que contém todas as planilha em diversas pastas.

worksheets = Planilhas individuais, contidas nas abas.

1 - Active - Referenciando o workbook ativo

A propriedade do ActiveWorkbook faz referência ao workbook que tem o foco. 

Digamos que após atualizar a informação num workbook ativo, provavelmente deseje salvá-lo, esta é uma tarefa simples para a propriedade do ActiveWorkbook. A SUB a seguir utilizará a propriedade ActiveWorkbook  para fechar o workbook ativo:

Sub CloseActiveWBSemSalvar()
  ' Fecha o workbook ativo sem salvar.

  ActiveWorkbook.Close False
End Sub

Sub CloseActiveWBSalvando()
  'Fecha o workbook ativo e o salva.

  ActiveWorkbook.Close True
End Sub

Sub CloseActiveWBEscolheSeSalva()
  'Fecha o workbook ativo escolhendo se deseja salvar.
  'Deixa o usuário decidir se deseja salvar ou não.

  ActiveWorkbook.Close
End Sub

Sim, o exemplo foi apenas elucidativo, pode-se facilmente combinar estes três estado em uma única função passando o modo como a ação será executada através de parâmetros.

Abaixo segue outro exemplo onde o nome, o caminho são atribuidos:

Function GetActiveWBPathName() As String

  Let GetActiveWBPathName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

End Function
Nenhum comentário:
Tags: , , , , , , , ,

VBA Excel - 02.10 - Referenciando planilha - Currently - Workbook executando o código



É sempre bom saber que:

workbooks = Arquivo que contém todas as planilha em diversas pastas.

worksheets = Planilhas individuais, contidas nas abas.

2 - Currently - Workbook executando o código

A propriedade para o ThisWorkbook é similar a propriedade ActiveWorkbook, mas...
ActiveWorkbook avaliará o workbook com foco, ThisWorkbook se referirá ao workbook que estiver rodando no momento.

Esforce-se para distinguir estes dois momentos. Sim este aparente pormenor acrescenta grande flexibilidade uma vez que o workbook ativo nem sempre é o workbook que está rodando o código.

Function GetThisWB() As String

              Let GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name

End Function

A Figura abaixo mostra o resultado da execução do procedimento:


Nenhum comentário:
Tags: , , , , , , , ,

VBA Excel - 03.10 - Referenciando planilha - Collection - Referenciando a coleção do Workbook



É sempre bom saber que:

workbooks = Arquivo que contém todas as planilha em diversas pastas.

worksheets = Planilhas individuais, contidas nas abas.


3 - Collection - Referenciando Workbooks na coleção

As coleções de Workbooks contém todos os objetos Workbooks que estão abertos. 

Para instanciá-lo, a seguinte SUB populará um listbox num formulário do usuário com os nomes de todos os workbooks abertos:

Private Sub UserForm_Activate()
  'Popula o listbox com os nomes dos workbooks abertos.

  Dim wb As Workbook
  
  For Each wb In Workbooks
    ListBox1.AddItem wb.Name
  Next wb

 End Sub

O FORM resultante é mostrado na Figura abaixo. perceba a listagem de todos os workbooks abertos. Ao utilizar a referência à coleção Workbooks, poderá referenciá-los sem "hard-coding", simplesmente o nome do workbook.


Para listar todos os workbooks abertos é uma tarefa fácil; agradeça isso a existência da Workbooks collection. Todavia abrir todos os workbooks numa pasta específica é uma tarefa dura, mas você poderá se beneficiar desta SUB:

Sub OpenAllwb()
  'Abre todos os workbooks numa pasta específica.

  Dim i As Integer

  With Application.FileSearch
  
    Let .LookIn = "C:\A&A"
    Let .FileType = msoFileTypeExcelWorkbooks
       ' Se houver workbooks.

      If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
          Workbooks.Open (.FoundFiles(i))
        Next i
  
      ' Caso não hajam workbooks       Else
        MsgBox "Não existem workbooks para acessar.", vbOKOnly
      End If
  End With
End Sub

Esta tarefa mostra o que pode ser feito com a coleção Workbooks. Neste caso o código não circula através da coleção Workbooks; antes tenta tirar vantagem de um dos métodos da coleção (collection) — especificamente, o método Open (abrir). 

Fechar todos os workbooks abertos é tão fácil como foi abrí-los, aplique a SUB abaixo:

Sub CloseAllWB()
  'Fecha todos workbooks abertos.
  Workbooks.Close
End Sub

Para visualizar mais métodos e propriedades de collection, pressionando F2 no VBE e naveguendo no Object Browser.
Nenhum comentário:
Tags: , , , , , , , , ,
Assinar: Postagens (Atom)
diHITT - Notícias