New version of Porous Absorber spreadsheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Whealy - 05 May 2005 12:23 GMT

Hi folks

I have finally finished an update to my Porous Absorber spreadsheet. It
now contains an extra sheet called "Microperforated Panel" in which
the absorption curve of a micro-perforated panel is calculated. Please
discard any and all previous versions you may have.

The beauty of this design is that no layer of porous material is
required in order for the panel to function as an absorber. In other
words, you can make a see through absorber using clear perspex or glass!

http://www.whealy.com/acoustics/Porous.html

My initial problem with implementing this calculation, was that I needed
to find the zero'th and first order Bessel function values of complex
numbers, and the Analysis ToolPak functions supplied with Excel only
permit real numbers as parameters. (See Cox & D'Antonio, Eq 6.36, pg 181)

Therefore, I would like to take this opportunity to thank Gordon
Everstine who donated his Fortran implementation of the Bessel function
taking a complex parameter. After some jiggery-pokery with Excel, and
some performance problems caused by the ATP's horribly inefficient data
type conversion, I have reimplemented this code in VBA.

Regards

Chris W

Signature

The voice of ignorance speaks loud and long,
But the words of the wise are quiet and few.
---

Reply to this Message

Hannes Krummheuer - 12 May 2005 22:25 GMT

Hello Chris,

there are versions for Office 95, 97 and 2002 available on your website.
I have Office 2000 (on Win2k) at work and Office 2004 for Macintosh at
home respectively (both are German versions).

Unfortunately, neither the new nor the former versions are functional.
When I open the spreadsheet, a curve is shown. If I change any parameter
(e.g thickness), everything breaks down and all cells just show "#NAME".
Analysis Toolpaks are activated as described on your website.

Do you know why it does not work? The files for Office 95 an 97 should
in my opinion be functional with Office 2000, and Office 2004 for Mac
should be compatible with Office 2002 as well.

Any idea for a workaround?

Best regards,
Hannes

Reply to this Message

Chris Whealy - 13 May 2005 01:00 GMT

> Unfortunately, neither the new nor the former versions are functional.
> When I open the spreadsheet, a curve is shown. If I change any parameter
> (e.g thickness), everything breaks down and all cells just show "#NAME".
> Analysis Toolpaks are activated as described on your website.

I think this error to related to the fact that the names of the
functions in the Analysis ToolPak (ATP) are language specific.

The #NAME! error indicates that a cell contains an unknown function
name. Could you please cause the error to happen again (on any
appropriate sheet) then scroll to the right. In the large table of
calculations, which is the first cell (reading top-to-bottom,
left-to-right) to contain the #NAME! error indicator?

I suspect that it is the first cell to reference an Analysis ToolPak
function. For instance, if you have version 1.5 of the Porous Absorber
spreadsheet, then I suspect that on sheet "Porous Absorber", cells Q10,
Q11 and Q13 will be fine, but cell Q14 will contain #NAME! In column Q,
row 14 is the first cell of that column to call an ATP function.

You can test this by opening an new spreadsheet, and then try to enter
and ATP function into a cell. For instance, =COMPLEX(1.23,4.56,"j").
Do you have to enter a function called COMPLEX() or KOMPLEX(). If yours
is called KOMPLEX(), then the function names in the ATP are definitely
language specific.

On my office installation, the Analysis ToolPak functions live directory
C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis. Of the
files found here, I believe that only ATPVBAEN.XLA is language specific.
I suspect that your German installation has a file called ATPVBADE.XLA
instead of ATPVBAEN.XLA

Therefore, I think the quickest solution would be for you to take a copy
of the English Analysis ToolPak file and place it in the directory
mentioned above. I'm not sure if Excel can work simultaneously with two
language versions of the same Add-in, so it would probably be safer to
take a back up of the entire analysis directory, then copy in the
English files.

Then, the only configuration you will need to do is to check that the
add-in is recognised by Excel. Goto Tools -> Add-ins, and make sure
that the "Analysis ToolPak" is selected. Then you need to open the VBA
editor (press Alt-F11), then select Tools -> References and make sure
that atpvbaen.xls is selected.

I hope that this will both solve the language difference problem, and
not interfere with any other spreadsheets you may be using.

Eric Desart (moderator of the acoustics newsgroup forum.studiotips.com)
is an expert with Excel programming, and he has written a workaround for
solving this language dependency problem. The only issue for me is that
it will take quite a large amount of reworking of the spreadsheet to
implement. Unfortunately, I just don't have time for this at the moment.

You can download the English version of the ATP from here:
http://www.whealy.com/downloads

Tschuess

Chris W

Signature

The voice of ignorance speaks loud and long,
But the words of the wise are quiet and few.
---

Reply to this Message

Natural Science Forum / Physics / Acoustics / May 2005

New version of Porous Absorber spreadsheet