✒️ 방명록 github

IT/기타

[IT/일상] 엑셀 VBA 매크로로 페어 매칭 프로그램 만들기

땅일단 2024. 10. 19. 18:41

계기

최근 회사에서 페어 프로그래밍을 주기적으로 하게 됐는데...

페어를 짓는 프로그램을 개발부서원들이 직접 개발하는 소소한 대회(?) 가 열렸다.

상품은 무려 치킨이기 때문에 도전하지 않을 수 없었다.

 

어떤 플랫폼, 어떤 언어로 하든지 상관이 없어서 그것부터 결정해야 했는데, 엑셀로 하는 게 제일 좋겠다는 결론에 이르렀다.

 

왜냐하면...

  • 결과로 나온 데이터가 저장되었으면 좋겠는데, 작은 프로그램이라서 DB까지 쓰기는 너무 거창하다.
  • 적당한 GUI를 쉽게 보여줄 수 있다.
  • 프로그램을 공유하기가 쉽고, 엑셀 프로그램만 있다면 별다른 환경 세팅이 필요없다.
  • 학교 다닐 때랑 컴활 1급 딸 때 학습했던 VB를 다 까먹어서 복습하고 싶다.

이런 이유들이 있었다.

 

 

다른 방법 생각해보기

하지만 위의 이점 (4번 빼고) 들을 위해 꼭 엑셀을 이용해야 하는 걸까? 

이제 와서 생각해보니까 내 지식 선에서 다른 대안도 있는데, 아래와 같다.

 

  • Java: Swing 등을 이용해서 GUI를 만든다.
  • Python: tkinter 등을 이용해서 GUI를 만든다.

대신 이 친구들은 JRE가 없는 환경에선 .exe로 변환해야 하고, 데이터를 저장할 파일(ex: .txt)도 있어야 해서 총 2개의 파일이 만들어지며, GUI 만들기가 좀 귀찮을 순 있을 듯. 그럼 GUI 만들기가 쉬운 html/js/css는 어떨까?

 

  • HTML/JS/CSS: GUI 만들기는 쉽다. 대신 CORS 보안 문제 때문에 브라우저에서 로컬 파일을 가져오려면 사용자가 명시적인 "파일 선택" 을 해야 한다.

명시적인 파일 선택을 하지 않고도 동작하게 하기 위해선 웹 서버를 돌려야 하지만 프로그램 공유 차원에서는 애매한 방법이고 (물론 회사 가상서버에 넣으면 되긴 한데... 엄...), 아니면 브라우저에서 안 돌아가도록 하면 된다. 어떻게 하냐, 바로 Electron, NodeGUI 등으로 "데스크톱 앱" 즉 .exe 파일로 변환하여 사용자의 컴퓨터에서 돌아가도록 하는 것이다.

 

...이렇게 보니 어떤 방법을 선택하더라도 고민거리는 있는 것 같다. GUI가 필요 없다면 자바나 파이썬, 아니면 Node.js를 이용하는 게 가장 정석적으로 보인다.

 

 

개발 환경 띄우기

버튼을 클릭하면 페어를 짜주는 기능을 만들 계획이라, 버튼을 누를 때 특정 매크로가 동작해야 한다.

매크로를 실행 가능한 파일로 만들려면 .xlsx가 아닌 .xlsm으로 저장해야 하므로 참고하자.

 

비주얼 베이직 에디터는 엑셀의 개발 도구 > Visual Basic 을 클릭해서 열 수 있다.

개발 도구 탭이 없다면, 파일 > 옵션 > 리본 사용자 지정 > 리본 메뉴 사용자 지정에서 개발 도구에 체크해주면 된다.

 

 

 

버튼으로 사용할 도형 하나를 만들고, 매크로 지정을 클릭한다. 그러고 나서 매크로를 새로 만들고 편집을 클릭하면 비주얼 베이직 에디터가 나타난다.

 

 

UI 만들기

UI는 좀 직관적으로 만들고 싶어서 간결하게 만들어 봤다.

이름은 스토브리그 배역들 이름임. (tmi)

 

 

기본적인 문법

Cells(행 번호, 열 번호)

셀의 값을 가져온다. 이때 인덱스는 1부터 시작한다. 열도 A, B, C... 가 아닌 자연수로 입력해야 한다.

 

Dim 변수이름 As Integer
변수이름 = 값

Dim 정적배열(0 To 3) As String

Dim 동적배열() As String
ReDim 동적배열(0 To 변수)

변수를 정의하고 값을 할당한다.

유의할 점은 정적 배열의 크기를 정의할 땐 상수가 들어가야 한다.

동적 배열은 변수든 상수든 상관 없다.

 

For i = 0 To 5 Step 2
    ' 반복할 동작
Next

Do while i < 10
    ' 반복할 동작
Loop

for문과 While문이다. 각각 Next와 Loop로 끝나야 한다.

 

If i = 2 Then
    ' 동작1
Elseif i = 3 Then
    ' 동작2
Else
    ' 동작3
End If

조건문이다. End If로 끝나야 한다.

 

 

페어 생성 기능 만들기

랜덤하게 짝을 만드는 건 배열의 요소들을 무작위로 섞어서 간단하게 구현이 가능하다.

물론 간단하단 것은 다른 언어일 때의 얘기고 VBA에선 조금 복잡하게 구현해야 한다.

배열의 순서를 무작위로 섞는 알고리즘(shuffle 등)을 기본적으로 지원하지 않기 때문이다.

 

그래서 고민하다가, 다행히 VBA에서 난수를 생성하는 기능은 지원했기에 이걸 이용하기로 했다.

Randomize

' 일부 변수 생성 부분은 생략
Dim startRowIdx As Integer
startRowIdx = 5

Dim n As Integer
n = 8

' 모든 사람의 이름을 names 배열에 추가
For i = 0 To n - 1
    names(i) = Cells(startRowIdx + i, 2)
Next

For i = 0 To n - 1
    ' 0 ~ n-i-1 까지의 난수 생성
    Dim randomIndex As Integer
    randomIndex = Int(Rnd * (n - i))

    ' 매칭
    res(i) = names(randomIndex)

    ' 마지막 인덱스 요소를 덮어쓰기
    names(randomIndex) = names(n - 1 - i)
Next

Rnd는 기본적으로 0에서 1 사이의 난수를 생성한다.

첫 번째 루프(i = 0)에서는 Int(Rnd * 8) 이 실행되는데, 이러면 0에서 7 사이의 랜덤한 정수가 만들어진다.

예를 들어서 5가 나왔다고 하자.

 

그 인덱스의 요소인 '양원섭' 을 res, 즉 결과 배열의 0번째 인덱스에 담는다.

그러고 나서 마지막 인덱스 요소인 '유경택' 을 names(5) 에 덮어쓴다. 다시 말해 '양원섭' 이 '유경택' 으로 교체된다.

 

그 다음 루프에선 0에서 6 사이의 랜덤한 정수를 만들고, 선택한다.

이 과정을 반복한다.

 

왜 굳이 배열의 길이를 줄이지 않고(요소 삭제 등) 덮어쓰는 방식을 택하냐는 의문이 들 수도 있는데,

VBA에서는 배열의 요소를 삭제하는 기능이 따로 없다.

요소를 삭제하거나 배열의 길이를 줄이려면 자바스크립트의 slice 같은 방식으로 배열을 다시 생성해야 한다.

 

 

직전 페어와 겹치는 페어가 있는지 식별

' ------직전 결과와 겹치는 페어가 있는지 확인------

isDifferentFromPreviousResult = True

Dim previousPairs() As String
Dim currentPairs() As String
Dim currentPairsReverse() As String

ReDim previousPairs(0 To n \ 2 - 1)
ReDim currentPairs(0 To n \ 2 - 1)
ReDim currentPairsReverse(0 To n \ 2 - 1)

Dim exitLoop As Boolean
exitLoop = False

For i = 0 To n \ 2 - 1
    previousPairs(i) = Cells(startRowIdx + i, 4) & Cells(startRowIdx + i, 5)
Next

For i = 0 To n \ 2 - 1
    currentPairs(i) = Cells(startRowIdx + i, 7) & Cells(startRowIdx + i, 8)
    currentPairsReverse(i) = Cells(startRowIdx + i, 8) & Cells(startRowIdx + i, 7)
Next

For i = 0 To n \ 2 - 1
    For j = 0 To n \ 2 - 1
        If currentPairs(i) = previousPairs(j) Or currentPairsReverse(i) = previousPairs(j) Then
            isDifferentFromPreviousResult = False
            exitLoop = True

            Exit For
        End If
    Next

    If exitLoop = True Then
        Exit For
    End If
Next

 

직전 페어와 겹치는 부분이 있는지 확인하는 코드는 길이가 길어졌지만 원리는 간단하다.

현재 페어의 이름을 연결한 요소를 담은 배열을 만드는데, ex) 이세영한재희

이때 거꾸로 연결된 배열도 하나 만든다. ex) 한재희이세영

 

직전 페어도 같은 방식으로 연결하고, 위의 두 가지의 배열과 비교한다.

비교해서 같은 페어가 존재한다면 더 이상 비교할 필요 없이 다른 조합으로 페어를 한번 더 만들어야 하기 때문에 isDifferentFromPreviousResult를 False로 만든 후 비교 반복문을 빠져나간다.

 

VBA에서 Exit For는 중첩 반복문에서 사용한다면 하나의 반복문만 빠져나가므로, exitLoop 플래그 변수를 통해 한 단계 더 빠져나가도록 하였다.

 

 

코드 전문

Sub 생성_click()
    Randomize
    
    Dim startRowIdx As Integer
    startRowIdx = 5
    
    Dim names() As String
    Dim res() As String
    Dim n As Integer
    n = 8
    
    ReDim names(0 To n - 1)
    ReDim res(0 To n - 1)
    
    Dim isDifferentFromPreviousResult As Boolean
    isDifferentFromPreviousResult = False
    
    Do While isDifferentFromPreviousResult = False
    
        ' ------랜덤한 페어 만들기------
        
        For i = 0 To n - 1
            names(i) = Cells(startRowIdx + i, 2)
        Next
            
        For i = 0 To n - 1
            ' 0 ~ n-i까지의 난수 생성
            Dim randomIndex As Integer
            randomIndex = Int(Rnd * (n - i))
            
            ' 매칭
            res(i) = names(randomIndex)
            
            ' 마지막 인덱스 요소를 덮어쓰기
            names(randomIndex) = names(n - 1 - i)
        Next
        
        
        ' ------셀에 결과 표시------
        
        For i = 0 To n - 1
            If i Mod 2 = 0 Then
                Cells(startRowIdx + (i \ 2), 7) = res(i)
            Else
                Cells(startRowIdx + ((i - 1) \ 2), 8) = res(i)
            End If
        Next
        
        
        ' ------직전 결과와 겹치는 페어가 있는지 확인------
        
        isDifferentFromPreviousResult = True
        
        Dim previousPairs() As String
        Dim currentPairs() As String
        Dim currentPairsReverse() As String
        
        ReDim previousPairs(0 To n \ 2 - 1)
        ReDim currentPairs(0 To n \ 2 - 1)
        ReDim currentPairsReverse(0 To n \ 2 - 1)
        
        Dim exitLoop As Boolean
        exitLoop = False
        
        For i = 0 To n \ 2 - 1
            previousPairs(i) = Cells(startRowIdx + i, 4) & Cells(startRowIdx + i, 5)
        Next
        
        For i = 0 To n \ 2 - 1
            currentPairs(i) = Cells(startRowIdx + i, 7) & Cells(startRowIdx + i, 8)
            currentPairsReverse(i) = Cells(startRowIdx + i, 8) & Cells(startRowIdx + i, 7)
        Next
        
        For i = 0 To n \ 2 - 1
            For j = 0 To n \ 2 - 1
                If currentPairs(i) = previousPairs(j) Or currentPairsReverse(i) = previousPairs(j) Then
                    isDifferentFromPreviousResult = False
                    exitLoop = True
                    
                    Exit For
                End If
            Next
            
            If exitLoop = True Then
                Exit For
            End If
        Next
    Loop
    
End Sub

Do while문을 통해 isDifferentFromPreviousResult가 True가 될 때까지 계속해서 조합을 새로 만든다.

 

 

결과 결정하기

그냥 현재 결과를 직전 결과 셀로 옮기는 기능이라 간단하다.

Sub 결정_click()
    Dim startRowIdx As Integer
    startRowIdx = 5
    
    Dim n As Integer
    n = 8
    
    For i = 0 To 8
        Cells(startRowIdx + i, 4) = Cells(startRowIdx + i, 7)
        Cells(startRowIdx + i, 5) = Cells(startRowIdx + i, 8)
        
        Cells(startRowIdx + i, 7) = ""
        Cells(startRowIdx + i, 8) = ""
    Next
End Sub

 

 

결과

 

 

 

 

 

PC방 시재 계산기 이후로 오랜만에 실생활에 필요해서 만든 프로그램이라 재미가 있었읍니다.

그리고 포스팅 하면서 계속 코드를 수정했는데, 역시... 코드리뷰의 중요성을 깨달았습니다.

 

배열 길이를 n -1로 했어야 했는데 n으로 한 부분이라든가... exitLoop를 못 타고 있었던 부분이라든가...

암튼 생각대로 결과물이 잘 나와줘서 다행이군요.

 

치킨을 못 받더라도 과정이 재미있어서 좋았습니다.

진짭니다........

저작자표시

'IT > 기타' 카테고리의 다른 글

[IT/일상] NAT Loopback 때문에 겪은 좌절의 시간  (0) 2024.10.03
[IT] 프론트엔드 관련 면접 질문 등 지식  (2) 2024.09.08
[IT] 로그인 시 세션 관리 방법  (0) 2024.08.04
[IT/책] Clean Code(클린 코드) - 주석  (0) 2024.07.25
[IT/책] Clean Code(클린 코드) - 변수와 함수  (0) 2024.07.24

'IT/기타'의 다른글

  • 현재글[IT/일상] 엑셀 VBA 매크로로 페어 매칭 프로그램 만들기

관련글

댓글

티스토리툴바