You are here: Home » VBA » SBA.gov Web Service API Methods – Licenses

SBA.gov Web Service API Methods – Licenses

business license

From Business.Gov:

SBA.gov's Web Service API provides methods for obtaining small business resources and geographic data used by SBA.gov's core search tools including its award-winning state and local search engine, loans and grants search, and licenses and permits search.

Using SBA.gov's API, software developers can build new applications and mashups using authoritative information from Federal, state and local government agencies. The API is free of charge, does not require registration, and allows for unlimited calls.

The SBA.gov API is RESTful. Output formats are available in either XML or JSON. Each web service begins with a base URL followed by parameters and arguments. Parameters and arguments are separated by a forward slash ("/").

Following are sample methods for consuming SBA.gov API data in your VBA programs.

Business Licenses & Permits API

Like many of the other web API code samples found on this site (see Wordnik API, New York State Legislature API and Geonames API), these functions use XMLHTTP to fetch and cache a web query, then parse it for information using the MSXML Object Model, which is returned to the calling procedure.

You'll also need to include the code from the Enum Section and the Helper Functions in order for these functions to work.

Return Business Licenses By Category

The following function returns all business license information in all U.S. states and territories (54 in all). Depending on the license category chosen, the function returns

  • doing business as – Requirements for registering a fictitious business name or "doing business as" in all 54 states and territories
  • entity filing – Requirements for registering a business' legal structure (e.g. LLC, Corporation, etc.)in all 54 states and territories
  • employer requirements – Registration and filing requirements for employers in all 54 states and territories
  • state licenses – Business licenses required by state governments for each business type in the Licenses & Permits database
  • tax registration – Requirements registration and permit requirements for 54 states and territories
Function GetLicensesByCategory(licenseCategory As licenseCategory) As String()
' http://www.sba.gov/content/business-licenses-permits-api-category-method

Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim cat As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByCategory"
Const XML_FILE_EXTENSION As String = ".xml"

  cat = GetLicenseCategory(licenseCategory)

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & cat & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    ' URL encode the string because license categories have spaces
    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/by_category/" & URLEncode(cat) & _
    XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByCategory = tempString

End Function

Don't forget the URLEncode helper function in order to make sure the license category is passed properly to the API.

Because the API can return data in multiple node groups, I had to loop through the nodes twice. The first time, we count the number of data points so that the main array can be sized properly. The second time, data in each node group is put into an array, then combined into the larger main array. I'm sure there is a simpler way to solve this problem but that's how I did it.

Return Business License Information By State

We can filter the above results to return license information by a given state. This function "returns all business licenses for all business types required to operate in an specific state or territory."

The technique is much the same as above — create small arrays and roll them up into a larger array.

Function GetLicensesByState(stateAbbr As stateAbbr) As String()
' http://www.sba.gov/content/business-licenses-permits-api-state-method

Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim state As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByState"
Const XML_FILE_EXTENSION As String = ".xml"

  state = GetStateAbbr(stateAbbr)

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & state & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/all_by_state/" & state & _
    XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByState = tempString

End Function

Business Licenses By Business Type

We can also filter business licenses by business type. The following types are supported:

  • General Business Licenses
  • Auto Dealership
  • Barber Shop
  • Beauty Salon
  • Child Care Services
  • Construction Contractor
  • Debt Collection Agency
  • Electrician
  • Massage Therapist
  • Plumber
  • Restaurant
  • Insurance Requirements
  • New Hire Reporting Requirements
  • State Tax Registration
  • Workplace Poster Requirements

Calling this function with any of the above parameters returns business license information about that business type.

Function GetLicensesByBusinessType(business As businessType) As String()
' http://www.sba.gov/content/business-licenses-permits-api-business-type-method
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim businessName As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByBusinessType"
Const XML_FILE_EXTENSION As String = ".xml"

  businessName = GetBusinessName(business)

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & businessName & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/by_business_type/" & _
    URLEncode(businessName) & XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByBusinessType = tempString

End Function

Filter Business Licenses By State And Type

As above, we can filter business licenses by state and business type to return only business license information from a given state and type.

Function GetLicensesByBusinessTypeAndState(business As businessType, _
    stateAbbr As stateAbbr) As String()
' http://www.sba.gov/content/business-licenses-permits-api-business-type-and-state-method
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim businessName As String
Dim state As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByBusinessTypeAndState"
Const XML_FILE_EXTENSION As String = ".xml"

  businessName = GetBusinessName(business)
  state = GetStateAbbr(stateAbbr)

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & businessName & state & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/state_only/" & URLEncode(businessName) & _
    "/" & state & XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByBusinessTypeAndState = tempString

End Function

Filter Business Licenses by State, Type and County

We can filter even further by limiting business license information to a given type, state and county. This function "returns business licenses and permits required for a specific type of business in a specific state and county." Apologies in advance for the very long function name.

Function GetLicensesByBusinessTypeStateAndCounty(business As businessType, _
    stateAbbr As stateAbbr, county As String) As String()
' http://www.sba.gov/content/business-licenses-permits-api-business-type-state-and-county-method
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim businessName As String
Dim state As String
Dim countyName As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByBusinessTypeStateAndCounty"
Const XML_FILE_EXTENSION As String = ".xml"

  businessName = GetBusinessName(business)
  state = GetStateAbbr(stateAbbr)

  ' at some point I'll update this to check the web for
  ' the correct county name
  countyName = county

  If Right$(countyName, 6) <> "County" Then
    countyName = countyName & " County"
  End If

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & businessName & _
    state & countyName & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/state_and_county/" & URLEncode(businessName) & _
    "/" & state & "/" & URLEncode(countyName) & XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByBusinessTypeStateAndCounty = tempString

End Function

Filter Business License Information By Type, State and City

The following function "returns business licenses and permits required for a specific type of business in a specific state and city." See above for business types.

Currently there's no way to validate the city/state combination (other than spell check?). I'll add that eventually.

Function GetLicensesByBusinessTypeStateAndCity(business As businessType, _
    stateAbbr As stateAbbr, city As String) As String()
' http://www.sba.gov/content/business-licenses-permits-api-business-type-state-and-city-method
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim businessName As String
Dim state As String
Dim cityName As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByBusinessTypeStateAndCity"
Const XML_FILE_EXTENSION As String = ".xml"

  businessName = GetBusinessName(business)
  state = GetStateAbbr(stateAbbr)

  ' at some point I'll update this to check the web
  ' to make sure that the city and state match
  cityName = city

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & businessName & _
    state & cityName & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/state_and_city/" & URLEncode(businessName) & _
    "/" & state & "/" & URLEncode(cityName) & XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByBusinessTypeStateAndCity = tempString

End Function

Filter Business Licenses By Business Type and Zip Code

This function "returns business licenses and permits required for a specific type of business in a specific zip code." In this function there is no validation on the zip code.

Function GetLicensesByBusinessTypeAndZip(business As businessType, _
    zipCode As String) As String()
' http://www.sba.gov/content/business-licenses-permits-api-business-type-and-zipcode-method
Dim xml As Object ' MSXML2.XMLHTTP
Dim tempFile As String
Dim tempString() As String
Dim tempCountySites() As String
Dim tempLocalSites() As String
Dim tempStateSites() As String
Dim tempSitesForBusinessType() As String
Dim tempSitesForCategory() As String
Dim result As String
Dim xmlDoc As Object ' MSXML2.DOMDocument
Dim xmlDocRoot As Object ' MSXML2.IXMLDOMNode
Dim results As Object ' MSXML2.IXMLDOMNodeList
Dim sites As Object ' MSXML2.IXMLDOMNode
Dim subNodes As Object ' MSXML2.IXMLDOMNodeList
Dim site As Object ' MSXML2.IXMLDOMNode
Dim numRows As Long, numCols As Long
Dim i As Long, j As Long, k As Long, h As Long, g As Long
Dim businessName As String
Dim maxArraySize As Long
Dim maxColumns As Long
Dim nextArrayPosition As Long

Const TEMP_FILENAME As String = "LicensesByBusinessTypeAndZip"
Const XML_FILE_EXTENSION As String = ".xml"

  businessName = GetBusinessName(business)

  ' if XML file exists, don't requery website
  tempFile = Environ("temp") & "\" & TEMP_FILENAME & businessName & zipCode & XML_FILE_EXTENSION

  If Len(Dir(tempFile)) = 0 Then

    Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

    With xml
      .Open "GET", _
    "http://api.sba.gov/license_permit/by_zip/" & URLEncode(businessName) & _
    "/" & zipCode & XML_FILE_EXTENSION, False
      .Send
    End With

    result = ConvertAccent(xml.responseText)

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

  With xmlDoc
    .async = False
    .validateOnParse = False
    .Load tempFile
  End With

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)

  ' get first level child nodes
  Set results = GetChildNodes(xmlDocRoot)

  ' get array max size and max number of columns
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    ' number of columns in the array is the largest number of nodes in all node sets
    ' if node set has zero nodes, ignore
    If sites.childNodes.Length > 0 Then
      ' assumes all subnodes are the same length
      maxColumns = Application.Max(sites.childNodes.item(0).childNodes.Length, maxColumns)

      ' number of rows in the array is the sum of all available nodes
      maxArraySize = maxArraySize + sites.childNodes.Length
    End If
  Next i

  ' resize array
  ReDim tempString(1 To maxArraySize, 1 To maxColumns)

  ' create separate arrays for each node set
  For i = 1 To results.Length
    Set sites = results.item(i - 1)

    Select Case sites.nodeName

      Case "county_sites"
        ' grab loans and put into temp array
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempCountySites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempCountySites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempCountySites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempCountySites(h, g)
            Next g
          Next h

        End If
      Case "local_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempLocalSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempLocalSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempLocalSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempLocalSites(h, g)
            Next g
          Next h

        End If
      Case "state_sites"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempStateSites(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempStateSites(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempStateSites)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempStateSites(h, g)
            Next g
          Next h

        End If
      Case "sites_for_business_type"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForBusinessType(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForBusinessType(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForBusinessType)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForBusinessType(h, g)
            Next g
          Next h

        End If
      Case "sites_for_category"
        Set subNodes = GetChildNodes(sites)
        If subNodes.Length > 0 Then
          ReDim tempSitesForCategory(1 To subNodes.Length, 1 To maxColumns)

          For j = 1 To subNodes.Length
            Set site = subNodes.item(j - 1)

            For k = 1 To site.childNodes.Length
              tempSitesForCategory(j, k) = site.childNodes(k - 1).nodeTypedValue
            Next k
          Next j

          ' populate main array with values from temp array
          ' calculate next empty position in main array
          nextArrayPosition = GetEmptyArrayPosition(tempString)
          For h = 1 To UBound(tempSitesForCategory)
            For g = 1 To maxColumns
              tempString(h + (nextArrayPosition - 1), g) = tempSitesForCategory(h, g)
            Next g
          Next h

        End If

    End Select

  Next i

  GetLicensesByBusinessTypeAndZip = tempString

End Function

Sample Usage

The following sample procedure calls all of the above functions and prints their results to the Immediate Window.

Sub TestBusinessGov()

Dim results() As String
Dim i As Long, j As Long

    results = GetLicensesByCategory(tax_registration)
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByState(ARIZONA)
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByBusinessType(Restaurant)
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByBusinessTypeAndState(Barber_Shop, ARIZONA)
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByBusinessTypeStateAndCounty(Auto_Dealership, CALIFORNIA, "Los Angeles")
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByBusinessTypeStateAndCity(Auto_Dealership, NEW_YORK, "New York")
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
    results = GetLicensesByBusinessTypeAndZip(Child_Care_Services, "11103")
    For i = LBound(results) To UBound(results)
      For j = LBound(results, 2) To UBound(results, 2)
        Debug.Print results(i, j)
      Next j
    Next i
End Sub

Helper Functions

These functions should be pasted into a standard module in the same project as the functions listed above. You'll also need to grab the URLEncode function.

Function GetBusinessName(business As businessType) As String
  Select Case business
    Case 0: GetBusinessName = "General Business Licenses"
    Case 1: GetBusinessName = "Auto Dealership"
    Case 2: GetBusinessName = "Barber Shop"
    Case 3: GetBusinessName = "Beauty Salon"
    Case 4: GetBusinessName = "Child Care Services"
    Case 5: GetBusinessName = "Construction Contractor"
    Case 6: GetBusinessName = "Debt Collection Agency"
    Case 7: GetBusinessName = "Electrician"
    Case 8: GetBusinessName = "Massage Therapist"
    Case 9: GetBusinessName = "Plumber"
    Case 10: GetBusinessName = "Restaurant"
    Case 11: GetBusinessName = "Insurance Requirements"
    Case 12: GetBusinessName = "New Hire Reporting Requirements"
    Case 13: GetBusinessName = "State Tax Registration"
    Case 14: GetBusinessName = "Workplace Poster Requirements"
  End Select
End Function
Function GetLicenseCategory(licenseCategory As licenseCategory) As String
  Select Case licenseCategory
    Case 0
      GetLicenseCategory = "doing business as"
    Case 1
      GetLicenseCategory = "entity filing"
    Case 2
      GetLicenseCategory = "employer requirements"
    Case 3
      GetLicenseCategory = "state licenses"
    Case 4
      GetLicenseCategory = "tax registration"
  End Select
End Function

Function GetSpecialty(specialty As specialty) As String
  Select Case specialty
    Case 0: GetSpecialty = "general_purpose"
    Case 1: GetSpecialty = "development"
    Case 2: GetSpecialty = "exporting"
    Case 3: GetSpecialty = "contractor"
    Case 4: GetSpecialty = "green"
    Case 5: GetSpecialty = "military"
    Case 6: GetSpecialty = "minority"
    Case 7: GetSpecialty = "woman"
    Case 8: GetSpecialty = "disabled"
    Case 9: GetSpecialty = "rural"
    Case 10: GetSpecialty = "disaster"
  End Select
End Function
Function GetIndustryType(industry As industryType) As String
  Select Case industry
    Case 0: GetIndustryType = "Agriculture"
    Case 1: GetIndustryType = "Child Care"
    Case 2: GetIndustryType = "Environmental Management"
    Case 3: GetIndustryType = "Health Care"
    Case 4: GetIndustryType = "Manufacturing"
    Case 5: GetIndustryType = "Technology"
    Case 6: GetIndustryType = "Tourism"
  End Select
End Function
Function GetStateAbbr(stateAbbr As stateAbbr) As String
  Select Case stateAbbr
    Case 0: GetStateAbbr = "AL"
    Case 1: GetStateAbbr = "AK"
    Case 2: GetStateAbbr = "AS"
    Case 3: GetStateAbbr = "AZ"
    Case 4: GetStateAbbr = "AR"
    Case 5: GetStateAbbr = "CA"
    Case 6: GetStateAbbr = "CO"
    Case 7: GetStateAbbr = "CT"
    Case 8: GetStateAbbr = "DE"
    Case 9: GetStateAbbr = "DC"
    Case 10: GetStateAbbr = "FM"
    Case 11: GetStateAbbr = "FL"
    Case 12: GetStateAbbr = "GA"
    Case 13: GetStateAbbr = "GU"
    Case 14: GetStateAbbr = "HI"
    Case 15: GetStateAbbr = "ID"
    Case 16: GetStateAbbr = "IL"
    Case 17: GetStateAbbr = "IN"
    Case 18: GetStateAbbr = "IA"
    Case 19: GetStateAbbr = "KS"
    Case 20: GetStateAbbr = "KY"
    Case 21: GetStateAbbr = "LA"
    Case 22: GetStateAbbr = "ME"
    Case 23: GetStateAbbr = "MH"
    Case 24: GetStateAbbr = "MD"
    Case 25: GetStateAbbr = "MA"
    Case 26: GetStateAbbr = "MI"
    Case 27: GetStateAbbr = "MN"
    Case 28: GetStateAbbr = "MS"
    Case 29: GetStateAbbr = "MO"
    Case 30: GetStateAbbr = "MT"
    Case 31: GetStateAbbr = "NE"
    Case 32: GetStateAbbr = "NV"
    Case 33: GetStateAbbr = "NH"
    Case 34: GetStateAbbr = "NJ"
    Case 35: GetStateAbbr = "NM"
    Case 36: GetStateAbbr = "NY"
    Case 37: GetStateAbbr = "NC"
    Case 38: GetStateAbbr = "ND"
    Case 39: GetStateAbbr = "MP"
    Case 40: GetStateAbbr = "OH"
    Case 41: GetStateAbbr = "OK"
    Case 42: GetStateAbbr = "OR"
    Case 43: GetStateAbbr = "PW"
    Case 44: GetStateAbbr = "PA"
    Case 45: GetStateAbbr = "PR"
    Case 46: GetStateAbbr = "RI"
    Case 47: GetStateAbbr = "SC"
    Case 48: GetStateAbbr = "SD"
    Case 49: GetStateAbbr = "TN"
    Case 50: GetStateAbbr = "TX"
    Case 51: GetStateAbbr = "UT"
    Case 52: GetStateAbbr = "VT"
    Case 53: GetStateAbbr = "VI"
    Case 54: GetStateAbbr = "VA"
    Case 55: GetStateAbbr = "WA"
    Case 56: GetStateAbbr = "WV"
    Case 57: GetStateAbbr = "WI"
    Case 58: GetStateAbbr = "WY"
  End Select

End Function

Function GetEmptyArrayPosition(arr() As String) As Long
' pass in 2D String array, returns first empty position

Dim i As Long
Dim tempString As String

  ' loop through first position of array
  ' until empty row is found
  Do
    i = i + 1
    tempString = arr(i, 1)
    If Len(tempString) = 0 Then
      GetEmptyArrayPosition = i
      Exit Function
    End If
  Loop Until i = UBound(arr)

End Function

The following MSXML helper functions should also be placed in the same project.

Enum Sections

The following Enums should be pasted into a standard module in the same project as the SBA.gov API methods. They are used by some/all of the functions above.

Public Enum businessType
  General_Business_Licenses
  Auto_Dealership
  Barber_Shop
  Beauty_Salon
  Child_Care_Services
  Construction_Contractor
  Debt_Collection_Agency
  Electrician
  Massage_Therapist
  Plumber
  Restaurant
  Insurance_Requirements
  New_Hire_Reporting_Requirements
  State_Tax_Registration
  Workplace_Poster_Requirements
End Enum

Public Enum licenseCategory
  doing_business_as
  entity_filing
  employer_requirements
  state_licenses
  tax_registration
End Enum

Public Enum specialty
  general_purpose
  development
  exporting
  contractor
  green
  military
  minority
  woman
  disabled
  rural
  disaster
End Enum

Public Enum industryType
  Agriculture
  Child_Care
  Environmental_Management
  Health_Care
  Manufacturing
  Technology
  Tourism
End Enum

Public Enum stateAbbr
  ALABAMA
  ALASKA
  AMERICAN_SAMOA
  ARIZONA
  ARKANSAS
  CALIFORNIA
  COLORADO
  CONNECTICUT
  DELAWARE
  DISTRICTOFCOLUMBIA
  MICRONESIA
  FLORIDA
  GEORGIA
  GUAM
  HAWAII
  IDAHO
  ILLINOIS
  INDIANA
  IOWA
  KANSAS
  KENTUCKY
  LOUISIANA
  MAINE
  MARSHALL_ISLANDS
  MARYLAND
  MASSACHUSETTS
  MICHIGAN
  MINNESOTA
  MISSISSIPPI
  MISSOURI
  MONTANA
  NEBRASKA
  NEVADA
  NEW_HAMPSHIRE
  NEW_JERSEY
  NEW_MEXICO
  NEW_YORK
  NORTH_CAROLINA
  NORTH_DAKOTA
  NORTHERN_MARIANA_ISLANDS
  OHIO
  OKLAHOMA
  OREGON
  PALAU
  PENNSYLVANIA
  PUERTO_RICO
  RHODE_ISLAND
  SOUTH_CAROLINA
  SOUTH_DAKOTA
  TENNESSEE
  TEXAS
  UTAH
  VERMONT
  VIRGIN_ISLANDS
  VIRGINIA
  WASHINGTON
  WEST_VIRGINIA
  WISCONSIN
  WYOMING
End Enum

Site last updated: May 17, 2012

Peltier Tech Chart Utilities for Excel Peltier Tech Waterfall Chart Utility Peltier Tech Box and Whisker Chart Utility Peltier Tech Cluster-Stack Chart Utility Peltier Tech Panel Chart Utility Peltier Tech Marimekko Chart Utility Peltier Tech Dot Plot Utility Peltier Tech Cascade Chart Utility