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

Answer 1

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

Answer 2

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