Excel VBA Two functions on one array

Question

I need to delete spaces at the beginning, end of string and make string Proper Case.

I have found two scripts:

Sub Function01()

Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

  Range("R1", Range("R1").End(xlDown)).Select
  lRows = Selection.Rows.Count
  lCols = Selection.Columns.Count

  ReDim arrData(1 To lRows, 1 To lCols)
  ReDim arrReturnData(1 To lRows, 1 To lCols)

  Set rng = Selection
  arrData = rng.Value

  For j = 1 To lCols
    For i = 1 To lRows
      arrReturnData(i, j) = Trim(arrData(i, j))
      ///ADDING HERE(read below)

    Next i
  Next j

  rng.Value = arrReturnData

  Set rng = Nothing


End Sub

which is deleting spaces on string and another script:

Sub ChangeCase()
    Dim Rng As Range
    On Error Resume Next
    Err.Clear
    Application.EnableEvents = False
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
             xlTextValues).Cells
        If Err.Number = 0 Then
            Rng.Value = StrConv(Rng.Text, vbProperCase)
        End If
    Next Rng
    Application.EnableEvents = True
End Sub

Which is making Proper Case of string. Those two scripts are working on ranges to select all not null cells in R column. I need to make function second script in the first one.

Adding this code in first script at (///ADDING HERE) point: arrReturnData(i, j) = StrConv(arrData(i, j), vbProperCase)

Making my output in Proper Case but with spaces. Could you guys suggest how to make two script functions in a stroke?

Thank you!

Simply this: arrReturnData(i, j) = StrConv(Trim(arrData(i, j)), vbProperCase) — Rory
– Rory, Commented Sep 8, 2016 at 14:00
if you mean to convert it to string without spaces between words then you need to replace the spaces with vbNullString after ProperCasing them, Replace(StrnConv(..), space(1), vbnullstring) — tsolina
– tsolina, Commented Sep 8, 2016 at 14:07
@Rory Thank you very much now it is clear how to connect functions for me. — Wont Provide
– Wont Provide, Commented Sep 8, 2016 at 14:14

Scott Craner · Accepted Answer · 2016-09-08 14:12:35Z

1

This will do the whole without loops:

Sub Function01()    

  Dim rng As Range   

  Set rng = Selection
  rng.Value = rng.Parent.Evaluate("INDEX(PROPER(TRIM(" & rng.Address & ")),)")    

End Sub

Before:

After:

edited Sep 8, 2016 at 14:12

answered Sep 8, 2016 at 14:04

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wont Provide Over a year ago

Holy motherlucker. It is simpler than a tea recipe.

Collectives™ on Stack Overflow

Excel VBA Two functions on one array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related