|
Formatting a cell calculated by a VBA function
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 |
Formatting a cell calculated by a VBA function
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? --- Regards, Norman "Andy" wrote in message m... 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 |
Formatting a cell calculated by a VBA function
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 "Bob Phillips" wrote in message ... 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" wrote in message m... 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 |
Formatting a cell calculated by a VBA function
|
Formatting a cell calculated by a VBA function
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 |
Formatting a cell calculated by a VBA function
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 wrote in message oups.com... 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 |
| All times are GMT +1. The time now is 06:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com