title: Calling Access VBA function from Excel
body: <p>I am building a compensation worksheet.  The tables and key information is in an access database, <code>Compensation.accdb</code>, which has a function to figure out the compensation based on the agent's contract. If the agent is a "REP" the Access VBA function does one computation and if the agent is a SRP it does another. Several separate users want to call this function from Excel (btw, MS Office 2013). The idea is a user changes or inputs the quantity in their spreadsheet and the spreadsheet calls the <code>Compensation.accdb</code> VBA function.  <code>Compensation.accdb</code> does the computation, and passes the value back to the originating spreadsheet.  </p>
  
  <p>Here is the <code>Compensation.accdb</code> VBA code:</p>
  
  <pre><code>Option Compare Database
  
  Function AutoComp(WritingRepContract As String) As Integer 'Auto insurance
      'Debug.Print WritingRepContract
      If WritingRepContract = "REP" Then
          AutoComp = 1 'Compensation formula for Rep will go here
      End If
  
      If WritingRepContract = "SRP" Then
          AutoComp = 2 'Compensation formula for Senior Rep will go here, etc.
      End If
  
  End Function
  </code></pre>
  
  <p>Here is the Excel VBA code:</p>
  
  <pre><code>Public Sub Worksheet_Change(ByVal Target As Range) 
      'Is there a better way than this???
      Dim WritingLevel As String
      If Target.Address = "$B$8" Then 
      'This is the cell where one would enter Auto value.
          If Target.Value = 1 Then 'just a test to see if working
              WritingLevel = "REP"
          Else
              WritingLevel = "SRP"
          End If
          CallAutoComp (WritingLevel)
      End If
  
  End Sub
  
  Sub CallAutoComp(WritingLevel)
      Dim appAccess As Access.Application
      Dim test As Integer
  
      Set appAccess = New Access.Application
      Debug.Print appAccess.Eval("AutoComp(WritingLevel)") 
      'A test to see what is happening
      With appAccess
          .OpenCurrentDatabase "C:\Mypath\Compensation.ACCDB"
          .Visible = False ' Useful for debugging when true
          .Eval ("AutoComp(WritingLevel)")
      End With
      test = appAccess.Eval("AutoComp(WritingLevel)")
      With appAccess
          .CloseCurrentDatabase
          .Quit
      End With
  
  End Sub
  </code></pre>
  
  <p>When I change the value in B8 I get this:</p>
  
  <blockquote>
    <p>Run-time error 2425: The expression you entered has a function name
    that Microsoft Access can't find.</p>
  </blockquote>
  
  <p>It does not like the following statements (same 2425 error for all three statements):</p>
  
  <pre><code>Debug.Print statement
  .Eval ("AutoComp(WritingLevel))") statement
  test = appAccess.Eval("AutoComp(WritingLevel)")
  </code></pre>
  
  <p>I have tried several version of single and double quotes and <code>"&amp;"</code> surrounding <code>WritingLevel</code> with no success.</p>
  
  <p>I am not sure if this is the best approach. This is the first time I have tried to cross platforms like this, but it would be a great help to a lot of people if I could get an answer to this problem. It would be great if I could just call the function in an easier way, such as one does for internal Excel functions. I have searched for days with different versions of my search string with no success.  </p>
  
  <p>When I replace <code>AutoComp(WritingLevel)</code> with <code>AutoComp(SRP)</code> or <code>AutoComp(REP)</code> it works fine.</p>