How to return results in different types

[JenniferMurphy]

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%_)].

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

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?

 

[bobsan42]

3. You can apply the custom number format with code to the whole output table, incl. the top left corner and leave numbers as they are.
Even if you don't do further calculations you may want to produce a graph - you need numbers for this.

 

[JenniferMurphy]

3. You can apply the custom number format with code to the whole output table, incl. the top left corner and leave numbers as they are.
Even if you don't do further calculations you may want to produce a graph - you need numbers for this.

Really? Can you provide some details about how to do that? Do I need to have the table formatted as a Table? I have been meaning to get around to learning how to use Excel tables.

You make a great point about wanting to make a graph.

 

[bobsan42]

No tables needed. It's a property of the Range object or class.

Range("D7:P10").NumberFormat = "0.0"" dB""_ "

 

[JenniferMurphy]

No tables needed. It's a property of the Range object or class.

Range("D7:P10").NumberFormat = "0.0"" dB""_ "

Can I do that from a UDF? I thought I read somewhere that only macros (Subs) can modify cells. UDFs can only return a result.

And since it needs a range, do I name the range and pass that as a parameter?

Thanks, this is encouraging.

 

[bobsan42]

You probably can't do it from within a function, but you can always execute a piece of code (Worksheet_Change) when you're changing/selecting the Table type - this is a must do if you only want to use one table - you will have to change the type and only then you will need number format change.

 

[JenniferMurphy]

You probably can't do it from within a function, but you can always execute a piece of code (Worksheet_Change) when you're changing/selecting the Table type - this is a must do if you only want to use one table - you will have to change the type and only then you will need number format change.

It sounds like my best option is to change it to a Sub and give the table range a set name. Then the Sub can handle anything and it might be faster than calling a function 100 times.

I'll go give that a try.

 

[bobsan42]

If you're lookin to fiil the whole table with values, maybe your final conclusion is correct.
But if you're looking for a formula to produce only a certain value from that table it's good to have a function

 

[JenniferMurphy]

One more question. How can I also change the alignment of the cells in the table? I tried

Range("D7").Justify = xlRight

But I got an error that said I cannot justfy cells that contain numbers.

 

[bobsan42]

I think you should use .horizontalalignment instead of justify