Excel & VBA---Brain Training
프로그래밍에 있어서 문자열 정보를 다루는 것은 필수적이다
숫자도 문자형식으로 보내고 받으면서 다시 숫자로 만들어서 처리하고
그러니 문자열 정보를 잘 다루어야 프로그래밍이 훨씬 자유스러워진다
이 코너에서는 문자열을 지져먹고..볶아 먹도록 한다
보내고 받는 이메일도 문자열 정보이고
홈페이지 이름도 문자열 정보이고
엑셀 2007부터는 XML문으로 정보가 관리가 되기도 하니..
XML 또한 죄다 문자열정보의 조합이다
여러분의 이름, 주소 등등 숫자빼고 모두 문자열 정보다
아래의 구문을 하나 실행 시켜 보시기 바란다
질문중에서 참 많은 질문중의 하나다..
수많은 이메일주소가 있는데 이렇게 저렇게 붙여 넣고 짜깁기를 하다 보니까
중복되는 것도 많고
엉터리 주소도 많고..
좀 분석을 하기는 하여야겠는데..이것이 삽질이라는 것이다
아래의 구문을 실행해 보시면 줄줄 이메일이 만들어 질것이고..
<![CDATA[
Sub makeFalseEmails()
Dim iX As Integer
Dim iY As Integer
Dim iCount As Integer
Dim sEmail As String
Dim sEmails() As Variant
Dim oDupe As New Collection
Dim vX As Variant
Dim rDatas As Range
Dim rInsert As Range
iCount = Int(Rnd() * 1000) + 500
ReDim sEmails(1 To iCount)
For iX = 1 To iCount
For iY = 1 To 8
sEmail = sEmail & Chr(Int((122 - 97 + 1) * Rnd + 97))
Next
sEmail = sEmail & IIf(Int(Rnd * 20) = 0, "@@", "@")
For iY = 1 To 5
sEmail = sEmail & Chr(Int((122 - 97 + 1) * Rnd + 97))
Next
sEmail = sEmail & IIf(Int(Rnd * 20) = 0, "_", ".") & _
Array("com", _
"co.kr", _
"net", _
"edu", _
"gov", _
"biz", _
"mil", _
"org")(Int(Rnd() * 7))
If Int(Rnd() * 10) = 0 Then
oDupe.Add sEmail
End If
sEmails(iX) = sEmail
sEmail = ""
Next
iX = iX - 1
Set rDatas = Worksheets.Add.Range("A1").Resize(iX)
rDatas = Application.Transpose(sEmails)
Application.ScreenUpdating = False
For Each vX In oDupe
Set rInsert = rDatas.Rows(Int(Rnd() * iX))
rInsert.Insert
rInsert.Offset(-1) = vX
Next
Application.ScreenUpdating = True
End Sub
]]>
이 것 중에서 우선 중복되는 것을 걸러 내 보시기 바란다
그 것 풀고 또 다른 문제나간다!!
물론 데이타/휠터/유일한 값만 휠터/ 이딴 것으로 하는 것 말구
VBA로 하는 문제!!
이것은 여러 방법중에서 제일 간단하다고 생각되는 것은 아래 정도가 될 것이다
<![CDATA[
Sub getUniqString()
Dim rDatas As Range, rX As Range, oX As New Collection
Dim varX As Variant, iNext As Integer
Set rDatas = ActiveSheet.Range("A1").CurrentRegion
On Error Resume Next
For Each rX In rDatas.Cells
oX.Add rX, CStr(rX)
Next
With Worksheets.Add.Range("A1")
For Each varX In oX
.Offset(iNext) = varX
iNext = iNext + 1
Next
End With
End Sub
]]>
이곳 저곳에서 수도 없이 이야기 한 Collection오브젝트의 활용!!
Collection오브젝트에 Add 메소드의 두번째매개변수는 문자열로
주되 이것은 유일한 INDEX역할을 하는 것이기때문에 중복되는 것이 추가되면
에러가 난다..
그래서 On Error Resume Next를 하면 에러가 나도 그냥 계속 Add하지만
에러가 난 것은 빠지게 되는 셈이다
결과는 Collection오브젝트에 유일한 값들만 모아지게 되는 것이고
다 수집한 Collection오브젝트를 새로운 시트에 뿌려 주면 되는것
문제는 정상적인 Email을 어떻게 찾아내나???
그것이 문제다!!
잘 못된 이메일은 어떤 것이 있을까??
xxx@@xxx.com
xxx@ xxx.com
xxx @ xxx.com
xx;xxx@.com
xx]xxx@.com
xxx@[.com
xxx@/.com
xxx\@.com
xxx:@.com
xx*x@.com
xx(xx@.com
xxx@(.com
xxx@:.com
x<>xxxx@.com
xxxxxxx@.com
등등이 많을 것이다
또한 눈에 보이지 않는 문자 Tab문자,Enter문자등등이 들어 있을 것이다
이런것들을 일일이 비교하여 보아야 할 것이다
이런 것들은 일종의 자동화를 위한 삽질에 속하는 일들이다
이런 삽질도 어떻게 하면 편하게 할 수 있을까??를 생각해 보아야 할 것이다
워크시트 함수로 이것을 처리한다고 하면 SUBSTITUTE함수나 REPLACE함수등을
사용하겠지만..이것도 중첩에 중첩을 쳐서 할 짓이 아니다
VBA의 순환문은 그냥 주욱 훓으면 위의 문제가 되는 내용을 다 훓는다
문자열을 잘 다루면 , 문자열을 잘 다룬다고 하는 것은 문자열관련 함수를
잘 다룬다는 것과도 같은 소리일 것이다
또한 도메인이름도 각양각색일 것이다
.sex
.xxx
.jp
.fr
.hotel
.flight
등등 수도 없이 많을 것이나
아래 정도만 걸러내도록 하자
.com
.net
.co.kr
.edu
.gov
.biz
.mil
.org
각자가 필요한 상황에 따라서 휠터조건을 좀더 추가하면 될것이다
그런 요령으로..
문자열 체크와 더불어서
소루션소품으로 올렸던 ProgressBar를 잘 응용해 볼수 있는 문제라서
ProgressBar를 활용할수 있는 좋은 응용 쌤플화일이 되도록 하였다
BrainTraining_067_1.
