VBA RGB Code LBound to UBound

[Mi500100]

Hi All,

this is a most excellent forum with a wealth of information and a wonderful user base! I have learned a lot from this forum, and see many thoughtful answers. I have a macro which I need help with, I'm attempting to display a list of RGB codes to the recent colors section within excel via a VBA macro.

The current syntax is such:

Sub LoadRecentColors()
'PURPOSE: Use A List Of RGB Codes To Load Colors Into Recent Colors Section of Color Palette

Dim ColorList As Variant
Dim CurrentFill As Variant

'Array List of RGB Color Codes to Add To Recent Colors Section (Max 10)
ColorList = Array("198, 224, 180", "255, 255, 204", "230, 184, 183", "184, 204, 228", "54, 96, 146")

'Store ActiveCell's Fill Color (if applicable)
If ActiveCell.Interior.ColorIndex <> xlNone Then CurrentFill = ActiveCell.Interior.Color

'Optimize Code
Application.ScreenUpdating = False

'Loop Through List Of RGB Codes And Add To Recent Colors
For x = LBound(ColorList) To UBound(ColorList)
ActiveCell.Interior.Color = RGB(Left(ColorList(x), 3), Mid(ColorList(x), 5, 3), Right(ColorList(x), 3))
DoEvents
SendKeys "%hhm~"
DoEvents
Next x

'Return ActiveCell Original Fill Color
If CurrentFill = Empty Then
ActiveCell.Interior.ColorIndex = xlNone
Else
ActiveCell.Interior.Color = currentColor
End If

End Sub


I am having difficulties understanding the LBound to Ubound list, everytime I run the macro or try to edit this portion of the macro, I don't have success.

Would one of you VBA guru's be able to help?

Thanks,
MI

 

[NdNoviceHlp]

Re: VBA RGB Code LBound to UBound Help

To Dim arrays.....

Dim ColorList() As Variant

Here's some trial code for U....

Option Base 1

Sub Arraytest()
Dim colorlist() As Variant, Cnt As Integer
colorlist = Array("Hi1", "Hi2", "Hi3", "Hi4")
For X = LBound(colorlist) To UBound(colorlist)
MsgBox "Bound test " & colorlist(X)
Next X

'Not Option base 1 test
'For Cnt = 0 To UBound(colorlist)
'MsgBox "Not Option base 1 test " & colorlist(Cnt)
'Next Cnt

'Option base 1 test
For Cnt = 1 To UBound(colorlist)
MsgBox "Option base 1 test " & colorlist(Cnt)
Next Cnt
End Sub

Note that arrays positions start at zero (Lbound) unless option base 1 is declared at the top of the code page and then array positions start at 1 (ie Lbound is 1).
You load arrays by assigning information to their positions. For example U could...
Colorarray(0) = "Hi1" or Option Base1 Colorarray(1) = "Hi1" to enter "Hi1" to position 1 in the array. To get info out, you can loop the array with the Ubound/Lbound thing or specify what position U want the info from. So, if you want position 1 of array: Variable = colorarray(0) or Variable = colorarray(1) using option base 1.
Anyways, what are you trying to accomplish? How do you get the recent colours? Why are you storing the colours first? Do you just want to change the colour of something based on what's in the array... I'm not clear. Trial the code to learn about array use. Not sure if you know about commenting/uncommenting code? Just add/remove the apostrophes from the front of lines of code to turn them green and inoperative. Comment out the Option Base 1and Option Base 1 test part of the code and uncomment the rest to test the code as a normal array (non Option Base1). Good luck. Dave
ps Welcome to the Board
pps please use code tags

 

[Mi500100]

Re: VBA RGB Code LBound to UBound Help

NdNoviceHlp,

thank you for explaining commenting on and off items in VBA, it was very helpful. I have corrected this macro with your help. My goal was to be able to load 5 colors in the recent color pallet. I wanted to utilize this because I have numerous excel files which need these specific 5 colors coded in different cells, and since certain workbooks are saved under certain themes, it is difficult to track down the colors.

I will use code tags going forward.

Thank you again!

 

[NdNoviceHlp]

Re: VBA RGB Code LBound to UBound Help

You are Welcome. Thanks for posting your outcome. Dave