VBA

Excel

Excel VBA 추가기능 리본 메뉴 만들기

Excel에서 Syntax Highlighting SQL

• 계산과 Screenupdating 안하기

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

열려 있는 모든 통합 문서   Application.Calculate (또는 Calculate)
특정 워크시트        Worksheets(1).Calculate
특정 범위        Worksheets(1).Rows(2).Calculate
Worksheets(""Sheet1"").UsedRange.Columns(""A:C"").Calculate

range("A1").FormulaR1C1 = "=TODAY()"
Range("F2") = Evaluate("SUM((B2:B60001=E2)*C2:C60001)")

• for i 보다 for each
• 자동채우기

Range("G2:O2").AutoFill Destination:=Range("G2:O" & Range("A" & Rows.Count).End(xlUp).Row)

http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/25/customuieditor.aspx

• 'Home' 탭에 리본메뉴 추가

<tab idMso="TabHome">
   <group id="customGroup"  insertAfterMso="GroupStyles" label="Remove Styles">
       <button id="customButton"  getLabel="GetButtonLabel" size="large" imageMso="CellStylesGallery"
         onAction="RemoveTheStyles" screentip="Remove Styles"
         supertip="Removes all but the default set of styles, any styles in use other than those will revert to using the Normal style."/>
   </group>
</tab>

• 리본메뉴 Dynamically 수정하기

'Callback for customButton getLabel
Sub GetButtonLabel(control As IRibbonControl, ByRef returnedVal)
  If ActiveWorkbook Is Nothing Then
      returnedVal = "Remove Styles"
  Else
      returnedVal = "Remove Styles" & vbCr & Format(ActiveWorkbook.Styles.Count, "#" & Application.International(xlThousandsSeparator) & "##0")
  End If
End Sub

• for i =1to10

Set Wf = WorksheetFunction
With Wf
  .함수들..
end with

• Error 처리 https://m.blog.naver.com/PostView.nhn?blogId=smilo73&logNo=220304995739&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F
• Create Sheet https://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets

 Private Sub CreateSheet()
   Dim ws As Worksheet
   With ThisWorkbook
       Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
       ws.Name = "Tempo"
   End With
 End Sub

• Does Sheet Exist? https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists

Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
  Dim sht As Worksheet
  If wb Is Nothing Then Set wb = ThisWorkbook
  On Error Resume Next
  Set sht = wb.Sheets(shtName)
  On Error GoTo 0
  WorksheetExists = Not sht Is Nothing
End Function

• Array Function 사용법 https://www.get-digital-help.com/how-to-use-the-array-function-vba/

• Optional Parameter Sub name(ByVal parameter1 As datatype1, Optional ByVal parameter2 As datatype2 = defaultvalue)

Dim DestinationPPT as string
Dim PowerPointApp as Object
Dim myPresentation as Object
Dim mySlide as Object

Set PowerPointApp = CreateObject("PowerPoint.Application")

DestinationPPT = "c:\...."
Set myPresentation = PowerpointApp.Presentations.Open(DestinationPPT)

Set mySlide = myPresentation.Slides(1)

Set tBox = mySlide.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, left:=30, top=30, Width:=20, Height:=20)
tBox.TextFrame.TextRange.Text = "Text"

With tBox.TextFrame.TextRange.ParagraphFormat.Bullet
  .Type = ppBulletUnnumbered
  .Font.Name = "Wingdings"
  .Character = bullet_-chr '252:checkmark, 113: Box,  167:filled square black
End With

• 목록 수정

With ActiveSheet.Shapes("이름").ControlFormat
  .ListFillRange = "범위"
  .LinkedCell = "셀"
  .DropDownLines = 개수
End With

https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project