Returning text from a VBA function

ROHPitMan

New Member
Joined
Nov 1, 2002
Messages
3
Does anyone know of a way to colour code the text string returned from a VBA function? I have written a function that returns a string of the days of the week worked e.g. M*W*FS* (where *=day off) and I want to turn Sat or Sun red if they are present (i.e. not *), but the range.characters method doesn't work. Is it possible to create a string with embedded colour? Does anyone know how to colour parts of a string such as this? I'd appreciate any thoughts.

Cheers
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
Hi;

is it possible for you to post the code ?

Edit:

Or, may be something like this may help you;

[A1].Characters(Start:=1, Length:=5).Font.ColorIndex = 3
This message was edited by Raider on 2002-11-02 15:28
 

ROHPitMan

New Member
Joined
Nov 1, 2002
Messages
3
Thanks for the quick response. Here is the function:
Function DayList(ID As Integer, LookUp As Range)
Dim DayOfWeek(7) As Boolean
Dim lData As Integer, lFree As Integer, DayBinary As Integer
Dim DayOfWeekInc As Long
Dim QueryCell As Object
Dim DayLetters As String, DayListString As String, QueryWorksheet As String
DayListString = "": DayLetters = "MTWTFSS"
QueryWorksheet = "Weekly Breakdown (" + Format(ID) + ")"
With Worksheets(QueryWorksheet)
For Each QueryCell In LookUp
If .Cells(QueryCell.Row, 2) = 1 Then
lData = QueryCell.Value
If lData <> 0 Then
DayBinary = 1
For DayOfWeekInc = 1 To 7
If lData And DayBinary Then DayOfWeek(DayOfWeekInc) = True
DayBinary = DayBinary * 2
Next
End If
End If
Next
End With
For DayOfWeekInc = 1 To 7
If DayOfWeek(DayOfWeekInc) = True Then
DayListString = DayListString + Mid(DayLetters, DayOfWeekInc, 1)
Else
DayListString = DayListString + "*"
End If
Next
DayList = DayListString
End Function

This is called by:
=IF($J10<>"",DayList(1,'Weekly Breakdown (1)'!$BE$4:$BE$73),"")

The background to this spreadsheet is a long story, but basically enables an orchestra member to view the impact of a combination of projects on their working week. The function listed above displays which days of the week they will be working on, and changes dynamically when the user changes the projects they may wish to work on.
The line of sheet code is repeated once for each year week, calling a different range on the 'Weekly Breakdown (x)' sheet each time.
 

Swamp Thing

Active Member
Joined
Aug 16, 2002
Messages
313
Have you considered conditional formatting?

(There is no mechanism by which a function can return a formatted string when called from a cell.)
This message was edited by Swamp Thing on 2002-11-02 20:42
 

ROHPitMan

New Member
Joined
Nov 1, 2002
Messages
3
The problem is that I only want to colour either if the two S's if a Saturday or Sunday is worked, not the rest of the letters. I've tried a separate Sub to change the colour, but although I can debug.print the contents of the cell (e.g. M**T*S*), .Characters cannot seem to format the displayed string. I was hoping to avoid this (because its messy), but the only way forward I can see is to have a separate cell for each day and conditional format them if they are different from "*" 8-(
 

Forum statistics

Threads
1,144,220
Messages
5,723,091
Members
422,477
Latest member
pete101

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
Top