Formatting a cell calculated by a VBA function

[Andy]

Hi, I am writing some xtra functions for excel, and am wanting to
return formatting with the result.

An example might be creating a NextBusinessDay(Today As Date) function
that returns the next business day after the date supplied. I can
write the code to return the value. but i would also like to apply
formatting to the cell.

So for example if the day is a Friday, color it red.


It cells in the excel spreadsheet that use the function would just
have a value like =NextBusinessDay('07/01/2005')

Is it possible to return formatting directly from the VBA function. or
is there a way to tell from the VBA functions which cell has called
it.

Thanks
Andy

 

[Norman Jones]

Hi Andy,

A worksheet function can only return a value to the the calling cell it
cannot apply formatting.

BTW, for your NextBusinessDay function, you can use the Workday function if
you have the Analysis ToolPak loaded.

For your format problem, perhaps you could use conditional formatting?

 

[Bob Phillips]

Andy,

VBA should be the last resort. You will often find that there are worksheet
fun ctions that will do what you want, which will save you re-inventing the
wheel, For instance, the WORKDAYfunction can find the next business day for
you, and even account for holidays.

To do the colouring, you can use conditional formatting, again, built in
techniques.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andy]

Sorry I've chosen an incorrect example here, and it's confused the
matter, I tried to simplify it too much. The functions are custom
functions to be used by business anyalst's, the functions connect to
an SQL data source and retrieve information on a number of statistics
about the business, such as the Spot Price of electricity etc, so they
can't be replaced by something else.

The functions would be used in a spreadsheet by the BAs to perform
calculations and it would be handy if the formatting, and visual
indicators could be applied programmatically by the function to help
the BAs view and understand the results.

Thanks
Andy

 

[Ron Rosenfeld]

Sorry I've chosen an incorrect example here, and it's confused the
matter, I tried to simplify it too much. The functions are custom
functions to be used by business anyalst's, the functions connect to
an SQL data source and retrieve information on a number of statistics
about the business, such as the Spot Price of electricity etc, so they
can't be replaced by something else.

The functions would be used in a spreadsheet by the BAs to perform
calculations and it would be handy if the formatting, and visual
indicators could be applied programmatically by the function to help
the BAs view and understand the results.

Thanks
Andy

You may be able to preformat the appropriate cells when you set up the
worksheet; and also use conditional formatting.

If that is not flexible enough, I think you will have to use event triggered
macros to perform the appropriate formatting after the UDF's have changed the
cell.


--ron

 

[ajharvey]

Hi

Is it also true, that you cannot change the format of any Cell within a
macro if the macro is run during the calculation of a cell?

In testing I have the following function

Function Format() As String
Range("A1").Font.Bold = True
Format = "Done"
End Function

Then i set Cell B1 to equal =Format() which runs the function, I can
break execution in the function and execute the Bold = True line, but
in the watch window the Property stays false.

However if i run the function from a button or by just running the
macro, the line works and changes the cell text to bold.

Seems to me that the contents and formatting of the worksheet is locked
and readonly while Excel is calculating a cell.
Is this true?

Thanks
Andy

 

[Chip Pearson]

Yes, it is true that you can't change the formatting of a cell in
a function called from a worksheet cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com