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" <(E-Mail Removed)> wrote in message
>>>> news:77D6D7F8-39D0-4FCC-8776-(E-Mail Removed)...
>>>>> 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 here:
>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>> news:BDD3154D-5FFF-43CD-89A9-(E-Mail Removed)...
>>>>>> 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
Similar Threads
