For Example:

|        ||Column A|
|Row: 4  || 23,24  |  

If I have two variables,

Dim a As Integer
Dim b As Integer

How can I let "a" carry the number 23, and "b" cary number 24 in VBA?

Thanks in advance!

CodePudding user response：

• You read the value of that cell as a string.
• You use Split to turn it into an array of two strings.
• You convert both strings in that array into integers, most probably using the CInt() function.

CodePudding user response：

Dominique is right, although I believe you don't even need to use the CInt() function because you are storing the strings into a variable that is already initialized as an integer (ie: it gets converted automatically).

Here are two options (assuming cell A4 has the value "23,24"):

Sub Option1()
    Dim c As Range: Set c = Range("A4")
    Dim a As Integer
    Dim b As Integer

    'Using left()/right()
    Dim cellValue As String, commaPosition As Long
    cellValue = c.Text
    commaPosition = InStr(cellValue, ",")
    a = Left(cellValue, commaPosition)
    b = Right(cellValue, Len(cellValue) - commaPosition)
    
    Debug.Print a '23
    Debug.Print b '24
    Debug.Print a   b '47
End Sub


Sub Option2()
    Dim c As Range: Set c = Range("A4")
    Dim a As Integer
    Dim b As Integer
    
    'Using Split()
    Dim cellValues As Variant
    cellValues = Split(c.Text, ",")
    a = cellValues(0)
    b = cellValues(1)
    
    Debug.Print a '23
    Debug.Print b '24
    Debug.Print a   b '47
End Sub

Both execute at about the same speed.