getting vb module formulae to work in excel 2003

[Bob Matthews]

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into Cell
C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You should end
up
' with 7. While there, go ahead and enter 7 into the last cell (to replace
the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in the
' formula bar type =SplineData(A3:B9,C1:C50), and do a control-shift-enter.
The
' first column is a cubic spline interpolation of your data; each subsequent
' column is a higher-order derivative. Of course you dont need to return so
much
' data. If you want the cubic-spline interpolation at a single point, no
problem:
' specify a single point. If you want more information about the behavior
of the
' fitted function evaluated at that point, select up to 4 contiguous cells
in the
' same row, and go for it. If you want to know some info about one of the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using the vb
editor.
When I follow the testing procedure as outline above I end up with #NAME? in
the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Phillips]

Do you have the actual code as well as the comments?

 

[Rick Rothstein]

First question... when you said you "copied and pasted this module into a
new module", did you mean a module that you inserted yourself by clicking
Insert/Module from the VBA menu bar?

 

[Bob Matthews]

Hi Rick
Yes - not expressing myself too well.............
but as you say.....I chose Insert/Module from the VBA menu bar

Bob

First question... when you said you "copied and pasted this module into a
new module", did you mean a module that you inserted yourself by clicking
Insert/Module from the VBA menu bar?

--
Rick (MVP - Excel)

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You should
end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in
the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to return
so much
' data. If you want the cubic-spline interpolation at a single point, no
problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one of
the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using the
vb editor.
When I follow the testing procedure as outline above I end up with #NAME?
in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Matthews]

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You should
end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in
the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to return
so much
' data. If you want the cubic-spline interpolation at a single point, no
problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one of
the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using the
vb editor.
When I follow the testing procedure as outline above I end up with #NAME?
in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Phillips]

I have just gone and grabbed that function, plugged it into a new module,
and after a few corrections (comments being wrapped around in Google), it
worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You should
end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in
the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single point, no
problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one of
the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using
the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Matthews]

Hi Bob

Yes - I got all those wrap around problems sorted

Bob

I have just gone and grabbed that function, plugged it into a new module,
and after a few corrections (comments being wrapped around in Google), it
worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You should
end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in
the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single point,
no problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one of
the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using
the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Phillips]

... and still the problem?

--
__________________________________
HTH

Bob

Hi Bob

Yes - I got all those wrap around problems sorted

Bob

I have just gone and grabbed that function, plugged it into a new module,
and after a few corrections (comments being wrapped around in Google), it
worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence the
length.
' If you "know" that input is always valid, set the compiler directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You
should end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns), in
the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single point,
no problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one of
the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using
the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Matthews]

Yes - afraid so.........

.. and still the problem?

--
__________________________________
HTH

Bob

Hi Bob

Yes - I got all those wrap around problems sorted

Bob

I have just gone and grabbed that function, plugged it into a new module,
and after a few corrections (comments being wrapped around in Google), it
worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence
the length.
' If you "know" that input is always valid, set the compiler
directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You
should end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns),
in the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single point,
no problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one
of the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using
the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Matthews]

Hi Bob

I have sorted out my problems..............

Bob

.. and still the problem?

--
__________________________________
HTH

Bob

Hi Bob

Yes - I got all those wrap around problems sorted

Bob

I have just gone and grabbed that function, plugged it into a new module,
and after a few corrections (comments being wrapped around in Google), it
worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence
the length.
' If you "know" that input is always valid, set the compiler
directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps). Into
Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You
should end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns),
in the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single point,
no problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one
of the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003 using
the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob

 

[Bob Phillips]

Can you divulge the solution ... for posterity?

--
__________________________________
HTH

Bob

Hi Bob

I have sorted out my problems..............

Bob

.. and still the problem?

--
__________________________________
HTH

Bob

Hi Bob

Yes - I got all those wrap around problems sorted

Bob

I have just gone and grabbed that function, plugged it into a new
module, and after a few corrections (comments being wrapped around in
Google), it worked fine.

--
__________________________________
HTH

Bob

Hi Bob

Yes - I have the code

Bob

Do you have the actual code as well as the comments?

--
__________________________________
HTH

Bob

Hi

I am new to this.............so please bear with me

I found the following module that starts off as follows:-
------------------------------------------------------------------------------
Option Explicit
' This module contains routines for cubic spline interpolation and
integration.
' Designed for Microsoft Excel 97 and beyond
' Written 1999/6/30, David J. Braden
' Revisions:
' 1999/7/2 DJB Made 2nd derivative at upper endpoint exact
' Tightened up input validation code
' 1999/7/6 DJB Streamlined (optional) error-checking
' About 20% of the code is for checking that input is valid, hence
the length.
' If you "know" that input is always valid, set the compiler
directive
' fValidateInput = True.
' To test:
' Into cells A3:A9, enter 1,2,3,4,5,6,7, and into B3:B9, enter
3,3,1,2,1,3,3.
' Into cell B1 enter =6/49 (we will go from 1 to 7 in 50 steps).
Into Cell C1,
' enter 1; into C2, enter =C1+$B$1, and drag it down to C50. You
should end up
' with 7. While there, go ahead and enter 7 into the last cell (to
replace the
' proximal value). Select D1:G50 (thats right, 50 rows, 4 columns),
in the
' formula bar type =SplineData(A3:B9,C1:C50), and do a
control-shift-enter. The
' first column is a cubic spline interpolation of your data; each
subsequent
' column is a higher-order derivative. Of course you dont need to
return so much
' data. If you want the cubic-spline interpolation at a single
point, no problem:
' specify a single point. If you want more information about the
behavior of the
' fitted function evaluated at that point, select up to 4 contiguous
cells in the
' same row, and go for it. If you want to know some info about one
of the
' derivatives of such a fit, INDEX is very useful. [etc. etc.]
------------------------------------------------------------------------------
I copied and pasted this module into a new module in excel 2003
using the vb editor.
When I follow the testing procedure as outline above I end up with
#NAME? in the D1:G50 grid

Cleary the function SplineData is not being recognised..........

What do I need to do to get it to work?

Bob