working with shapes
TRANSCRIPT
![Page 1: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/1.jpg)
WORKING WITH SHAPESVBA-EXCEL
![Page 2: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/2.jpg)
Variant
Vertices
Group Items
HEIGHT WIDTH1
2
Z-Order Position
Item
Array of coordinate points
R Add CurveAdd Polyline
¿Vs. Nodes
Shape Nodes collection
![Page 3: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/3.jpg)
Variant
Vertices
Group Items
HEIGHT WIDTHH
W
12
Z-Order Position
Item
Array of coordinate points
R Add CurveAdd Polyline
¿Vs. Nodes
Shape Nodes collection
Points Values
AB C
Objective
![Page 4: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/4.jpg)
Objective
.Name.
Sets a String Value
.representing name of object
LINE /LINE FORMAT
AjustesShape Range Object
Shape Determinations
< [[PROPERTIES] ]hitherto
![Page 5: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/5.jpg)
Objective
OLE
SHAPE RANGE CK.
ie. Dwgs +> .jpgs
.gifs
PICTURE FORMAT
< [[PROPERTIES] ]hitherto
![Page 6: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/6.jpg)
Objective
OLE
SHAPE RANGE CK.
ie. Dwgs +> .jpgs
.gifs
PICTURE FORMAT
AjustesShape Range Object
< [[PROPERTIES] ]hitherto
Shape Stylefrom
MSOShapeStyleIndex
Loop Shapes verify Lock Aspect Ratio
Unless applying adjustable Shape scenario
ShapeTypeMSOShapeType
![Page 7: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/7.jpg)
Objective
Alternative Text
{for Shapes}
| Shape Range |
WEBPAGEBackground Style
< [[PROPERTIES] ]hitherto
“Build Free From”Add Notes Common to Shape
E n d W i t h
Collection of Shape Objects{Shapes Object Members}
W i t h FreeFormBuilder d e c y D o c u m e n t . S h a p e s . B F F ( m s o E d i t i n g C o r n e r 3 6 0 , 2 0 0 )
Ie. .AddNodes msoSegmentCurve, msoEditingCorner,_380,230,400,250 etc.
.ConvertToShape
![Page 8: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/8.jpg)
Objective
Align: aligns shapes in a specified range.
METHODS
Regroup – Shapes become Shape Object
[group has [Z-order]
.Item returns single object from a collection
Increment | RotationIncrement | Left
| Top
S e l e c t s t h e o b j e c t s e t S h a p e s D e f f a u l t P r o p e r t i e s
Apply |{ }| [Pick Up] Method.
Formatting Copy-paste to shapes
Scale heightScale Width
Distribute Range Shapes
![Page 9: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/9.jpg)
Objective
MyDocument.Shapes.item(i).Type = msoAutoShape
____
msoShapeType Enumeration
SHAPESShapeShapes
.Count
.Item
Properties xOn Action shape xNodes
xOn Action
Shape xControlFormat.LockAspectRatio
.Child
.ParentGroup
MethodxSetShapes Default Properties
xReroute ConnectionsxIncrementLeftxIncrementRotation
.PickUp
.ScaleWidth
.ScaleHeight
.
Shape Object Members
![Page 10: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/10.jpg)
On Nodes.Count as MyDocument.Shapes(3).Nodes
groupProperty
msoEditingType
REDIM
H W
Q: ¿ .Count is a Static Value ?A: It is a read only property of a Collection
Reallocate storage space for dynamic array variables.
VBA Checks through each node to verify type of “condition” and assigns “quality”.
¿Vs. Nodes
Shape Nodes collection
Shape Node .EditingType ‘read only.’
msoEditing /corner .SetEditing TypemsoEditing Smooth
¿free form dwg?
![Page 11: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/11.jpg)
On Nodes.Count as MyDocument.Shapes(3).Nodes
groupProperty
msoEditingType
REDIM
H W
VBA Checks through each node to verify type of “condition” and assigns “quality”.
¿Vs. Nodes
Shape Nodes collection
msoEditing /corner .SetEditing TypemsoEditing Smooth
¿free form dwg?
[ On.Distribute( _ , _ )
Let myDocument = Worksheets(1)
With myDocument.Shapes
nomShapes = .Count
If numShapes > 1 Then
numAutoShapes = 0 { initialize }{ReDim autoShpArray.(1 to (.Count)numShapes)}
For i = 1 to numShapesif .item(i).Type = msoAutoShape then…
dynamic array
![Page 12: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/12.jpg)
Objective
Test each shape
Set Method
apply Property
End Test
End Loop
3 OBJECTSSHAPES COLLECTION: SHAPE RANGE COLLECTION: SHAPE OBJECT
Loop through collection Shape Range
Shapes are not confined by Excel Versions previous to ¿’07 to ‘010
![Page 13: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/13.jpg)
Objective
Test each shape
Set Method
apply Property
End Test
End Loop
SHAPE RANGE COLLECTIONCONTAINS ALL SHAPES ON A SHEET
With ShapeRange Property ╘ return ShapeRange object containing selected shapes.
Worksheets(1).Shapes.Select
Set sr = selection.ShapeRange
Index
create a ShapeRange Object containing all shapes in a Shape collection.
H W
![Page 14: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/14.jpg)
Objective
Shapes defined for the index of worksheets. So re-define worksheet See i) myDocument = worksheets(1)
for Each Sh In myDocument.Shapes
{actions applied to sh. property. }
2010 DEVELOPER REFERENCE“WORKING WITH SHAPES”
Applying a Property Or a Method to a ShapeRange Collection
<<((hypothesize))>>Exterior
looping through worksheets
ie. MyDocument = Workbook(“ “)
For each Wksht In
MyDocument.Wksht
Wksht.Selection.ShapeRange
![Page 15: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/15.jpg)
Objective
“ . “ Title. Text = “___” & vbcr & “___” &_
Iif ( Range( “units” ) = “___”, “___”, “___”, “___” )
ActiveChart.Deselect.
‘’ACTIVE
[ Active Sheet.ChartObjects(“ChartName”).Activate ]
Shapes are not confined by Excel Versions previous to ¿’07 to ‘010
ThisWorkbook.Path.
&” \___.g.b ” etc.
Created an image 1
![Page 16: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/16.jpg)
Range
Evaluate changing variables
{Single formula}
Objective
‘’ACTIVE
Autofill create list of ordered numbers.
Generate in vba to paste values in column.
ie. No formulas for “data tables”in first column.
Ref; Susan Harkins | Tech Republic
{ if error function } for distributable
Once data table is in place; can’t change any referencing cells. (B,C,+D in data table)
Data table are a series of =TABLE() functions
Data Table
Result
Enter data table labels
Verify first column variable matches.Input cell amount.
Input of first row from result cells.
Go to “what if and …”
In Data Tools group.Column input = input
cell .Value.$R$C
![Page 17: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/17.jpg)
Objective
Returns Value of Table or Range
INDEX
Worksheet.Index(Arg 1, Arg 2, Arg 3, Arg 4)
ThisWorkbook.Path.
&” \___.g.b ” etc.
Arg 1 Range or Array const. ref. one or more cell ranges.
Arg 2 Row_num
Arg 3 Column_num
Arg 4 Area_num
if omitted, range of, area_num is returnedIf row or column are omitted, column or row are
returned
Purpose ie Wood Tables
‘The result of the INDEX function is a reference ex.:CELL(“width”,…) ‘
![Page 18: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/18.jpg)
Objective
[ relative position in an array. (Value1, Value2, Value3) ]Vs. Lookup for position vs. Range item eg.
MATCH
Worksheet.Match(Arg 1, Arg 2, Arg 3)
eg. Order 1,2,3,example
Arg 1 Lookup_Value
Arg 2 Row_num
Arg 3 Match_type
Using number or text etc. or cell reference.
Lookup_array: contiguous range of cells. Containing possible lookup values.Lookup_array : array or reference.Match_type -1, 0, or 1 {matching ARG 1} how (min ≥) = (≤ max)
A B
8
9
14 16
20 40
<<((paraphrase))>> Match A8 -> A8 vs. Lookup A8 ->14
![Page 19: Working with shapes](https://reader035.vdocuments.us/reader035/viewer/2022062406/55bfe878bb61eb825c8b4630/html5/thumbnails/19.jpg)
Excel No. False
Input eg.
Selection.ShapeRange.Align msoAlignLefts,False
Methods of Shapes in Shape Range
Align
Distribute
Group
Regroup
ALIGN
Colombia Thailand Cambodia
Align expression.Align (Align.cmd,Relative To) msoAlignCmd
Align Cmd
Relative To
“Arrange all shapes in the specified range with left edge of left most shape in the range.”
Reference
Distribute
With{Define “Visual groups (vbgroups)”
Methods