Visual Basic Excel - Delete an array name

Question

I’m here with some problems, I never touched Visual Basic and I was wondering if it would help me to make a list I have in Excel faster (Through Visual Basic Excel).

It’s very simple, I just want to search in the array if you have a word that has less than 3 letters and if you have, delete the word or do replace by "" (which I think is to have nothing too)

I had something like this, but it’s not working, returns:

Object required

My code:

  nome = Target(1, 1)
  nomesArray = Split(nome)
  Dim i As Integer
  i = 0
  While i < UBound(nomesArray, 1)
      If Len(nomesArray(i)) < 3 Then
          nomesArray.Value = Replace(nomesArray.Value, nomesArray(i), "")
      End If
      i = i + 1
  Wend

Can someone please help?

Answer 1

Arrays in VBA (Visual Basic For Application) are not objects, so they have no methods or attributes.

Therefore, nameArray.value cannot be used, since the array has no attributes. VBA arrays are like C and C arrays++.

Based on what you need to do, I modified your code, see below:

nome = Target(1, 1)
nomesArray = Split(nome)
Dim i As Integer
i = 0
While i < UBound(nomesArray, 1)
  If Len(nomesArray(i)) < 3 Then
      nomesArray(i) = ""
  End If
  i = i + 1
Wend

Note that the only modification was to change the way you assign the empty value to the position of the vector that has less than 3 positions.

Anyway, the array will remain the same size, but with positions that have value = "".