My Blog

VB.Net to retrieve the names and arguments of all Excel formulas

Another excellent question that I came across in http://stackoverflow.com. Pradeep was kind enough to share a C# version of it in the same thread.

Unfortunately there is no inbuilt method where you can loop through all the Excel formulas and gets it’s name and arguments. So how do we retrieve all that information?

One way to retrieve this info is to parse any online page that has all the list and then retrieve the relevant details. For this example, we will use this link.

To start with, create a form and place 2 command buttons and a textbox as shown below

In the Get Command Button, paste the following code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    TextBox1.Clear()
    Dim th As New Threading.Thread(AddressOf GetFormulas)
    th.Start()
End Sub

…and In the STOP Command Button, paste this code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    TextBox1.Clear()
    Dim th As New Threading.Thread(AddressOf GetFormulas)
    th.Start()
End Sub

And add this procedure as well:

Sub GetFormulas()
    Cancelled = False
    Dim doc As mshtml.HTMLDocument = NewHtmlDoc("http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805551279990&lcid=1033&NS=EXCEL.DEV&Version=12&pid=CH080555125")
    Dim table As mshtml.IHTMLElement2 = DirectCast(DirectCast(doc.getElementById("vstable"), mshtml.IHTMLElement2).getElementsByTagName("table")(0), mshtml.IHTMLElement2)
    Dim links As mshtml.IHTMLElementCollection = table.getElementsByTagName("A")
    For Each link As mshtml.IHTMLElement In links
        If Cancelled Then Exit For
        Dim doc2 As mshtml.HTMLDocument = NewHtmlDoc(link.getAttribute("href").ToString)
        Dim div2 As mshtml.IHTMLElement = doc2.getElementById("m_article")
        For Each elem As mshtml.IHTMLElement In DirectCast(div2, mshtml.IHTMLElement2).getElementsByTagName("P")
            If elem.getAttribute("className") IsNot Nothing AndAlso elem.getAttribute("className").ToString = "signature" Then
                Dim formulaString As String = elem.innerText
                AddText(link.innerText & vbTab & vbTab & formulaString & vbCrLf)
            End If
        Next
    Next
    RaiseEvent FormulaRetrivalCompleted(Me, EventArgs.Empty)
End Sub

Private Function NewHtmlDoc(ByVal url As String) As mshtml.HTMLDocument
    Dim wc As New Net.WebClient
    Dim page As String = wc.DownloadString(url)
    Dim doc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
    doc.write(page)
    doc.close()
    Return DirectCast(doc, mshtml.HTMLDocument)
End Function

Private Sub AddText(ByVal text As String)
    If TextBox1.InvokeRequired Then
        Dim d As New AddTextCallback(AddressOf AddText)
        Me.Invoke(d, text)
    Else
        TextBox1.AppendText(text)
    End If
End Sub

Paste this Form1_FormulaRetrivalCompleted event.

Private Sub Form1_FormulaRetrivalCompleted(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.FormulaRetrivalCompleted
    If Cancelled Then
        MessageBox.Show("Cancelled!", "Processing Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Else
        MessageBox.Show("Processing completed!", "Processing Completed", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
End Sub

So your complete code will look like this:

Option Strict On

Public Class Form1
    Dim Cancelled As Boolean
    Delegate Sub AddTextCallback(ByVal text As String)
    Event FormulaRetrivalCompleted As EventHandler

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        TextBox1.Clear()
        Dim th As New Threading.Thread(AddressOf GetFormulas)
        th.Start()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Cancelled = True
    End Sub

    Sub GetFormulas()
        Cancelled = False
        Dim doc As mshtml.HTMLDocument = NewHtmlDoc("http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805551279990&lcid=1033&NS=EXCEL.DEV&Version=12&pid=CH080555125")
        Dim table As mshtml.IHTMLElement2 = DirectCast(DirectCast(doc.getElementById("vstable"), mshtml.IHTMLElement2).getElementsByTagName("table")(0), mshtml.IHTMLElement2)
        Dim links As mshtml.IHTMLElementCollection = table.getElementsByTagName("A")
        For Each link As mshtml.IHTMLElement In links
            If Cancelled Then Exit For
            Dim doc2 As mshtml.HTMLDocument = NewHtmlDoc(link.getAttribute("href").ToString)
            Dim div2 As mshtml.IHTMLElement = doc2.getElementById("m_article")
            For Each elem As mshtml.IHTMLElement In DirectCast(div2, mshtml.IHTMLElement2).getElementsByTagName("P")
                If elem.getAttribute("className") IsNot Nothing AndAlso elem.getAttribute("className").ToString = "signature" Then
                    Dim formulaString As String = elem.innerText
                    AddText(link.innerText & vbTab & vbTab & formulaString & vbCrLf)
                End If
            Next
        Next
        RaiseEvent FormulaRetrivalCompleted(Me, EventArgs.Empty)
    End Sub

    Private Function NewHtmlDoc(ByVal url As String) As mshtml.HTMLDocument
        Dim wc As New Net.WebClient
        Dim page As String = wc.DownloadString(url)
        Dim doc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
        doc.write(page)
        doc.close()
        Return DirectCast(doc, mshtml.HTMLDocument)
    End Function

    Private Sub AddText(ByVal text As String)
        If TextBox1.InvokeRequired Then
            Dim d As New AddTextCallback(AddressOf AddText)
            Me.Invoke(d, text)
        Else
            TextBox1.AppendText(text)
        End If
    End Sub

    Private Sub Form1_FormulaRetrivalCompleted(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.FormulaRetrivalCompleted
        If Cancelled Then
            MessageBox.Show("Cancelled!", "Processing Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Else
            MessageBox.Show("Processing completed!", "Processing Completed", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub
End Class

Now when you run it, you will start getting the all the relevant details. I am using the Textbox for the output but you can direct the output to a CSV as well.