Excel VBA - Type inside Class Module

Question

I am trying to use a Type inside a Class Module, but I keep getting several errors. I have tried switching between "Private" and "Public", but to no avail.

Can anyone provide me an example of how to use a Type inside a Class Module?

This is what I am trying:

'Class "clsColor"

Private Type Colors
Red as string
Green as string
Blue as string
End Type

Private Hex as Colors
'_____________________________________________
Private Sub Class_Initialize()
Hex.Red = "FF0000"
Hex.Green = "00FF00"
Hex.Blue = "0000FF"
End Sub

'Regular Module

msgbox clsColor.Hex.Red

What can I do to make this Type work inside the Class Module? Or is it better practice to create an extra Class instead of a Type?

Declare it in a standard module (public) and the class should only have a property of that type. — Kostas K.
– Kostas K., Commented Aug 18, 2021 at 15:12

T.M. · Accepted Answer · 2021-08-18 18:57:20Z

2

Brian M Stafford proposing a two-class approach noted: "Type cannot be used in this case" when you want to set different colors as object property to .Hex, e.g. via

MyColor.Hex.Blue

"Can anyone provide me an example of how to use a Type inside a Class Module?"

Instead of a successive object hierarchy, it might make calls more fluent, if one passes the wanted color as ►variant argument to .Hex only.

In this case you need no related class and can profit from Type (and Enum) definitions allowing to get the wanted result e.g. via (enumerated constant)

MyColor.Hex(Blue)      ' or via VBA.ColorConstants: MyColor.Hex(vbBlue)
                       ' or via string input:       MyColor.Hex("Blue")

Class Color - Header definitions (Enum, Type)

Option Explicit

Enum ColorSynonyms
   [_Start] = -1
'equivalents to the 8 VBA.ColorConstants (vbBlack, vbRed..)
    Black
    Red
    Green
    Yellow
    Blue
    Magenta
    Cyan
    White
'---user defined constants
    Brown
    Grey
    Orange
End Enum

Private Type THexColors
        Red   As String
        Green As String
        Blue  As String
End Type

Private HexColors As THexColors

Class Color - further code

Private Sub Class_Initialize()
    With HexColors
        .Blue = "0000FF"
        .Green = "00FF00"
        .Red = "FF0000"
    End With
End Sub

Public Property Get Hex(currColor) As String
    Select Case currColor
        Case "Red", Red, vbRed
            Hex = HexColors.Red
        Case "Green", Green, vbGreen
            Hex = HexColors.Green
        Case "Blue", Blue, vbBlue
            Hex = HexColors.Blue
        Case Else
            Hex = "Undefined!"
    End Select
End Property

Example Call

Private Sub Test()
    Dim MyColor    As Color
    Set MyColor = New Color
    Debug.Print "Blue", MyColor.Hex(Blue)
    
'alternatively:
    Debug.Print "Blue", MyColor.Hex(vbBlue)
    Debug.Print "Blue", MyColor.Hex("Blue")
End Sub

edited Aug 18, 2021 at 18:57

answered Aug 18, 2021 at 18:45

T.M.

10.1k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rezzy777 Over a year ago

Great solution, though a bit complex for my case (I have a lot more than a few colors to work with)! But thanks anyway, I'll keep this solution in mind!

Rezzy777 Over a year ago

Good point, I just did! :-) I also recently got a message while trying to make a new question that 'some of my questions have not been well-received', which apparently means I asked some wrong questions I am not aware of. Do you know where I can see my not-so-well-received questions?

T.M. Over a year ago

The best thing to do is to study How to Ask and MCV E, an abbreviation for a Minimal, Complete, and Verifiable Example. - Some hints: Voting down, also known as "casting downvotes", is how the community indicates which questions and answers are least useful - c.f. SO's help Vote down. Always show what you tried and identify the precise errors you were facing. Downvotes also reflect personal expectations and may set the bar high.

Brian M Stafford · Accepted Answer · 2021-08-18 15:37:10Z

1

A Type cannot be used in this case. Instead use a Class:

Class Colors

Option Explicit

Public Red As String
Public Green As String
Public Blue As String

Class Color

Option Explicit

Private m_Colors As Colors

Private Sub Class_Initialize()
   Set m_Colors = New Colors
   m_Colors.Red = "FF0000"
   m_Colors.Green = "00FF00"
   m_Colors.Blue = "0000FF"
End Sub

Public Property Get Hex() As Colors
   Set Hex = m_Colors
End Property

Form

Option Explicit

Private Sub Test()
   Dim MyColor As Color
   Set MyColor = New Color
   Debug.Print MyColor.Hex.Red
End Sub

answered Aug 18, 2021 at 15:37

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

4 Comments

T.M. Over a year ago

Solved the issue how to use {object}.Hex.{color} syntax +:) - Fyi You might be interested in my answer focussing more on the practical use of Type and Enum definitions as asked by OP ("Can anyone provide me an example of how to use a Type inside a Class Module?")

Rezzy777 Over a year ago

I got everything working properly now. One final question (if I may): at the bottom of class 'Color' you use "Set" to set "Hex" to "m_colors". Is this because you defined Hex as a class "Colors", and classes always need to be set? I'm pretty new to Classes, hence my question ;-)

Brian M Stafford Over a year ago

@Rezzy777 Yes, that is correct. Classes need to use Set.

T.M. Over a year ago

Further readings: MS Help: Objects and classes as well as Chip Pearson's classical site, here about[Classes] (cpearson.com/excel/classes.aspx) - @Rezzy777

Collectives™ on Stack Overflow

Excel VBA - Type inside Class Module

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related