[다정다감] 프로그래밍Tips (엔지니어를 위한)파이썬 시작하기[14] 엑셀VBA-파이썬 연동

프로그래밍 Tips
프로그래밍에 관한 Tip을 공유하는 곳입니다.

(엔지니어를 위한)파이썬 시작하기[14] 엑셀VBA-파이썬 연동

이석종

2022-12-05 14:30:31, 조회 : 414, 추천 : 38

- Download #1 :

engpython14.png (37.5 KB), Download : 6

- Download #2 :

rcworks.zip (17.1 KB), Download : 6

(엔지니어를 위한) 파이썬 시작하기[14]

내용 : 엑셀VBA - 파이썬 연동

참조 : https://wikidocs.net/157782 (VBA에서파이썬 코드 실행하기)

xlwings 공식 홈페이지 : https://docs.xlwings.org/en/stable/udfs.html (User Defined Functions)

0.시작하며

지난 시간에는 python으로 Excel의 sheet에 값이나 수식을 넣는 것을 소개했다. 엑셀 고급사용자들은 자신만의 VBA를 만들어서 사용한다. Visual Basic은 마이크로 소프트가 MS Office에 도입한 언어로 오랫동안 사용되어 왔다. xlwings는 기존에 사용하고 있는 VBA를 사용하면서 python코드들을 같이 사용할 수 있는 방법을 제시하고 있다. python측에서 엑셀을 통제하는 방법은 명령창에서 파이썬 코드를 실행시키거나 파이썬 코드를 실행파일로 만들어서 실행해야 한다. 조금 불편할 수도 있다. VBA의 장점은 엑셀 시트 안에 버튼을 만들 수도 있고, 별도의 GUI를 만들 수 있다. 만일 VBA는 UI용으로만 쓰고 필요한 작업을 하는 코드는 python으로 만든다면 어떨까? 예를 들어 철근량을 구해주는 함수를 만들었다고 하자. VBA로 만들면 해당 엑셀에서밖에 사용하지 못한다. 파이썬으로 만들면 다른 파이썬 프로그램이나 다른 엑셀에서도 불러다 사용할 수 있다. 하나의 파이썬 코드를 만들어서 사용할 수 있고 중복을 피할 수 있다는 것이다.

1. xlwings설치 확인 및 addin설치

엑셀 VBA와 python을 연동하기 위해서는 Xlwings 가 설치되어 있어야 한다. 아나콘다를 설치하면 xlwings가 같이 설치되는데 xlwings설치 확인은 이전시간의 http://www.gumifo.org/zeroboard/view.php?id=devtips&no=56

'(엔지니어를 위한) 파이썬 시작하기[13] 엑셀 파이썬 연동'을 참조하자. 아나콘다 설치는 첫 시간의 http://www.gumifo.org/zeroboard/view.php?id=devtips&no=40 '(엔지니어를 위한) 파이썬 시작하기[1]'을 참조하자.

엑셀에서 python코드를 사용하려면 xlwings용 addin을 설치해야 한다. 명령창에서 xlwings addin install이라고 입력하면 된다. 물론 xlwings가 설치되어 있어야 한다.

(base) D:\dev\python\xlwings>xlwings addin install
xlwings version: 0.24.9
Successfully installed the xlwings add-in! Please restart Excel.
There is already an existing ~/.xlwings/xlwings.conf file. Run 'xlwings config create --force' if you want to reset your configuration.

2. 설정

xlwings addin이 설치되었다면 엑셀을 실행시켜보자. 메뉴에 xlwings가 추가된 것을 확인할 수 있다. 이 메뉴에 VBA와 파이썬을 연결해주는 기능들이 들어 있다.

그리고 [개발 도구] 메뉴가 보이지 않는 경우에는 [개발 도구] 메뉴가 보이도록 해야한다.

[파일] 메뉴의 [옵션]을 선택한 후 [리본 사용자 지정]을 선택한 후 오른쪽 기본 탭에서 개발도구를 선택하면 [개발 도구] 메뉴가 나타난다.

[개발 도구] 메뉴를 선택하자. 리본이 나타나면 Visual Basic을 선택하자.

그러면 VBA 창이 별도로 뜬다. VBA 코드를 편집할 수 있는 창이다.

[도구] 메뉴의 [참조]를 선택한다. [참조(R)...]을 선택한다.

사용가능한 참조 리스트에서 xlwings를 선택하고 [확인] 버튼을 누른다.

참조 다이얼로그 박스의 아래 쪽을 보면 xlwings 위치를 알 수 있다. 사용자명\AppData\Roaming\Microsoft\Excel\XLSTART다. 이 폴더에 가보면 xlwings.xlam파일이 있는 것을 알 수 있다. xlam파일은 엑셀의 추가기능 파일이다. 디렉토리명을 보면 알겠지만 XLSTART 디렉토리에 있는 파일은 엑셀이 실행되면 자동으로 열린다.

VBA창을 열었을 때 왼쪽 프로젝트 탐색기에 이미 xlwings.xlam파일이 열려있는 걸 확인할 수 있다. 프로젝트 탐색기에 통합문서1과 xlwings.xlam파일이 열려있는 것을 알 수 있다. xlwings.xlam파일이 엑셀을 실행시킬 때마다 열려서 VBA와 파이썬을 연결해주는 중요한 역할을 한다.

여기까지 설정하면 VBA에서 파이썬 코드를 실행하는 데 문제가 없다. 그런데 필자가 테스트해본 결과 좀 불편한 점이 있다. 파이썬 파일이 엑셀 파일과 같은 디렉토리에 있어야 하고 파일이름도 같아야 한다. 상상해보자. 엑셀 파일은 내가 지금 일하고 있는 프로젝트 디렉토리에 있는데 매번 파이썬 코드를 엑셀파일이 있는 곳에 복사해서 써야 한다면 여간 불편한 것이 아닐 것이다.

그래서 좀 번거롭지만 설정을 더 하자. 파이썬 코드가 있는 위치와 파이썬 파일 이름을 지정해주는 과정이다.

xlwings설정 파일을 찾아서 수정해줄 것이다. xlwings설정 파일은 c:\users\사용자이름\.xlwings 디렉토리에 있는 xlwings.conf 파일이다. 이파일을 열어 "PYTHONPATH","" 와 "UDF MODULES",""의 값을 바꿔주면 된다.

"PYTHONPATH"는 파이썬 코드가 있는 디렉토리다. 우리 엔지니어링소셜코딩에서 만든 코드들을 C:\ESC 디렉토리에 모아놓기로 하자. "PYTHONPATH","C:\ESC" 이렇게 수정해주자. 이제 엑셀에서 파이썬 파일을 실행시키면 우선 엑셀파일이 있는 디렉토리에서 파일을 찾고 없으면 PYTHONPATH에 설정된 디렉토리에서 찾게 된다.

이번에는 UDF MODULES를 설정할 차례다. 이 설정은 UDF(User Defined Function, 사용자 정의 함수)를 사용하기 위한 설정이다. 우리는 철근콘크리트 관련 기능들을 모아놓은 파이썬 소스 파일을 rcworks.py라고 만들 예정이다. 파이썬 코드 파일명을 써주면 된다. 확장자는 제외하고 파일이름만 써준다.

"UDF MODULES","rcworks"

그러면 xlwings.conf의 전체 내용은 아래와 같다. 다른 설정 항목들도 있는데 xlwings 공식 사이트의 관련 페이지를 참조하자. https://docs.xlwings.org/en/stable/addin.html Add-in & Settings

"CONDA PATH","C:\ProgramData\Anaconda3"
"CONDA ENV","base"
"PYTHONPATH","c:\esc"
"SHOW CONSOLE","False"
"UDF MODULES","rcworks"

이렇게 VBA와 파이썬을 연동하기 위한 설정은 끝났다.

3. VBA에서 파이썬 실행하기

이제 VBA에서 파이썬 코드를 실행해보자. 우선 파이썬 코드를 만들자. 파일 이름은 rcworks.py이고 c:\esc 디렉토리에 저장하자.

이 파이썬 코드는 두 개의 기능이 있다 하나는 필요 철근량을 구해주는 함수(udf_cal_As)이고 또 하나는 필요 철근량 산정 테이블을 만들어주는 함수(reqAsTable)다. reqAsTable은 엑셀에 필요철근량 산정용 테이블을 만드는 기능이고, udf_cal_As는 엑셀함수처럼 사용할 철근량을 산정해주는 UDF다.

지금 만들려는 기능은 매크로(reqAsTable)를 실행시키면 현재 선택된 셀(A2)에 테이블을 만든다. 이 테이블은 필요철근량을 구하는데 필요한 데이터들과 마지막 칼럼(K3)에 udf_cal_As함수를 삽입해준다. udf_cal_As함수는 A3~J3까지의 셀이 물려 있다. 예제를 하나 만들어 준다고 생각하면 되겠다. 여러 단면의 철근량을 구할 때 이 테이블을 이용하면 편하다. 기본테이블의 내용을 수정하고 복사해서 여러 단면의 필요철근량을 구할 수 있다.

이 기능을 만들기 위해서는 파이썬 쪽에서 해야 하는 작업이 있고 VBA쪽에서 해야하는 작업이 있다. 우선 파이썬 쪽에서 코딩을 해보자. 파일명은 rcworks.py이고 디렉토리는 C:\ESC이다.

import xlwings as xw

def reqAsTable():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    seladd = wb.app.selection.address
    formula="=udf_cal_As(" + ",".join(sheet[seladd].offset(1,i).address.replace("$","") for i in range(0,10)) + ")"
    tblhead = ["Φc", "Φs", "α", "β", "fck(MPa)", "fy(MPa)", "B(mm)", "H(mm)", "d'(mm)", "Mu(kN.m)", "reqAs(mm)"]
    row1 = [0.65, 0.9, 0.8, 0.4, 30, 400, 1000, 800, 80, 100, 0]
    tbl = [tblhead,row1]
    sheet[seladd].value = tbl
    sheet[seladd].offset(1,10).formula=formula

@xw.func
def udf_cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu):
    """ Calculate required As"""
    return cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu)

def cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu):

    d = H-dp

    # V1,2,3 : V1*As²+V2*As+V3=0
    V1 = beta*pi_s**2*fy**2/(alpa*pi_c*0.85*fck*B)
    V2 = pi_s*fy*d
    V3 = Mu*10**6

    As = (V2-(V2**2-4*V1*V3)**0.5)/(2*V1)
    return As

if __name__ == "__main__":
    print(cal_As(0.65, 0.9, 0.8, 0.4, 30, 400, 1000, 800, 80, 100))

이 코드는 아래와 같이 5개의 파트들로 구성을 가진다.

xlwings를 import하는 부분 : import xlwings as xw

테이블을 만들어주는 함수 : def reqAsTable():

엑셀 UDF를 정의하는 함수 : def udf_cal_As( ~ ):

필요철근량 구하는 함수 : cal_As( ~ ):

테스트를 위한 부분 : if __name__ ~

이 절에서는 VBA에서 파이썬 코드를 실행하는 내용이므로 reqAsTable() 함수를 실행하는 방법을 살펴보겠다. 엑셀에서 파이썬 코드를 실행하기 위해서는 VBA가 필요하다. VBA에서 파이썬 파일을 불러와 실행한다고 생각하면 된다.

엑셀의 [개발 도구]메뉴에서 [Visual Basic]을 선택하자. VBA 창이 뜰 것이다. 여기서 새로운 VBA코드를 추가하기 위해서 왼쪽의 프로젝트 탐색기에서 자신의 엑셀파일명에 오른쪽 마우스를 클릭한 후 [삽입(N)]을 선택하고 [모듈(M)]을 선택한다.

그러면 아래 그림처럼 새로운 모듈이 추가 된다. 모듈이름은 기본적으로 Module1으로 만들어진다. 이 모듈에 파이썬 코드를 실행하는 VBA 코드를 추가할 것이다. VBA를 잘 모른다고 해도 걱정할 필요 없다. 아주 단순하고 파이썬과 크게 다르지 않다.

화면의 오른쪽에 Module1의 소스코드를 입력할 수 있다. 아래의 코드를 입력하자. VBA코드인데 Sub은 파이썬의 def와 같은 개념이다. 마지막의 End sub은 함수 선언의 마지막임을 표시한다. 단 세줄이다.

Sub reqAsTable()
RunPython "import rcworks;rcworks.reqAsTable()"
End Sub

RunPython 다음에 파이썬 명령을 써주면 된다. import문으로 rcworks를 불러오고 rcworks.reqAsTable() 명령으로 reworks.py안에 있는 reqAsTable함수를 실행시키라는 명령이다. 두개의 명령이 ;로 구분되어있다. rcworks.py파일은 현재 엑셀파일이 있는 디렉터리와 xlwings.conf에서 설정한 PYTHONPATH에서 찾아서 import한다.

이제 파이썬 매크로를 실행할 준비는 끝났다. 매크로를 실행해보자. 매크로를 실행하는 방법은 엑셀의 [개발 도구]를 선택한 다음 [매크로]를 선택한다. 그러면 현재 파일에서 실행할 수 있는 매크로 리스트가 뜬다. VBA에 추가된 모듈들 안에 sub로 정의된 매크로들의 리스트를 보여준다.

reqAsTable 매크로를 선택한 다음 [실행(R)] 버튼을 누르면 매크로가 실행된다. 실행 결과는 아래 그림과 같다. 매크로를 실행하기 전에 선택되었던 셀에 테이블이 그려진다. A2 셀에서 매크로를 실행했기 때문에 A2 :K3 영역에 테이블이 만들어졌다.

아마도 여러분은 위 그림과 같은 결과가 나오지 않고 아래 그림과 같은 결과가 나왔을 것이다.

셀 주소 K3에 들어있는 내용을 확인해보면 아래 그림과 같이 제대로 수식이 물려 있는 것을 확인할 수 있다. 그런데 왜 #NAME?이라는 결과를 보여주는 것일까? 엑셀에서 #NAME? 에러는 그런 함수가 없다는 뜻이다.

분명 파이썬 파일(rcworks.py)에서 @xw.func 데코레이터로 def udf_cal_As() 함수를 감싸주었으니 제대로 작동해야 하는 것이 아닐까?

앞에서 ‘파이썬 코드만으로 실행되지 않고 VBA코드를 통해서 실행해야 한다’다고 했다. reqAsTable도 파이썬에서 정의하고 VBA에서도 정의했다. UDF도 마찬가지로 VBA에서도 정의해줘야 한다. UDF를 사용하는 방법은 다음 절에서 알아보도록 하겠다.

4. 사용자 정의 함수(UDF)

UDF를 정의하기 위해서는 파이썬 쪽에서 선언해주고 VBA 쪽에서도 선언해줘야 한다. 파이썬 쪽에서 선언하는 방법은 @xw.func 데코레이터로 감싸주었다.

@xw.func
def udf_cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu):
""" Calculate required As"""
return cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu)

def udf_cal_As( ):의 앞줄에 @xw.func라는 문장이 나온다. 이 부분이 바로 엑셀에게 UDF라는 것을 알려주는 부분이다. @로 시작하는 것은 데코레이터라고 한다. 그냥 다른 함수를 감싸주는 일종의 함수라고 생각하면 된다. udf_cal_As가 엑셀에서 사용할 함수 이름이다. def문 아래에 세개의 따옴표로 싸여진 문자는 함수의 설명이다. 엑셀에서 [수식]메뉴의 함수 삽입을 눌렀을 때 볼 수 있다.

VBA 코드를 추가해줘야 UDF가 제대로 작동한다. reqAsTable도 VBA에서 매크로를 추가했던 것을 기억하자. 현재의 엑셀 파일에서 파이썬으로 작성한 UDF를 사용하기 위해서는 다음과 같이 설정해주면 된다.

엑셀의 [xlwings] 메뉴를 눌러보자. Import Functions 버튼을 눌러주면 설정 끝이다.

그런데 이 기능을 사용하려면 한가지 설정을 더 해줘야 한다. [개발 도구]의 [매크로 보안]을 누른 다음 [개발자 매크로 설정] 의 ‘VBA 프로젝트 개체 모델에 안전하게 액세스할 수 있음’을 켜주자. Import Functions를 누르면 xlwings가 VBA프로젝트에 소스코드를 직접 추가하기 때문에 이것이 켜져 있어야 한다.

이제 [xlwings] 메뉴의 [Import Functions]를 눌러보자. 아무 일도 벌어지지 않는다. 그래도 등록은 끝났다. 이 버튼을 누르면 xlwings가 xlwings.conf파일에 있는 "UDF MODULES","rcworks" 설정에서 지정한 파일인 rcworks.py 파일을 찾아서 @xw.func로 감싸진 함수들을 찾아서 등록해준다. 어디다 등록을 하는 것일까? VBA를 열어보자.

import functions를 누르기 전에는 없었던 xlwings_udfs 모듈이 추가된 것을 알 수 있다. 이 모듈의 내용을 보자. 복잡하다. 우리가 이 코드의 내용을 알 필요는 없다. 대충 보면 Function udf_cal_As(~)으로 시작해서 End Function으로 끝났다. VBA로 UDF를 정의하는 방법이다.

Function udf_cal_As(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu)
    #If App = "Microsoft Excel" Then
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        udf_cal_As = Py.CallUDF("rcworks", "udf_cal_As", Array(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu), ThisWorkbook, Application.Caller)
        Exit Function
    #Else
        udf_cal_As = Py.CallUDF("rcworks", "udf_cal_As", Array(pi_c, pi_s, alpa, beta, fck, fy, B, H, dp, Mu))
        Exit Function
    #End If
failed:
    udf_cal_As = Err.Description
End Function

중간 쯤에 udf_cal_As = Py.CallUDF(“rcworks”, “udf_cal_As”, ~ ) 이런 부분이 있다. rcworks.py파일의 udf_cal_As 함수에 대해 정의하고 있는 것으로 보인다. 이 코드의 내용은 자세하게 알 필요는 없다. import functions버튼을 누르면 이 코드가 자동으로 생성되기 때문이다.

4. 맷음말

이번 시간에는 xlwings를 이용해서 엑셀의 VBA와 파이썬을 연동하는 방법을 알아봤다. 두 가지 기능을 알아봤는데 하나는 매크로고 하나는 사용자정의 함수(UDF)다. 매크로는 일괄작업을 하는 것이라서 파이썬 쪽에서 실행하는 것과 크게 다르지 않고 VBA쪽에서 sub로 매크로를 등록하고 파이썬 파일을 실행하는 개념이었다. UDF는 파이썬의 함수를 마치 엑셀의 함수인 것처럼 엑셀에서 사용하는 것이다. UDF는 매크로에 비해서 설정할 것이 좀 많다. 하지만 파이썬으로 만든 함수들을 엑셀에서도 쓸 수 있기 때문에 코드 통합이 가능하고 관리가 편할 것이다.4

-끝-

예제파일 : rcworks.zip
- rcworks.py는 C:\ESC에 복사
- py1.xlsm 파일은 원하는 아무 폴더에 복사 후 py1.xlsm파일을 열기.

추천하기

목록보기