locked
Syntax for Excel Functions

    Question

  • 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.

    I have loaded the following:

    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.

    Can someone please help me wiith the syntax to do so?

    Thanks again.

    Glenn

    Wednesday, February 21, 2007 1:37 AM

Answers

  • Frank,

    Thanks for your reply, that's some really great information.

    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

    objExcel.Workbooks.Add()

    gea = objExcel.WorksheetFunction.LogInv(0.25, 12, 12)

     MsgBox(gea)

    End Sub

    End Class

    Wednesday, February 21, 2007 3:21 PM

All replies

  •  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 9:10 AM
  • Frank,

    Thanks for your reply, that's some really great information.

    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

    objExcel.Workbooks.Add()

    gea = objExcel.WorksheetFunction.LogInv(0.25, 12, 12)

     MsgBox(gea)

    End Sub

    End Class

    Wednesday, February 21, 2007 3:21 PM