Answered by:
Syntax for Excel Functions

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
Question
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 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/enus/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/enus/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 longwinded 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/enus/library/system.math.exp.aspx
For any arbitrary base B you can use the function System.Math.Pow () http://msdn2.microsoft.com/enus/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 Class
Wednesday, February 21, 2007 3:21 PM