Reputation: 1
I've been building a team vacation calendar in Excel (Office 365 version) and I'm using VBA for the first time to automate some calculations and styling.
I've been stuck on the following:
I want to create a function that changes the background color of a cell. I have four colors to switch between so I'd rather make four functions, one per color. That function will then be called within different functions when needed. I don't want to use ColorIndex, but rather a custom color (I can use RGB or the Long value), but I can't get the ColorIndex to work either.
My assumption is that the problem lies with the range but at this point, who knows :D.
The long values of each color are stored within a self-made Enum "OwnColorLong".
Here are some of my tries, every time the result in my Excel sheet (when running as a formula) is "#Value!".
'Function SetBackgroundToRed(RangeToChange As Range)
' Dim ColorIWant As Long
' ColorIWant = OwnColorLong.Red
' RangeToChange.Interior.Color = ColorIWant
'End Function
'Sub SetColorToRed(RangeToChange As Range)
' RangeToChange.Select
' With Selection.Interior
' .ColorIndex = 3
' End With
'End Sub
'
Function SetBackgroundToRed(RangeToChange As Range)
Dim MyRange As Range
Set MyRange = Worksheets("Vacation Calendar").Range("RangeToChange")
MyRange.Select
With Selection.Interior
.ColorIndex = 3
End With
End Function
I'm still a bit confused about when to use a sub or a function, or when to best use a class module. All code is now placed within one module, I'll be writing a Main sub linked to a button and putting all the code in there except for the functions themselves. If there are better practices, feel free to let me know.
Upvotes: 0
Views: 6850
Reputation: 1
I got this to work eventually!
Dim rng As String
rng = "A1"
Range(rng).Interior.Color = OwnColorLong.Red
The problem was not knowing how to pass a range as a variable, I had to use String apparently, not Range.
Thanks everyone for the help!
Upvotes: 0
Reputation: 49998
Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.
Sub
to Function
since this does something and doesn't return anything.Private Sub SetColor(ByVal RangeToChange As Range, ByVal Color As Long)
RangeToChange.Interior.Color = Color
End Sub
Called like
SetColor yourRange, OwnColorLong.Red
Upvotes: 1
Reputation: 5721
Second, Sub
or Function
? If you need an answer from your method, then Function
it is. In your case, you need no answer, so it is Sub
.
Sub SetBackgroundToRed(RangeToChange As Range)
With RangeToChange.Interior
.ColorIndex = 3
End With
End Sub
Upvotes: 0