VocExcel(単語帳) / VBA Tips
 
 [Key] エクセル / Excel / VBA /マクロ


<--- 戻る

英文を半角空白に区切って「***」に置換する

サンプルファイル








Private Sub CommandButton1_Click()



Dim myKey
Dim myArray

Dim myWord
Dim myNumber
Dim myAsta
Dim q
Dim myString
Dim i

Dim tmp1 As String
Dim tmp2 As String
Dim tmp3 As String



myKey = Replace(Trim(TextBox1.Text), vbLf, "", , , 1) ' セル内の改行を取り除いている
myKey = Replace(myKey, " ", " ", , , 1) '全角空白を半角空白に変換



'---半角空白で分割し配列変数に代入
myArray = Split(myKey, " ")


'---UBound関数で配列のインデックスの最大値を取得
For i = 0 To UBound(myArray)

myWord = myArray(i)
myNumber = Len(myArray(i))
myAsta = "*"

For q = 0 To myNumber - 2

tmp1 = myAsta & "*"
myAsta = tmp1


Next q



'文字が一つのとき
If myNumber = 1 Then

tmp1 = "*"

End If


'====================================================

'カンマ、ピリオド
Select Case myWord

Case ","
tmp1 = " , "

Case ","

tmp1 = " , "

Case "."

tmp1 = "."

Case "."
tmp1 = "."

Case "-"
tmp1 = " - "

Case "〜"
tmp1 = " 〜 "

Case "?"
tmp1 = " ? "


End Select


'====================================================

tmp2 = tmp1 & " "



If i = UBound(myArray) Then

tmp2 = tmp1
End If



tmp3 = tmp3 & tmp2


myString = tmp3



Next i



TextBox2.Text = myString '*




End Sub


--