I am actually seeing almost the exact same problem.
I have an Excel XP .xls file that I can open with Excel 2007 in compatibility mode, add a simple function to do a linear interpolation of a table, and everything works. I save the file, either as an Excel 97 compatible workbook or an Excel 2007 Macro-Enabled workbook, and the function that I added no longer shows up in the Function Wizard, and all of the cells using the function show #NAME?.
I have set the Macro Security so that all Macros are enabled, and it has no effect.
The function I am trying to use is:
Public Function Linterp(Tbl As Range, x As Double) As Variant
' linear interpolator / extrapolator
' Tbl is a two-column range containing known x, known y, sorted x ascending
Dim nRow As Long
Dim iLo As Long, iHi As Long
nRow = Tbl.Rows.Count
If nRow < 2 Or Tbl.Columns.Count < 2 Then
Linterp = CVErr(xlErrValue)
Exit Function '--------------------------------------------------------
End If
If x < Tbl(1, 1) Then ' x < xmin, extrapolate from first two entries
iLo = 1
iHi = 2
ElseIf x Tbl(nRow, 1) Then ' x xmax, extrapolate from last two entries
iLo = nRow - 1
iHi = nRow
Else
iLo = Application.Match(x, Application.Index(Tbl, 0, 1), 1)
If Tbl(iLo, 1) = x Then ' x is exact from table
Linterp = Tbl(iLo, 2)
Exit Function '----------------------------------------------------
Else ' x is between tabulated values, interpolate
iHi = iLo + 1
End If
End If
Linterp = Tbl(iLo, 2) + (Tbl(iHi, 2) - Tbl(iLo, 2)) _
* (x - Tbl(iLo, 1)) / (Tbl(iHi, 1) - Tbl(iLo, 1))
End Function
On Wednesday, June 04, 2008 6:45 AM Rober wrote:
I am running Excel 2007 on Windows Vista using Parallels Desktop for MAC, as
the excel version for MACs computer does not support VBA functionality (my
MAC is a 2.8GHz Intel Core Duo machine running MAC OS 10.5.3 - the type of
processor which would allow me to create a vitural IBM PC and run all MS
applications as if I were on an actual IBM PC).
When using prior versions of Excel (from ealier version of Microsoft's OS) I
was happily able to create and use user defined functions I had created in
VBA.
Now it seems as though when I create even the simplest of functions:
Option Explicit
Function doll(st As Single) As Single
Dim rate As Single
rate = 1.5
doll = st * rate
End Function
Excel 2007 does not recognize this and in the cell in which I am attempting
to call it all I get is the infamous #NAME? error message (for which scarce
useful information is provided).
I am wondering if there is some switch I am not setting correctly or
something else I just haven't configured. The new version of excel is
diferent enough that do not where any of these settings may be.
Any help provided would be greatly appreciated.
On Wednesday, June 04, 2008 6:55 AM Norman Jones wrote:
Hi Robert,
Your UDF is located in a standard module
of the active workbook?
---
Regards.
Norman
On Wednesday, June 04, 2008 7:11 AM Rober wrote:
Hi Norman,
Yes, I placed it both in the active worksheet and then in a module of the
active workbook. Neither method seemed to work.
Regards,
Robert
"Norman Jones" wrote:
On Wednesday, June 04, 2008 7:25 AM Norman Jones wrote:
Hi Robert,
===========
Yes, I placed it both in the active worksheet and then in a module of the
active workbook. Neither method seemed to work.
===========
If by 'active worksheet', you mean the code
module behind the worksheet, normally this
should house only worksheet event code and
your UDF code should be deleted from that
module.
Does your UDF appear in the list of user
functions in the 'Insert function' wizard?
---
Regards.
Norman
"Robert" wrote in message
...
On Wednesday, June 04, 2008 7:26 AM Bernard Liengme wrote:
Open the VBA editor using ALT+F11
Use the VBEditor command Insert | Module
Type your function in the large window that opens
Have a look he
David McRitchie's site on "getting started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Tell us you got it to work
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Robert" wrote in message
...
On Wednesday, June 04, 2008 8:49 AM Rober wrote:
Norman,
Hi. Sorry, I wasn't clear in my last reply. When I said I placed the code
in both places, I did not mean that I had them in both places simultaneously,
just that I had tried using the function when I had p[laced them in each
place, one at a time. It resides in my "Module" folder, where user defined
functions should be placed (it is not that I am completely new to VBA, just
that I am encountering a problem having my user defined functions not be
recognized.
Just so that I did not think I was going nuts, I had recorded a macro and
sure enough the code for the macro showed up in the same place.
In doing so, I have found the source of the problem.
Excel had a security setting which disabled macros (don't ask me how - as I
mentioned earlier, there may have been a setting I did not know about/could
not find) - an error message showed up informing me to re-open the file and
select "enable macros". Once I had done this, the sheet worked as expected
and my UDF worked fine.
Funny thing is that it had not asked me about this earlier.
Many thanks again for your time and effort in assisting me.
Regards,
Robert
"Norman Jones" wrote:
On Wednesday, June 04, 2008 8:51 AM Rober wrote:
Bernard,
Thanks for your kind reply.
Many thanks for the resource. It wasn't that I didn't know how to program
in VBA as much as the new version of excel 2007 on windows vista is different
enough from the environment I am used to working in, that something just
wasn't working as I was expecting. I have posted a reply to Norman, another
user who was providing existence.
The problem seems to have been that excel's security settings were set to
not allow macros to be run, but why it did not inform me of this when I had
first written the UDF, I have no idea.
Many thanks for your time and effort in providing me with assistance.
Regards,
Robert
"Bernard Liengme" wrote:
On Wednesday, June 04, 2008 9:05 AM Bob Phillips wrote:
Was it a 2003 workbook that you opened in 2007? If so, upgrade it to a 2007
xlsm and it should be okay.
--
HTH
Bob
(there is no email, no snail mail, but somewhere should be gmail in my addy)
On Wednesday, June 04, 2008 9:57 AM Rober wrote:
Bob,
Thanks for your reply - my question was even more basic, as VBA code (see
simple UDF in my original posting) wasn't working when written in excel 2007.
This issue however is sorted.
Many thanks.
Regards,
Robert
"Bob Phillips" wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Workflow Custom Input Forms
http://www.eggheadcafe.com/tutorials...put-forms.aspx