How do I create a User Defined Function using VBA in Excel 2007?

[Robert]

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.

 

[Norman Jones]

Hi Robert,

Your UDF is located in a standard module
of the active workbook?

 

[Robert]

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]

Hi Robert,

===========

Your UDF is located in a standard module
of the active workbook?

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?

 

[Bernard Liengme]

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

 

[Robert]

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

 

[Robert]

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

 

[Bob Phillips]

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's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Robert]

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

 

[DougK]

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

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?

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:

 

[Mike S]

Excel 97

Did you try this?

#7: Using the Compatibility Checker

http://blogs.techrepublic.com.com/msoffice/?p=135

Before you send a document that was created with an Office 2007 program
to someone who’s using a previous version of Office, you can run the
Compatibility Checker, which is built into Word, Excel, and PowerPoint
2007. It will identify any features or formatting you’ve used that won’t
be recognized by older versions of Office.

A list of the incompatible content will be displayed, and you’ll be
advised that such content may not be fully editable in the previous
version. The Compatibility Checker runs automatically when you save a
file in the old format. You can also run it manually from the Office |
Prepare menu, as shown in Figure D.

Figure D: You can run the Compatibility Checker from the Office Logo |
Prepare menu.

 

[Mike S]

On 10/22/2010 3:06 PM, DougK wrote:
<snip>

I can save and run this code in an xls file using Excel 2000 (9.0.2720)
so I don't think it's a problem with the code not being incompatible
with older versions of Excel:

Public Sub doit()
Dim sh As Worksheet, rng As Range
Set sh = Application.ActiveWorkbook.Sheets(1)
Set rng = sh.Range("A1", "B2")
MsgBox Linterp(rng, 9.3)
End Sub

data
1 3 1
2 6 3

Public Function Linterp(Tbl As Range, x As Double) As Variant
On Error GoTo LinterpErr
' linear interpolator / extrapolator
' Tbl is a two-column range containing known x, known y, sorted x
ascending
Dim nRow As Long, 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))
Exit Function
'
LinterpErr:
MsgBox "Error in Linterp Function " & Err.Number & " : " &
Err.Description
End Function