Syntax for Excel Functions

# Syntax for Excel Functions

• Wednesday, February 21, 2007 1:37 AM

Greetings:

Thanks in advance for any help.

I am trying to use VB 2005 Express to access Excel functions that are not in the math area.

Imports Microsoft.Office.Core

Imports Microsoft.Office.Interop.Excel

I am trying to find the inverse log of a number, or actually be able to use any of the Excel functions.

Thanks again.

Glenn

### All Replies

• Wednesday, February 21, 2007 9:10 AM

GAtkins wrote:
 I am trying to use VB 2005 Express to access Excel functions that are not in the math area.

That's not really what the Excel Interop Functions are for.  They are intended to let you manipulate an Excel document programmatically - for example, open the document, insert a new worksheet, fill some cells with data or formulae and then save the document again.  You can get some idea from the sample on this page http://msdn2.microsoft.com/en-us/library/ms247302(VS.80).aspx which creates an Excel application, adds a workbook to it, adds  a worksheet to the workbook and then makes the application visible.

The whole namespace is described here: http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel(VS.80).aspx

I suppose you could manipulate the worksheet to insert your number into one cell and the inverse log function into another cell, recalculate the worksheet and then retrieve the value in the second cell but that seems an awfully long-winded way to get what you want done.

 I am trying to find the inverse log of a number

Remember that the logarithm (for some base b) of a value V is that number L for which V = base raised to the power L.  For example the Log (base 10) of 100 is 2 because 10 squared is 100.  So, given the logarithm L and the base B you can find the inverse logarithm by raising the base B to the power L.

For natural logs (base e) you can use the function System.Math.Exp() http://msdn2.microsoft.com/en-us/library/system.math.exp.aspx

For any arbitrary base B you can use the function System.Math.Pow () http://msdn2.microsoft.com/en-us/library/system.math.pow.aspx

You can even use the second approach for natural logs by using System.Math.Pow along with the constant System.Math.E (but I suspect Exp might give a more accurate answer).

• Wednesday, February 21, 2007 3:21 PM

Frank,

Late last night I got it to work in some test code as follows:

the code returns the value 49.7058 and also makes available all the Excel functions that are not in "math."

Pretty cool stuff.

Thanks again for the derivations above.

Glenn

Imports Microsoft.Office.Core

Imports Microsoft.Office.Interop.Excel

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim objExcel As New Microsoft.Office.Interop.Excel.Application

Dim gea As Double

Dim gea1 As Double

objExcel.Visible = False