How to return results in different types

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,491
Office Version
  1. 365
Platform
  1. Windows
I may be trying to force a square peg into a round hole here, but...

I am working on a sheet that will calculate how changing the flight path of an aircraft (moving it higher or farther away) will affect the sound on the ground. It currently returns the results in two ways: (1) as a percentage of the sound and (2) as the reduction in decibels (dB). I currently have a UDF that returns a Double that I then format in the table.

This is an example of the % table. The table cells are formatted with [0%_)].
1630264162522.png


And this is an example opf the dB table. These table cells are formatted with [0.0 "dB"_)].
1630264238917.png


The "Table Type" values in L3 & L19 are parameters to the UDF to tell it what kind of a result to return. By changing that one cell, I can change all of the table cells. But I also have to change the custom formatting.

Because the formatting is different for each type of result, I have been using 2 different tables with the appropriate custom formatting patterns. But now I want to add a couple more options: (1) "D2" will cause the UDF to return the new distance to the aircraft, and (2) "D1/D2" will cause the UDF to return the ratio of the old distance to the new distance. Since these require completely different formatting, I will now need two more tables.

I'd like to be able to use a single table. To accomplish that, I thought I would change the UDF to return a String, properly formatted, and change the formatting of the cells to General or Text. A String result should work as there are no plans to do any additional calculations on the results in the cells.

I got partway through that when I realized that I have a problem with the cell in the upper left corner of the table, highlighted in blue: D7 in table 1 and D23 in table 2. This is the initial value based on which all of the other cells are calculated. The UDF is not called from that cell, so it cannot be formatted by the UDF. When I was assigning custom formats to the cells, I could include that cell. Now that the formatting is be3ing done by the UDF, how do I get that one cell formatted in the same way?

Any ideas on how to fit this square peg in that round hole?

I have 2 ideas:

1. I already pass the contents of D7 or D23 to the UDF as a parameter to be used as the base value. What if I move that value to the parameter line above the table and call the UDF from D7 & D23 just like all of the other table cells? Is there a way the UDF can detect that it was called from that one cell and just return the initial value formatted like all the rest?

2. The other two blue cells in the table are also initial values. What if I also move them to the initial values line above the table, which is probably where they belong anyway? Then the UDF can check to see if both the X and Y values are the initial values. If they are, it just returns the base call value properly formatted. This is probably the right way to go.

Any comments?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top