CODEWITHSUNDEEP
excelvbscriptvba
Arup Rakshit
Arup Rakshit

Reputation: 118271

how to remove spaces added with a values with the array elements?

I am having a major problem with an Array values, which are added with spaces when they were added to the array. So is there any way to remove such spaces means using Trim in a single line of statement, before they are used in further processing, otherwise I would need to use Trim in every calculations. So any quicker process to remove such spaces one time within the code?

Dim VMHArray
VMHArray = Array("VMH All Fields Review - Add Supplier  Emergency", _
                 "VMH All Fields Review  (Req. Provides Supplier Details) - Update/Enable Supplier", _
                 "VMH Triggered Final Sourcing Review - Add Address_Direct", _
                 "Activate / Create supplier in business system - Add Address", _
                 "Activate / Create supplier in Business System - Add Supplier")

Upvotes: 0

Views: 8932

Answers (3)

Jüri Ruut
Jüri Ruut

Reputation: 2530

My VBE didn't accept 350 entries in an array.

However, for VBA works the code below with a reduced set:

Sub main()

Dim VMHArray As Variant

VMHArray = Array("VMH All Fields Review - Add Supplier  Emergency", _
"VMH All Fields Review (Req. Provides Supplier Details) - Update/Enable Supplier", _
"VMH Triggered Final Sourcing Review - Add Address_Direct", _
"Activate - / Create supplier in business system - Add Address", _
"Activate /Create supplier in Business System - Add Supplier", _
"Activate / Create supplier in Business System -Add Supplier Emergency")

VMHArray = TrimArrayElements(VMHArray)

End Sub

Function TrimArrayElements(InputArray As Variant) As Variant

  Dim arr_element As Variant

  For Each arr_element In InputArray
    arr_element = Trim(arr_element)
  Next

End Function

Upvotes: 1

rene
rene

Reputation: 42463

Can't you clean up the array after initialization, like so:

Sub test()
   Dim a(2) As String
   a(0) = "t1"
   a(1) = "t2  "
    a(2) = "test3 "
    Dim i As Integer

    Dim trimmed As Variant

    trimmed = TrimArray(a)

     For i = 0 To UBound(trimmed)
         Debug.Print trimmed(i) + "|"
     Next

End Sub

Function TrimArray(ByRef a() As String) As Variant
    For i = 0 To UBound(a)
     a(i) = Trim(a(i))
    Next

    TrimArray = a
End Function

Upvotes: 3

Kyle
Kyle

Reputation: 1019

Trim the values as they are inserted into the array. Or, alternatively, loop over the array, trim each element and reinsert the trimmed element into the array.

Upvotes: 4

Related Questions