Wednesday, February 21, 2007 1:37 AM
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:
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?
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
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.
ImportsMicrosoft.Office.Interop.Excel Public Class Form1 Inherits System.Windows.Forms.Form
Dim gea As DoubleDim gea1 As Double
objExcel.Workbooks.Add()gea = objExcel.WorksheetFunction.LogInv(0.25, 12, 12)