이 문서에서 내가 당신을 보여주는 일부의 가장 놀라운 VBA Excel 코드를 사용할 수 있는 최적화하는 작업이다. VBA 는 MS Excel 및 기타 MS Office 응용 프로그램의 기능을 확장하는 데 사용할 수있는 프로그래밍 언어입니다. 작업을 자동화하고 효율성을 크게 향상시키는 데 사용할 수 있기 때문에 MS Excel 사용자에게 매우 유용합니다. 이 문서에서는 VBA 에 당신을 소개하고 거기에 vba 코드를 사용할 준비가 당신에게 가장 유용한의 일부를 보여줍니다. 이러한 매크로 예제를 사용하여 자신의 필요에 맞는 자체 스크립트를 만들 수 있습니다.

필요하지 않습 프로그래밍 경험을 활용하는 정보를 이 문서에서는,하지만 당신은 것으로 예상의 기본 지식을 가지고 Excel. 는 경우는 초급,사용자가하는 것이 좋습니다 기사를 읽 20Excel 식을 사용하여 시작해야에 대한 자세한 내용을 보려면 지금 Excel 의 핵심 기능입니다.

나는 당신을 위해 준비한의 번호를 사용할 준비가 VBA Excel 매크로 예으로 훌륭한 기능을 사용할 수 있는 최적화하는 작업이다. 그들을 사용하기 위해서는 엑셀 파일에”설치”해야합니다. 다음 단락은 Excel 매크로 설치를 다룹니다. 이미 이것에 익숙하다면이 부분을 건너 뜁니다.

매크로 설치 방법

Excel 에서 키 조합 alt+F11 을 누릅니다. 이렇게하면 MS Excel 의 VBA 편집기로 이동합니다. 그런 다음 왼쪽의 Microsoft Excel Objects 폴더를 마우스 오른쪽 버튼으로 클릭하고 Insert=>모듈을 선택하십시오. 이것은 매크로가 저장되는 장소입니다. 매크로를 사용하려면 Excel 문서를 매크로 사용으로 저장해야합니다. 탭에서 파일=>다른 이름으로 저장,매크로 사용 통합 문서로 저장을 선택(la.xlsm 확장)이제 첫 번째 매크로를 작성할 차례입니다!

1. 한 파일에서 다른 파일로 데이터를 복사하십시오.

매우 유용 매크로로에 복사하는 방법을 보여 줍니다 다양한 데이터 안쪽에서 vba 어떻게 만들고 새로운 통합 문서입니다. 자신의 요구 사항에 맞게 쉽게 업그레이드 할 수 있습니다:

하위 CopyFiletoAnotherWorkbook()'데이터 시트를 복사합니다("예제 1").범위("B4:C15").복사'새 통합 문서 통합 문서를 만듭니다.추가'데이터 활성 시트를 붙여 넣습니다.붙여 넣기'응용 프로그램 경고 응용 프로그램을 끕니다.DisplayAlerts=False'새로 파일을 저장하십시오. 디렉토리의 이름을 변경하십시오. ActiveWorkbook.저장 파일 이름:="C:\Temp\MyNewBook.xlsx"'응용 프로그램에 다시 응용 프로그램 경고를 켭니다.디스플레이 알러트=TrueEnd 하위

2. 숨겨진 행 표시

때때로 큰 Excel 파일에는 더 나은 명확성을 위해 숨겨진 줄이 포함되어 있습니다. 다음은 활성 워크 시트에서 모든 행을 숨기지 않는 매크로입니다.

Sub ShowHiddenRows()열.EntireColumn.숨겨진=거짓 행.엔 티레 로우.Hidden=FalseEnd Sub

3. 빈 행과 열 삭제

Excel 의 빈 행은 데이터 처리에 문제가 있습니다. 다음은 그것들을 제거하는 방법입니다.

Sub DeleteEmptyRowsAndColumns()'변수를 선언하십시오. 범위로 희미한 myrange 희미한 iCounter 만큼'목표 범위를 정의합니다. MyRange=ActiveSheet 을 설정합니다.UsedRange'행의 범위를 통해 역 루핑을 시작합니다. ICounter=MyRange 의 경우.행.전체 행이 비어 있으면 1 단계 -1'로 계산 한 다음 삭제하십시오. 응용 프로그램의 경우.CountA(행(iCounter).그런 다음 행(iCounter).삭제 제거'의견을 볼 수 있는 빈 행을'Ek"row"&iCounter&"비"End If'증가 카운터 아래로 다음 iCounter'6 단계:작가 역을 반복을 통해 범위의 열이 있습니다. ICounter=MyRange 의 경우.열.1 단계 -1'7 단계로 계산:전체 열이 비어 있으면 삭제하십시오. 응용 프로그램의 경우.CountA(열(iCounter).그런 다음 열(iCounter)을 열 수 있습니다.'8 단계'인 경우 종료 삭제: 다음 iCounter 끝 Sub

4 아래로 카운터를 증가시킵니다. 빈 셀 찾기

하위 FindEmptyCell()ActiveCell.오프셋(1,0).Activecell(ActiveCell)ActiveCell 이 아닌 동안 Do 를 선택하십시오.오프셋(1,0).LoopEnd Sub

5 를 선택하십시오. 빈 셀을 값으로 바꿉니다.

앞에서 언급했듯이 빈 셀은 데이터 처리 및 피벗 테이블 생성을 방해합니다. 다음은 모든 빈 셀을 0 으로 바꾸는 코드입니다. 이 매크로가 매우 큰 응용 프로그램을 사용할 수 있기 때문에 그것을 찾을 수 있고 교체 N/A 결과,뿐만 아니라 다른 문자와 같은 점을 쉼표로 구분 또는 중복되는 값:

Sub FindAndReplace()'변수를 선언하 Dim MyRange 으로 범위 Dim MyCell 으로 범위'통합 문서를 저장하기 전에 변경 셀? Case MsgBox("이 작업을 실행 취소할 수 없습니다. "&_"통합 문서를 먼저 저장 하시겠습니까?",vbYesNoCancel)Case 는=vbYes ThisWorkbook 입니다.Save Case Is=vbcancel Exit Sub End Select'대상 범위 정의. MyRange=Selection'범위를 통해 루핑을 시작하십시오. MyRange'의 각 MyCell 에 대해 0 길이를 확인한 다음 0 을 추가하십시오. 렌 경우(MyCell.값)=0 그런 다음 MyCell=0 끝 If'다음 Mycellnd Sub

6 범위의 다음 셀을 가져옵니다. 정렬 번호

다음 매크로는 활성 셀의 열에서 모든 숫자를 오름차순으로 정렬합니다. 정렬하려는 열의 셀을 두 번 클릭하기 만하면됩니다.주의:코드를 시트 1 에 넣고 작동 시키려면 모듈이 아닙니다:

Private Sub Worksheet_BeforeDoubleClick(ByVal 이름으로 대상 범위를 취소 Boolean)'변수를 선언하 Dim LastRow 으로 길을 찾아'마지막 행 LastRow=세포(행이 있습니다.카운트,1).끝(xlUp).두 번 클릭 한 열 행에서 행'정렬 오름차순("6:"&LastRow).정렬_키 1:=셀(6,ActiveCell.열),_Order1:=xlAscendingEnd Sub

7. 제 빈 공간

때때로,데이터 통합 문서에 포함되는 추가 공간(공간)방해할 수 있는 데이터 분석 및 손상의 수식에 대해 설명합니다. 여기에 매크로를 제거하는 모든 공간에서 선택된 셀 범위:

Sub TrimTheSpaces()'변수를 선언하 Dim MyRange 으로 범위 Dim MyCell 으로 범위를 저장을 통합 문서를 변경하기 전에 세포를 선택하는 경우 마이크로소프트("취소 할 수 없습니다. "&_"통합 문서를 먼저 저장 하시겠습니까?",vbYesNoCancel)Case 는=vbYes ThisWorkbook 입니다.Save Case Is=vbcancel Exit Sub End Select'대상 범위 정의. MyRange=Selection'범위를 통해 루핑을 시작하십시오. MyRange 의 각 MyCell 에 대해'공간을 다듬습니다. IsEmpty(MyCell)가 아닌 경우 MyCell=Trim(MyCell)End If'다음 MyCellEnd Sub

8 범위의 다음 셀을 가져옵니다. 강조 dublicated 값

때때로 우리가 조명하고자하는 여러 열에 중복 값이 있습니다. 여기에는 매크로가:

Sub HighlightDuplicates()'변수를 선언하 Dim MyRange 으로 범위 Dim MyCell 으로 범위를 정의 대상 범위에 있습니다. MyRange=Selection'범위를 통해 루핑을 시작하십시오. MyRange 의 각 MyCell 에 대해'셀에 텍스트 서식이 있는지 확인하십시오. 경우 WorksheetFunction.CountIf(MyRange,MyCell.값)>1 그런 다음 MyCell.인테리어.ColorIndex=36End If'다음 MyCellEnd Sub

9 범위의 다음 셀을 가져옵니다. 하이라이트 최고 열 값

이 코드는 것입니다 하이라이트상에서 값을 선택한 세포의:

Sub 플러()선택합니다.형식조건.AddTop10 선택.FormatConditions(선택.형식조건.카운트).선택과 설정 Firstpriority.형식 조건(1).TopBottom=xlTop10Top'다른 수의 값을 강조 표시하려면 여기에서 순위를 변경하십시오.순위=10.%=False End With With Selection.형식 조건(1).글꼴.컬러=-16752384.TintAndShade=0 끝 With With Selection.형식 조건(1).인테리어.PatternColorIndex=xlAutomatic.색상=13561798.TintAndShade=0 선택으로 끝납니다.형식 조건(1).StopIfTrue=FalseEnd Sub

다른 수의 값을 강조하기 위해 코드를 쉽게 조정할 수 있습니다.

10. 값보다 큰 강조 표시

이 코드를 실행하면 창이 프롬프트됩니다. 그것은 당신에게 당신이 선택한 셀을 비교하고자하는 값을 요청합니다.

하위 HighlightGreaterThanValues()Dim I As Integer i=InputBox("값보다 큰 입력","값 입력")선택.형식조건.삭제'연산자를 xllower 로 변경하여 값보다 낮은 선택을 강조 표시합니다.형식조건.추가 유형:=xlCellValue,연산자:=xlGreater,Formula1:=I 선택.FormatConditions(선택.형식조건.카운트).선택과 설정 Firstpriority.형식 조건(1).글꼴.색상=RGB(0,0,0).인테리어.Color=RGB(31,218,154)End WithEnd Sub

이 코드를 조정하여 더 낮은 값을 강조 표시 할 수 있습니다.

주석을 포함하는 모든 셀을 강조 표시하는 간단한 매크로:

하위 HighlightCommentCells()선택.SpecialCells(xlCellTypeComments).선택을 선택하십시오.Style="Note"End Sub

12. 하이라이트 세포 단어의 철자를

이것은 매우 유용할 때와 작동하는 기능을 문자열을,그러나,누군가가 입력 문자열과 함께 실수의 수식은 작동하지 않습니다. 이 문제를 해결하는 방법은 다음과 같습니다.

ActiveSheet 의 각 cl 에 대해 하위 Colormispellledcells().응용 프로그램이 아닌 경우 사용하십시오.CheckSpelling(단어:=씨엘.텍스트)다음_cl.인테리어.ColorIndex=28 다음 clEnd Sub

13. 피벗 테이블 만들기

다음은 MS Excel(2007 버전)에서 피벗 테이블을 만드는 방법입니다. 특히 매일 맞춤 보고서를 작성할 때 유용합니다. 다음과 같은 방법으로 피벗 테이블 생성을 최적화 할 수 있습니다.

Sub PivotTableForExcel2007()Dim SourceRange As Range Set SourceRange=Sheets("Sheet1").범위("A3:N86")ActiveWorkbook.피벗 캐시.그러나 나는 이것이 내가 할 수있는 유일한 방법이라고 생각한다.CreatePivotTable_TableDestination:="",_테이블:="",_DefaultVersion:=xlPivotTableVersion12End Sub

14. 이메일

내가 좋아하는 VBA 코드에 활성 통합 문서를 첨부하십시오. 그것은 당신이 첨부하고 미리 정의 된 이메일 주소,메시지 제목 및 메시지 본문으로 작업중인 파일을 보낼 수 있습니다! 먼저 microsoft Outlook 에 대한 참조를 설정해야합니다(VBA editior 에서 tools=>참조를 클릭하고 Microsoft Outlook 을 선택하십시오).

Sub SendFIleAsAttachment()'declare your variables'Outlook 으로 microsoft Outlook 개체 라이브러리 Dim OLApp 에 대한 참조를 설정합니다.응용 프로그램 희미한 Olmail 객체로'Outlook 열기 시작 새 메일 항목 설정 OLApp=새 Outlook.응용 프로그램 설정 OLMail=OLApp.CreateItem(0)OLApp.세션.Logon'을 구축하는 메일 항목과 함께 보내 OLMail.을="[email protected];[email protected]".CC="".숨은 참조="".제목="이것은 제목 줄"입니다.몸="안녕하세요".첨부 파일.ActiveWorkbook 추가.FullName.표시'로 변경.'메모리 정리 Set OLMail=Nothing Set OLApp=NothingEnd Sub

15 로 끝을 검토하지 않고 보내려면 보내십시오. 모두 보내 Excel 차트 파워 포인트 프리젠 테이션

매우 편리한 매크로를 추가할 수 있는 모든 Excel 차트 Powerpoint 프레젠테이션에 단 한 번의 클릭으로.

Sub SendExcelFiguresToPowerPoint()'설정 참조하여 Microsoft Powerpoint 개체 라이브러리'변수를 선언하 Dim PP 으로 파워포인트가 있습니다.파워 포인트로 응용 프로그램 희미한 PPPres.파워 포인트로 프리젠 테이션 희미한 PPSlide.슬라이드 Dim I As Integer'check for charts; 차트가 시트가없는 경우 종료하십시오("슬라이드 데이터").ActiveSheet 인 경우 선택하십시오.ChartObjects.Count<1 그런 다음 MsgBox"활성 시트를 기존 차트 없음"종료 하위 끝 If'PowerPoint 를 열고 새 프리젠 테이션 세트 PP=새 PowerPoint 를 만듭니다.응용 프로그램 세트 PPPres=PP.프리젠 테이션.PP 를 추가하십시오.Visible=True'I=1 에서 ActiveSheet 에 대한 차트 수를 기반으로 루프를 시작하십시오.ChartObjects.카운트'그림 활동 시트로 차트를 복사합니다.나는 이것을 할 수 없다.차트.CopyPicture_크기:=xlScreen,형식:=xlPicture 응용 프로그램.대기(지금+TimeValue("0:00:1"))'슬라이드를 세고 새 슬라이드를 사용 가능한 다음 슬라이드 번호 ppSlideCount=PPPres 로 추가하십시오.슬라이드.카운트 세트 PPSlide=PPPres.슬라이드.추가(SlideCount+1,ppLayoutBlank)PPSlide.선택'사진을 붙여 위치를 조정;다음 차트 PPSlide 로 이동합니다.모양.붙여 넣기.PP 를 선택하십시오.ActiveWindow.선택.ShapeRange.정렬 msoAlignCenters,True PP.ActiveWindow.선택.ShapeRange.정렬 msoAlignMiddles,True Next I'메모리 정리 세트 PPSlide=Nothing Set PPPres=Nothing Set PP=NothingEnd Sub

16. MS Word 에서 Excel 테이블 보내기

Excel 테이블은 일반적으로 텍스트 문서 안에 넣습니다. 여기는 자동화된 방식으로 내보내 Excel 표 MS Word:

Sub ExcelTableInWord()'설정 참조를 개체 라이브러리'변수를 선언하 Dim MyRange 니다.범위 희미한 wd 단어로.Word 로 응용 프로그램 희미한 wdDoc.Word 로 문서 희미한 WdRange.범위'정의된 범위 시트("수익 테이블")를 복사합니다.범위("B4:F10").Cop'대상 Word 문서 세트 wd=New Word 를 엽니 다.응용 프로그램 세트 wdDoc=wd.문서.오픈_(ThisWorkbook.나는 이것이 내가 할 수있는 유일한 방법이라고 생각한다.docx")wd.Visible=True'대상 북마크 세트 WdRange=wdDoc 에 초점을 설정합니다.북마크("DataTableHere").범위'이전 테이블을 삭제하고 오류 이력서 다음 WdRange 에 새 붙여 넣습니다.테이블(1).WdRange 를 삭제합니다.붙여 넣기'테이블에 붙여 넣기'열 너비 WdRange 를 조정합니다.테이블(1).열.SetWidth_(MyRange.폭/MyRange.열.이 응용 프로그램은 즐겨 찾기 wdDoc 을 다시 삽입 할 수 있습니다.북마크.추가"DataTableHere",WdRange'메모리 정리 세트 wd=아무것도 설정 wdDoc=아무것도 설정 WdRange=NothingEnd 하위

17. 셀에서 특정 단어 추출

특정 수의 기호를 추출하려는 경우 수식을 사용할 수 있습니다. 그러나 우리가 문장이나 셀의 단어 범위에서 두 번째 단어 만 추출하고 싶다면 어떨까요? 이렇게하려면,우리는 vba 와 사용자 정의 엑셀 함수를 만들 수 있습니다. 이것은 당신이 MS Excel 에 존재하지 않는 자신의 함수를 만들 수 있기 때문에,가장 iportant VBA 기능 중 하나입니다. Findword()와 findwordrev()의 두 가지 함수를 만들어 보겠습니다. 이것에 대한 vba 코드는 다음과 같습니다:

함수 FindWord(Source As String,Position As Integer)오류 재개시 문자열로 다음 FindWord=Split(WorksheetFunction.Trim(Source),"")(Position-1)On Error GoTo0End FunctionFunction FindWordRev(Source As String,Position As Integer)As String Dim Arr()As String Arr=VBA.분할(WorksheetFunction.Trim(Source),"")On Error 이력서 다음 FindWordRev=Arr(UBound(Arr)-Position+1)On Error Goto0End Function

아주 좋은,우리는 두 개의 cstom Excel 함수를 만들었습니다. 이제 Excel 에서 사용하려고합니다. 함수=FindWordRev(A1,1)는 A1 셀에서 마지막 단어를 가져옵니다. 함수=FindWord(A1,3)는 셀 A1 등에서 세 번째 단어를 가져옵니다.

18. 통합 문서를 보호

때때로 우리는 단지 우리가 그것을 변경할 수 있도록 우리의 파일의 데이터를 보호 할. 를 수행하는 방법은 다음과 같이 VBA:

Sub ProtectSheets()'변수를 선언하 Dim ws 으로 워크시트를 시작'루프를 통해 모든 워크시트에 대한 각 ws 에 ActiveWorkbook.워크 시트의 보호 및 다음 워크 시트 ws 에 루프.비밀번호 보호:="1234"다음 wsEnd Sub

축하합니다! 당신은 여전히 이것을 읽고 있기 때문에,당신은 vba 를 배우는 것에 정말로 열중하고 있습니다. 당신은 이미 자신을 위해,VBA 프로그래밍 언어의 매우 유용하고 저장할 수 있습 우리에게 많은 시간입니다. 난 당신이 도움이 정보를 발견하고 일반적으로 MS 엑셀,VBA 및 컴퓨터 소프트웨어의 마스터가되기 위해 그것을 사용 바랍니다.