In Excel, I was able to find the names/picture numbers for the first two shapes by trial and error. Then i added a third shape and unable to find the number; up to Number 35 and am tired of this BS. Is there a way of finding it so i can use it? As in ActiveSheet.Shapes().Select ?
Don't know. I do not remember how i created the first 2 "shapes"; the third one is a copy/paste and manual resize.
Array? What array? Does Excel automatically create an array and put these in it? Ditto for "range.
I think i found that ActiveSheet.Shapes(1).Select exists, and no other. I know that ActiveSheet.Shapes("Picture 6").Select works, and that ActiveSheet.Shapes("Picture 11").Select also works. What s irking is that anything else creates an error and the Excel OnError does NOT detect that error. If it did,a loop counting from 1 to (say) 999 MIGHT find that third item.
That first "example" looks like one is creating an array arShapes, but where in the hell did those names come from, what does each element look like (do not trust names), and where did they come from?
I suppose i could have 3 GIF files, and Insert From File to successive cells; note no "range" command used, no array foo, and certainly no names.
This business is less clear than oily mud.
** My relevant notes in the macro is: 'With myDocument.Shapes.BuildFreeform(msoEditingCorner, 260, 150) ' .AddNodes msoSegmentLine, msoEditingAuto, 400, 150 ' .AddNodes msoSegmentLine, msoEditingAuto, 400, 180 ' .AddNodes msoSegmentLine, msoEditingAuto, 260, 180 ' .AddNodes msoSegmentLine, msoEditingAuto, 260, 150 ' .ConvertToShape 'End With ' Have rectangle where i want the text ' Select that new shape... ' 'iobj.1 'for output display ' 'iobj.2 'vertical jumper ' 'iobj.3 'horizontal jumper ' 'iobj.4 'smaller jumper
Range of shapes? when one cannot be eXplicitly be found? Not an option.
Oh, but i did; made some changes, and deleted some to create/insert others. Am thinking this latest addition is way out there, like Picture
75 as a guess.
There is absolutely no way to access these directly, much less in order. After a lot of messing around, this was only reliable result:
pic = 1 ' or object but NOT picture ' 1=vert; 2=horiz; 3=frame; 4=small; 5=title For Each iobj In ActiveSheet.Shapes 'DrawingObjects iobj.Select If pic = 2 Then Selection.Copy 'get horizontal jumper End If pic = pic + 1 Next iobj
For removal of stuff/objects added by program:
Klear: 'Erase jumpers for next try ' 1=vert; 2=horiz; 3=frame; 4=small; 5=title num = 1 For Each iobj In ActiveSheet.Shapes 'DrawingObjects iobj.Select If num > 4 Then iobj.Delete End If num = num + 1 Next iobj Close
Note my objects 1-4 are reference for copy/paste operations.
You seem not to understand any of the internal Excel structures that you are trying to manipulate. I have a bit of sympathy it can look a little bit like the maze in Zork if you don't understand the architecture.
It is hard to tell what your convoluted clueless clutter is trying to do but the following short script will show you all the objects names and might if you have even two brain cells give you an idea how to proceed.
Sub test() i = 0 For Each s In ActiveSheet.Shapes i = i + 1 Debug.Print i, s.Name, s.Type, s.Type = msoPicture Next s End Sub
Your code would look a lot less clumsy if you used Shapes.Count and a for loop. Hint you can also access Shapes(n) where n is an integer.
I have only mirrored your coding style here to help you understand.
iobj.Select is redundant and pointless inside the loop
There are books about on coding in VBA I suggest you invest in one!
The code i presented is "clutter" despite the fact that it works?
Nothing happens. Nice try.
"Clumsy"? Cannot be more simple. Shapes(n) did not work: compile error; Sub or Function not defined. Also, Shapes.Count does not work: object required.
There is NO OTHER WAY to get the object. Only pointless if one does not want it in the first place.
Have two; almost as useless as the Excel so-called "help".
THAT is in (eXplicitly VBA, NOT n Excel (which does not have those "windows".
They sure make it look like Excel, but that function is implemented only in VBA.
I think what Martin fails to realize is that the program VBA is NOT Excel and vice-versa. In most cases, language statements to do a certain thing are the same, but there are some cases where some functions/methods/etc exist in only one or are implemented in a totally different way.
Seems StackOverflow has fallen into the same trap.
*NO* it isn't and *YES* it does. You are thicker than pig shit.
What ancient version of XL are you running? Pre 97 or something?
I program Excel from time to time. The example I gave works in XL2010 VBA 7.0 without any problems. There might be some tweaks needed for previous versions but nothing there strikes me as non-portable.
If you look closely at the screen print you will see that it is a part of the "View" menu list for XL Developers window.
They even give the helpful suggestion of displaying an output string in a msgbox if you want to halt execution.
ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.