Can i limit 255 characters in a variable in vba?

Question

I need a variable created in a loop to have a max value of 255 characters. How can I do this? The system I'm using limits the number of characters to 255. And in some cases, this value is overstated by a lot :P

Dim resultado As String
resultado = ""
Range("m2").Select

resultado = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
                
Do While ActiveCell.Value <> ""
    resultado = resultado & ";" & ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Loop

Read first how to [avoid select](https://stackoverflow.com/q/10714251/5514747) in excel vba. — Harun24hr, Jun 10 '22 at 06:29

Tim Williams · Answer 1 · 2022-06-10T15:14:02.850

0

You could do it like this -

Sub Test()
    Dim resultado As String, c As Range, v, sep
    
    Set c = ActiveSheet.Range("M2") 'start here
    Do
        v = c.Value
        If Len(v) = 0 Then Exit Do                      'stop if no more data
        If Len(resultado & sep & v) > 256 Then Exit Do  'too long?
        resultado = resultado & sep & v                 'not too long - append
        sep = ";"               'add separator after first value
        Set c = c.Offset(1)     'next cell
    Loop
    
    Debug.Print Len(resultado), resultado
End Sub

edited Jun 10 '22 at 15:14

answered Jun 10 '22 at 06:57

Tim Williams

154,628
8
97
125

1

Thoug helpful for sure, I strongly encourage fully qualifying of range references, especially when answering questions from obvious beginners, so they don't fall into familiar traps. @TimWilliams – T.M. Jun 10 '22 at 07:16

score 0 · Answer 2 · answered Jun 10 '22 at 07:48

Try this

        Dim resultado As String: resultado = ""
        Dim I as long: I = 0
        Range("m2").Select
        
        Do While ActiveCell.Offset(I ,0).Value <> ""
        If len( resultado & ";" & ActiveCell.Offset(I ,0).Value) < 255 Then
          resultado = resultado & ";" & ActiveCell.Offset(I ,0).Value
        End if 
           I = I + 1
        Loop

Can i limit 255 characters in a variable in vba?

2 Answers2