CODEWITHSUNDEEP
vbaexcel
Preston
Preston

Reputation: 8187

Let Value-string, Get value long VBA

I'm using a class module to store values for the position of buttons. I will use the same function to add 3 buttons in a row, and so need to change the .left to position the button based on its type;

Ie for a 'Create' button, I will look at the .Left value for Column A

for a 'Update' button, I will look at the .Left value for Column B

for a 'Destroy' button, I will look at the .Left value for Column C

I could add the case select in my code, but for readability i think it would sit better in the class module.

Currently I am trying to do the following- Relevant lines in class module:

Private pLeft As Long

Public Property Get Left() As Long
Left = pLeft
End Property

Public Property Let Left(strType As String)
    Select Case strType
        Case "Create"
            pLeft = pRange.Offset(0, 4).Left
        Case "Update"
            pLeft = pRange.Offset(0, 5).Left
        Case "Open"
            pLeft = pRange.Offset(0, 6).Left
    End Select
End Property

Relevant lines in module:

Dim Button As CButton
sub LetValueForLeft()
    Set Button = New CButton
    Button.Left = "Create"
End Sub

Heart breakingly I get the error :

enter image description here

I assume this is because I'm LETing with a string and GETing with a long, is there a way around this?

I have been using the really useful guide below, but it says nothing about this type of situation:

http://www.cpearson.com/excel/classes.aspx

Upvotes: 0

Views: 110

Answers (1)

Comintern
Comintern

Reputation: 22205

The Let and Get of a property need to be the same type. If I understand correctly what you're trying to do, you can always make Left a read-only property and use a method to initialize it:

'Class
Private pLeft As Long

Public Property Get Left() As Long
    Left = pLeft
End Property

Public Sub AssignLeft(strType As String)
    Select Case strType
        Case "Create"
            pLeft = pRange.Offset(0, 4).Left
        Case "Update"
            pLeft = pRange.Offset(0, 5).Left
        Case "Open"
            pLeft = pRange.Offset(0, 6).Left
    End Select
End Sub

You'd call it like this:

Set Button = New CButton
Button.AssignLeft "Create"

Upvotes: 2

Related Questions