0

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.

AVP
  • 1
  • 1
  • 1
    Better to use this as a Sub and run from a button. – BigBen Sep 01 '21 at 14:52
  • Hi Ben, I'm not sure if using a button is the best way to go here, since I want to use these functions within different functions. The color change is just a small part of the code I'm planning to write. I'm going to use this within IF-statements for example. I made one button in the sheet and that should run all calculations and update the colors for clarity. – AVP Sep 01 '21 at 15:00
  • 1
    Don't really understand why a button wouldn't work. Also doesn't make sense to not use the function's argument. – SJR Sep 01 '21 at 15:08
  • Still make it a Sub since it does something and doesn't return anything. – BigBen Sep 01 '21 at 15:09
  • SJR, how am i not using the function's argument? That's the range of cells that need the new color. I thought I was selecting that range and then changing the color, could be wrong tho :) I could make it a sub, but I've been trying everything :p second example is a sub and that didn't work either. I'll make sure to make it a sub in the final version :) I can't make it a button since I won't be using this sub in the sheet, It's going to be hidden in different functions. The color change is not the main goal of my code, although important for a quick view for the end user who opens the sheet – AVP Sep 01 '21 at 15:17
  • Why not using conditional formatting ? – iDevlop Sep 01 '21 at 15:19
  • `"RangeToChange"` is not the same as `RangeToChange`. The former is a `String`, the latter is a `Range`. – BigBen Sep 01 '21 at 15:23
  • Hi Patrick, I'm hoping to avoid it, just for cleanlines in my code. It is the backup tho :) – AVP Sep 01 '21 at 15:24
  • I found an example somewhere where in they used .Range("A1") (see third example in original question) I found it strange as well, but trying everything :) – AVP Sep 01 '21 at 15:25

3 Answers3

1
  • This won't work as a UDF called from a worksheet cell. Except for some edge cases, e.g. this, UDFs called from a cell can't modify other cells on the worksheet.

    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.

  • Prefer Sub to Function since this does something and doesn't return anything.
  • "I have four colors to switch between so I'd rather make four functions, one per color" - better to make one function and pass a color parameter.
Private Sub SetColor(ByVal RangeToChange As Range, ByVal Color As Long)
    RangeToChange.Interior.Color = Color
End Sub

Called like

SetColor yourRange, OwnColorLong.Red
BigBen
  • 46,229
  • 7
  • 24
  • 40
0

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
Sam
  • 5,424
  • 1
  • 18
  • 33
  • Hi Sam, Thanks! The cells will be looped over and checked for conditions, that will decide the range, so unfortunately not the selected cells :/ – AVP Sep 01 '21 at 15:23
  • I have changed above – Sam Sep 01 '21 at 15:28
0

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!

AVP
  • 1
  • 1