XL Personal.xlsb Macro

20220509 2201

REF
[1] "https://support.microsoft.com/en-us/office/create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790"
[2] "https://www.ablebits.com/office-addins-blog/2020/03/04/excel-personal-macro-workbook/"

 

SEFL REF

[1] https://weneedu.tistory.com/660, VBA kitchen utils

Outline
Personal.xlsb. That’s a hidden workbook stored on your computer, which opens in the background every time you open Excel. [1]

How to create Personal Macro Workbook in Excel [2]
Developer > Record Macro

 

Where is it

Your Personal.xlsb file is stored in a folder called XLSTART. [1]

⟼ everything으로 "XLSTART"를 검색해봐도 안나온다.
personal.xlsb에 다음 매크로를 추가하고 실행해보면 경로를 알 수 있다.

Sub zWhere_is_personal_xlsb() 
 MsgBox ThisWorkbook.Path
End Sub

결과는 "C:\Users\kdbne\AppData\Roaming\Microsoft\Excel\XLSTART"

 

파일은 아래 첨부

PERSONAL.XLSB

0.02MB

here's codes

Sub zMakeRoundBoxes() Dim shp1 As Shape ztxt = ActiveCell.Value For i = 1 To Selection.Areas.Count     x1 = Selection.Areas.Item(i).Left     w1 = Selection.Areas.Item(i).Width     y1 = Selection.Areas.Item(i).Top     h1 = Selection.Areas.Item(i).Height     '''add box     Set s1 = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, x1, y1, w1, h1)     's1.Fill.Visible = msoFalse 'unfill the interior of boxes     s1.TextFrame.Characters.Text = ztxt     s1.TextFrame.HorizontalAlignment = xlHAlignCenter     s1.TextFrame.VerticalAlignment = xlVAlignCenter Next i End Sub

Sub zMakeOval() Dim shp1 As Shape For i = 1 To Selection.Areas.Count     x1 = Selection.Areas.Item(i).Left     w1 = Selection.Areas.Item(i).Width     y1 = Selection.Areas.Item(i).Top     h1 = Selection.Areas.Item(i).Height     Set shp1 = ActiveSheet.Shapes.AddShape(msoShapeOval, x1, y1, w1, h1)     shp1.Fill.Visible = msoFalse 'unfill the interior of boxes Next i End Sub

Sub zConnectTwoCells_without_box_straightLine() 'beg x1 = Selection.Areas.Item(1).Left w1 = Selection.Areas.Item(1).Width y1 = Selection.Areas.Item(1).Top h1 = Selection.Areas.Item(1).Height 'end x2 = Selection.Areas.Item(2).Left y2 = Selection.Areas.Item(2).Top w2 = Selection.Areas.Item(2).Width h2 = Selection.Areas.Item(2).Height 'if beg is left of end If (x1 < x2) Then   ActiveSheet.Shapes.AddConnector(msoConnectorStraight, x1 + w1, y1 + h1 / 2, x2, y2 + h2 / 2).Select Else   ActiveSheet.Shapes.AddConnector(msoConnectorStraight, x1, y1 + h1 / 2, x2 + w2, y2 + h2 / 2).Select End If With Selection.ShapeRange.Line   .BeginArrowheadStyle = msoArrowheadNone   .EndArrowheadStyle = msoArrowheadOpen   '.Weight = 1.75   .ForeColor.RGB = RGB(0, 0, 0) End With End Sub

Sub kcDrawHLine() Dim Obj As Shape L1 = Selection.Cells(1).Left cnt = Selection.Columns.Count L2 = Selection.Cells(cnt).Left + Selection.Cells(cnt).Width 'right T1 = Selection.Cells(1).Top + Selection.Cells(1).Height / 2 Set Obj = ActiveSheet.Shapes.AddLine(L1, T1, L2, T1) With Obj.Line   .Weight = 3   .EndArrowheadStyle = msoArrowheadTriangle   .EndArrowheadLength = msoArrowheadLengthMedium   .EndArrowheadWidth = msoArrowheadWidthMedium End With End Sub

 

Sub zConnectTwoCells_with_box_and_kinked_line() zx = TypeName(Selection) 'rectangle 'zx = Selection.ShapeRange.ShapeStyle 'zx = Selection.ShapeRange.Type     Dim shp1 As Shape     Dim shp2 As Shape If zx = "Range" Then     x1 = Selection.Areas.Item(1).Left     w1 = Selection.Areas.Item(1).Width     y1 = Selection.Areas.Item(1).Top     h1 = Selection.Areas.Item(1).Height          x2 = Selection.Areas.Item(2).Left     y2 = Selection.Areas.Item(2).Top     w2 = Selection.Areas.Item(2).Width     h2 = Selection.Areas.Item(2).Height     '''add box     Set shp1 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x1, y1, w1, h1)     Set shp2 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x2, y2, w2, h2)          shp1.Fill.Visible = msoFalse     shp2.Fill.Visible = msoFalse Else     x1 = Selection.ShapeRange.Item(1).Left     w1 = Selection.ShapeRange.Item(1).Width     y1 = Selection.ShapeRange.Item(1).Top     h1 = Selection.ShapeRange.Item(1).Height          x2 = Selection.ShapeRange.Item(2).Left     y2 = Selection.ShapeRange.Item(2).Top     w2 = Selection.ShapeRange.Item(2).Width     h2 = Selection.ShapeRange.Item(2).Height          Set shp1 = Selection.ShapeRange.Item(1)     Set shp2 = Selection.ShapeRange.Item(2) End If Set conn = ActiveSheet.Shapes.AddConnector(msoConnectorElbow, x1, y1, x2, y2) With conn.ConnectorFormat   conn.Line.ForeColor.RGB = RGB(0, 0, 255)   conn.Line.EndArrowheadStyle = msoArrowheadTriangle 'BeginArrowheadStyle   conn.Line.EndArrowheadLength = msoArrowheadLong   .BeginConnect ConnectedShape:=shp1, ConnectionSite:=2 '1=up, 2=left, 3=down, 4= right   .EndConnect ConnectedShape:=shp2, ConnectionSite:=1   conn.RerouteConnections 'optimization End With End Sub

Sub kcDrawHLine_reverse() Dim Obj As Shape L1 = Selection.Cells(1).Left cnt = Selection.Columns.Count L2 = Selection.Cells(cnt).Left + Selection.Cells(cnt).Width 'right T1 = Selection.Cells(1).Top + Selection.Cells(1).Height / 2 Set Obj = ActiveSheet.Shapes.AddLine(L2, T1, L1, T1) With Obj.Line   .Weight = 3   .EndArrowheadStyle = msoArrowheadTriangle   .EndArrowheadLength = msoArrowheadLengthMedium   .EndArrowheadWidth = msoArrowheadWidthMedium End With End Sub Sub kcDrawVLine() Dim Obj As Shape x1 = Selection.Cells(1).Left + Selection.Cells(cnt).Width / 2 cnt = Selection.Rows.Count x2 = x1 y1 = Selection.Cells(1).Top y2 = Selection.Cells(cnt).Top + Selection.Cells(cnt).Height 'down Set Obj = ActiveSheet.Shapes.AddLine(x1, y1, x2, y2) With Obj.Line   .Weight = 3   .EndArrowheadStyle = msoArrowheadTriangle   .EndArrowheadLength = msoArrowheadLengthMedium   .EndArrowheadWidth = msoArrowheadWidthMedium End With End Sub Sub kcDrawVLine_reverse() Dim Obj As Shape x1 = Selection.Cells(1).Left + Selection.Cells(cnt).Width / 2 cnt = Selection.Rows.Count x2 = x1 y1 = Selection.Cells(1).Top y2 = Selection.Cells(cnt).Top + Selection.Cells(cnt).Height 'down Set Obj = ActiveSheet.Shapes.AddLine(x2, y2, x1, y1) With Obj.Line   .Weight = 3   .EndArrowheadStyle = msoArrowheadTriangle   .EndArrowheadLength = msoArrowheadLengthMedium   .EndArrowheadWidth = msoArrowheadWidthMedium End With End Sub Sub zMakeBoxes() Dim shp1 As Shape For i = 1 To Selection.Areas.Count     x1 = Selection.Areas.Item(i).Left     w1 = Selection.Areas.Item(i).Width     y1 = Selection.Areas.Item(i).Top     h1 = Selection.Areas.Item(i).Height     '''add box     Set shp1 = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x1, y1, w1, h1)     shp1.Fill.Visible = msoFalse 'unfill the interior of boxes Next i End Sub