My Little Pony, sorry, my first little macro

[LineLaline]

Thank you Chris!
I will have a look at it. I don't have a lot of time at the moment so it will be something to look forward to reading in the cosier hours, on the couch, with the television and cats in the background or, as ever so often, cats in the foreground.

[LineLaline]

Also: thank you both DocAElstein and Charles Kenyon for all this information. it's a bit (waaay) over my head now but I suppose once I actually sit down to really begin to use VBA I may understand it. I like learning new things

It also reminds me of a similar way in web design; you use a stylesheet in which the formatting styles, classes, and Id's, are combined. The page can then apply those when needed, or automatically of course in the case of tag styles.

[ChrisGreaves]

... it's a bit (waaay) over my head now ...

If something is not way over your head, then you're probably not learning enough

... a stylesheet in which the formatting styles, classes, and Id's, are combined.

Way over my head!

Cheers, Chris

[LineLaline]

Way over my head!

Well, it's simple. CSS consists of three simple things (and more complicated ones) that are collected in one external document (although more may be used, an extra one for print for example, but let me not make this more complicated).

• HTML tag styles
  These apply formatting to tags as these tags appear on the page, a <p> for instance will be styled automatically.

• Classes
  These apply formatting only if you call them. If you want a paragraph to be red, for instance, you need to give it that class.

• ID's
  These also apply formatting only if you call them. But unlike classes, only 1 element is allowed a particular ID name so, an ID style can only style one element with that name.

I am not sure how this is going to look in the post because the preview only shows the code, but well, we'll see!

[Charles Kenyon]

Way over my head!

Well, it's simple. CSS consists of three simple things (and more complicated ones) that are collected in one external document (although more may be used, an extra one for print for example, but let me not make this more complicated).

• HTML tag styles
  These apply formatting to tags as these tags appear on the page, a <p> for instance will be styled automatically.

• Classes
  These apply formatting only if you call them. If you want a paragraph to be red, for instance, you need to give it that class.

• ID's
  These also apply formatting only if you call them. But unlike classes, only 1 element is allowed a particular ID name so, an ID style can only style one element with that name.

I am not sure how this is going to look in the post because the preview only shows the code, but well, we'll see!

Chris,
AFAIK CSS has nothing to do with Word documents, templates or vba.
Standardized styles can be applied, but not through CSS.
https://www.addbalance.com/word/stylesh ... #StartPage

[macropod]

And then there is field code programming...

[LineLaline]

Chris,
AFAIK CSS has nothing to do with Word documents, templates or vba.
Standardized styles can be applied, but not through CSS.
https://www.addbalance.com/word/stylesh ... #StartPage

No, he knows, that's not why I wrote it, I asked if it could be compared to a stylesheet. Not that CSS is part of VBA.

[DocAElstein]

...are several tools in the VBA editor that are underused and often helpful.

• The object browser (F2) .....
• In the editor's menus, Debug > Compile will point out any syntax errors in your code. .....
• The Immediate window lets you execute a single statement (or several if you separate them with colons) to see what happens.
  ......inside a macro, use the Debug.Print command to do the same, with the result also in the Immediate window. And if you stop a macro in the middle of executing, you can get values of the macro's variables in the window.
• Running a macro one statement at a time, with the Debug > Step Into (F8) command, lets you watch what's happening both in the code and in the document as each statement runs.
• Most of the time you can click on the name of an object or one of its properties in your code and press F1 to open the documentation page for that thing in your browser. Sometimes that page is actually helpful. Sometimes it isn't.

Hi,
I think I would describe myself as a “senior experienced newbie”, and totally agree that the tools available are underused. Looking back I think I would have benefited immensely if at an early stage of learning Office, Excel or Word, I had learnt some of those tools. I think I would then have wanted to learn VBA, since I would describe using Office without VBA, a bit like this:
You have a multipurpose all terrain vehicle that can fly, submerge, travel to mars, dig a tunnel to the centre of the earth, etc., and then using Office without VBA is like using that vehicle on a short predetermined route to a local shop to pick up 1 or 2 pints of milk a couple of times a week: (What a waste! - Eventually your vehicle sinks under a pile of smelly rotting milk.)
Based on my learning experience, and with the benefit of hindsight, what/ how I would have preferred to learn, I think I would suggest :-
_a start with the Debug > Step Into (F8) command along with the full use of the immediate Window and I would make a point of grouping that all together , - combining the use of the Immediate Window with and without a macro running, so the pause / Stop stuff would all be included her. That all would probably be in my second Excel lesson, after my first one which included writing my first very short macro to do the very first thing I ever did manually in an Excel spreadsheet: Writing something in it.
_b this would be a small advancement to _a. It would include the Watch window/ adding watches on variables, (and talk about dragging the Immediate and Watch windows about conveniently outside the VB Editor, ( and definitely include the registry Dock fix cure when VB Editor windows go wonky https://eileenslounge.com/viewtopic.php ... 81#p302581
https://www.excelforum.com/excel-progra ... ost4545938 )

The other tools I would defer a while before tackling. The macro recorder would come with those later, which I would probably introduce as a convenient way to quickly get some syntax, and try to discourage using it to learn writing macros, for reasons discussed in this Thread.

I have not personally found the object browser very useful, yet, it might be personal ignorance in understanding it, I am not sure. I usually found I need to know the answer already in order to find anything, Perhaps it is something useful to an experienced user to jog his memory, - that might tie up a bit with the comment ….. sometimes you know there's a thing you need to deal with, but you can't remember its name, or you got the parts of the name backwards, or whatever. Oh, there it is! …..

_.__________

[DocAElstein]

.... Immediate window lets you execute a single statement (or several if you separate them with colons) to see what happens. Use the Print keyword (or just a question mark) at the start of the line to have the result printed in the window (if the statement produces a value). Inside a macro, use the Debug.Print command to do the same, ....

That thing with the colons was interesting to catch. I had missed that. I just checked that in Excel and I see the same.
I figured it could be something similar to carrying out more than one command in excel vba , where on the same physical line you separate two code lines with a colon.
Getting the result out seems more to follow the way you add strings in the Debug.Print code line, and in which case the separating thing is a semi-colon.
Here is a quick demo to explain what I am talking about: In this first screenshot, I have wroted the same physical code line, (which is actually two code lines to do something similar, I Have wroted it in a macro and also in the Immediate window. The two code lines are separated by a colon. Important: at this point I have not run the macro or used the Enter key after writing the line in the Immediate window- this last bit is important just for the demo, as hitting the Enter key makes that line be done/ run.

( I got German Excel so my Immediate window is called Direktbereich
To get the Immediate Window showing, then from Excel hit Alt+F11 to get the VB Editor and then from the VB Editor hit Ctrl+g to get the Immediate Window. If you like, for convenience, you can drag that Immediate window anywhere including outside the VB Editor, which I like to do )
https://i.postimg.cc/xT9rnZRn/Before2-Lines-Line.jpg


Now, to get the next screenshot you can,
either
_ run the macros
or
_ go to the end of the line in the Immediate window and hit Enter

https://i.postimg.cc/bYxcXP1m/After2-Lines-Line.jpg


So that made sense.
After you done that you can get the results into the Immediate Window, as Jay said, either with a Debug.Print code line in the macro or in the Immediate window you can do the same Debug.Print code line, ( and in the Immediate window you can also put a ? instead of the Debug.Print

https://i.postimg.cc/BvvJytdK/Immediate ... s-Line.jpg

Those examples above was all Excel , I lack the knowledge of Word unfortunately to do a similar comparable example in Word VBA. Perhaps someone with experience in Word could add some comparable example to check out this colon / semicolon issue. That could make an interesting contribution and demo perhaps )

Code: Select all

 Option Explicit
Sub TwoLinesLine()
' Put some stuff in
 Let Range("B3").Value = "Hello": Let Range("D3").Value = "=" & """Hello again"""
Stop ' Now first hit  Ctrl+g  to get the Immediate window up, then  hit  F8  to continue in  step  mode, ( If you like for convenience, you can drag that  Immediate Window  somewhere, including outside the VBA editor.   If there are problems with it vanishing or getting stuff in an awkward place, then check out this fix: https://eileenslounge.com/viewtopic.php?p=302581#p302581      https://www.excelforum.com/excel-programming-vba-macros/343845-docking-project-explorer-vertically.html#post4545938


' get the results out
Debug.Print Range("B3").Value; "   "; Range("D3").Value



Stop
Debug.Print "Hello"; "   "; "=" & """Hello again"""
End Sub

Alan

[DocAElstein]

@ Chris @LineLaline
Hi Chris, Hi LineLaline
Chris, Just to clarify, (as I am a bit stupid and slow to get the point usually…), and for the benefit of anyone else passing that might be as stupid and me and miss the point… that zip thing you uploaded is basically a word doc, Names_0894.doc with a big list of your macros? There is nothing else there? Correct? – I am only asking as I know that occasionally there are some differences in how Excel ( which I know quite well) and Word ( which I don’t) organise their VBA stuff. In the past that has caught me out and I have missed stuff.
( I am not necessarily wanting to see all your macros, Lol. Learning from other peoples macros rarely works, IMO. Everyone has different styles and trying to understand other peoples coding often takes a lot longer than writing them from scratch. Maybe later if a macro is well documented and ‘Commented you have half a chance of gleaning something from it, which is why I often give an opened up longer version with lots of ‘Commented) . But even then it’s a poor second best for some good tutorials and then buckling down and practicing., IMO )

The other stuff there in that zip looks like some administration stuff I presume, not particularly of relevance to us, - but once again correct me if I am wrong
Thxs
Alan.

[ChrisGreaves]

AFAIK CSS has nothing to do with Word documents, templates or vba.

Hi Charles. In general, you are correct, but in my case - writing a web site compiler in Word2003/VBA - I had to bone up on CSS and all that twenty years ago.
I haven't looked at it since, so my web sites are houses of cards!
Cheers, Chris

[ChrisGreaves]

And then there is field code programming...

Then: "Go aWAY macropod!"

(I had begun to think about {Filename/P} but got distracted ...)
Cheers, Oh Great Field-Code-Guru!
Chris

[ChrisGreaves]

No, he knows, ...

Oh you're Good!
Cheers, Chris

[ChrisGreaves]

I figured it could be something similar to carrying out more than one command in excel vba , where on the same physical line you separate two code lines with a colon.

Hi Alan. I try to discourage programmers from using multi-statement lines.
If a procedure has so much code that multi-statement lines are needed to fit it onto one screen/page/coding-form, then the procedures is too complex and should be broken down into smaller components that can be tested and maintained independently.
And some of those components might be suitable for a utility-library.
Cheers, Chris

[ChrisGreaves]

... (as I am a bit stupid and slow to get the point usually…),

Hi Alan.
I like to make a distinction between ignorance and stupidity.
Ignorance (from the Greek "gnosis" hence "knowledge") is Not Knowing". Nothing wrong with not knowing. Not one of you knows what I was doing this morning, before I returned home to type these replies.
Stupidity is knowing that I am ignorant but not asking for knowledge.
You, Alan, are not stupid. You might be ignorant. Your reply indicates your strong desire to share knowledge (from the Greek "gnosis" hence "ignorant")

... There is nothing else there? Correct?

There you go again, curing ignorance by asking a question; therefore NOT stupid!

The other stuff there in that zip looks like some administration stuff I presume, not particularly of relevance to us,...

The list is a shorter summary of VBA code in a single project - in this case my utility library uw.dot.
The Word table can be sorted, as can any Word table, on up to three columns.
For example when I sort on "Public/Private" and then "Function/Sub", I see what are essentially Forms procedures such as "cmdCancel_Click".
In that same sequence I see that I have a few "TEST..." macros from self-testing procedures; those macros should be pushed back inside their parent functions.
That most of the procedures are "Public", "Function", and return a Result is a natural consequence, given that this is a list of procedures in a utility library.

I can subtract "Line Start" from "Line End" and know how many lines are in each procedure - is it time to split longer procedures?

There is a User Guide at http://www.chrisgreaves.com/Downloads/UserGuide.pdf
I use "Strip", "List" and "Indent" most often.
PM if you would like to play with the current version.

Cheers, Chris

[LineLaline]

Well, this is turning into something that is no longer my little pony
I will have to sit down and read it all when I have the time.

As I wrote my last reply in bed with my brain shutting down, just to make sure I made it clear I didn't mean VBA was similar to CSS, I didn't have the energy/sense/else to add that CSS is inside Outlook email templates. It still has nothing to do with VBA (although this might generate it, heaven knows, I don't yet). But it's there, and Microsoft pollutes its templates with a lot of nonsense and bloats a simple HTML file with a load of crap that is not even used but that is there 'just in case'.
One of the first things I do is rip out all the things that are not necessary. And of course to prevent anything being styled with the dreadful, say, Times New Roman. I spit and fart in the general direction of Times New Roman. I would sit and poop on Times New Roman were it not that this would give it wonderful pink rose-scented compost to grow in. So instead I just spit and fart and that will have to do. I'm afraid I went off on a rant here

[ChrisGreaves]

I'm afraid I went off on a rant here

Too much Marmite in your stir-fry?

One of the first things I do is rip out all the things that are not necessary. And of course to prevent anything being styled with the dreadful, say, Times New Roman.

That's what I like about Eileen's Lounge⁽¹⁾; it is sometimes as easy to get back on topic as it is to slip off.

When I discovered that Word97 documents could be saved in HTML format I leaped for joy into my car and set off to buy a bigger hard drive.
Then I discovered that HTML was really just a text file, fer Heaven's Sake!
And TEXT was what search engines were looking to index, too, at that time!
So I reasoned that I could generate the HTML on-demand and do a better job than MSWord.

Hence WbWrd, which has saved me many hours.

Cheers, Chris

⁽¹⁾Reason #375, at least, unless it was #46

[ChrisGreaves]

Well, this is turning into something that is no longer my little pony

LineLaline: Would you like to write a little Word/VBA exercise that will deliver you
(1) A general-purpose function that can be stored in a library
(2) Will be usable and useful in every VBA application that you write
(3) Is easy to write
(4) Requires no complicated code

I know that you have a lot on your plate right now, but since you are just starting in VBA, such an exercise might be a good exercise because it is independent of any work, in VBA or out of VBA, that you are tackling right now.
That is, you could pick it up, work on it, then put it down for, say half an hour three times a week.
Cheers, Chris

[DocAElstein]

.. this is turning into something that is no longer my little pony

Going a bit off course, "Hijacking" a Thread as its called, usually sends a lot of Moderators at large forums into a narcissistic rage and they kill the Thread, and I think they would kill the participants as well if they could. Going too far off course can be bad, and starting from scratch a totally different subject is of course stupid, and often done by mistake so a Moderator should catch that. But quite often, just as in real life, IMO, furthering a discussion and being a bit open minded often gets useful results, that is the way the human brain works. At least mine has got some pretty impressive results that way. (Politics can be a bit tricky though. As Politicians just go around in circles like arguing children getting nowhere, it’s probably best to avoid those discussions too much as they might fall into a pointless never ending pit)
I also would have thought that adding solutions to a thread, or asking similar questions is preferable to swamping yet more duplicated stuff into an already overcrowded internet. I notice the situation getting worse at most forums. The short answer merit star chasing Zombies will kill themselves off eventually I expect. I know the owner of one major forum that is heavily monetised is about to flick the switch to read only as he only pretends to like his moderator and volunteer Admin teams that have taken over and run the place, after a fashion. For business, ( views of the Ads), its better if people visiting stay there a bit longer looking for the answer, or trying to get with help the answer themselves, rather than dropping off a quick question , going away and waiting for one of the question answering zombies to answer. For questions of any significance you can forget the places anyway these days.
Eileen’s Lounge seems a bit more tolerant to useful and enjoyable furthering of discussions. Could be the smaller size. Less infected with people with Narcissistic personality disorder – the larger forums seem to attract them and I know of one very prominent and senior member officially diagnosed with this disorder being sent to a forum to keep her out of the real world..
Chris’s existence here is also helpful factor, (I mean to encourage furthering a discussion, he is very welcome in the real world as well)
_._______________

I try to discourage programmers from using multi-statement lines....
If a procedure has so much code that multi-statement lines are needed to fit it onto one screen/page/coding-form, then the procedures is too complex and should be broken down into smaller components ......

Ah yes, multi-statement lines , that was the illusive thing I could not recall earlier today when I posted…
I never thought of using colons just for the sake of to compact the vertical, or to extend the horizontal.
I never had problems running out of space in the vertical.
As for the horizontal,… I have such beautiful long ( wide ) single code lines, which I enjoy scrolling, to and fro. To interrupt them coarsely with a colon to another code line or statement would be vulgar blasphemy, IMO, :)

When using things like the Immediate window to quickly develop a code or fix one, then such quick crude things are perhaps more appropriate, so it was useful for me to pick that info up from this thread. I expect they could be helpful in building things using these multi-statement lines in the immediate window.

I think there may be a few occasions in coding where the colon is helpful, and possibly an advantage. Off the top of my head I can think of this one
If This Then Do This : And do this : And do a few others

That may be better , or not , then doing it like

If This Then
Do This
And do this
And do a few others
Else
End If

There may not be any advantages of the single physical line version. Perhaps it depends on whether in VBA the If is treated as a formula Short-circuiting thing, as it appears the IF in Excel is, or I am talking rubbish just because I caught this recent post https://fastexcel.wordpress.com/2023/01 ... nd-switch/

_.__________________-

As for your table stuff Chris, in the , Names_0894.doc, and me not quite getting the point . You are right I guess, I am not stupid but definitely ignorant. ( Also under average intelligence, and very slow – I know that as a fact, I am not ignorant about that , its based on well over average life experience which taught me to know and handle my limitations so well that it resulted in me being more useful and often better at some things than most very clever people who over estimate themselves thinking they are geniuses).
I think I am happy with thinking it is a list of some of your macros, listed in some way you find useful to help organise things.

I have some thoughts about being a part time retro-Excel expert, all be it in a limited fashion. You are the retro Word-expert. One way or another your existence seems quite useful. I got into Excel initially as I had to because it was not working for me so well and others could not always help me so well. I think the Word people popping by here have Word sussed to a level above anything I will ever need.
_.______

I have never been to sure about splitting code into well organised sub routines and functions. I do use things like that a lot when developing, or fixing problems early on, or when generally messing about learning stuff.
But when I think I have something bullet proof I put it all in one long program, possibly full with 'Green comments, most hidden to the right.
Its just a gut Layman uneducated guess, (and probably wrong), that a single very long program works better than one calling a lot of functions, even if it feels and looks more efficient doing it in that efficient looking way split up with functions and things.
It’s possibly just based on my non programming background. To me the idea of organising something so that you can correct it later when you realise something was done wrong, all seems a bit strange. I was a successful practical physicist working at the forefront of technology. You got it right, perfectly working for ever, when it was ready for production. If anything did go wrong, you stayed there, ( or wherever necessary) all night, fixed it, using all methods, mostly strictly forbidden and illegal ones, and hoped no one noticed when you crawled out from under the bench in the morning. It meant we achieved things with a team of 3-4 people that people have struggled since to achieve with teams going up to the hundred or more, split up into well organised seperate sections.

Alan

[DocAElstein]

..When I discovered that Word97 documents could be saved in HTML format I leaped for joy
…. then I discovered that HTML was really just a text File….

Yes, bang on Chris.
I stumbled over this a few years ago, and demonstrates once again how very useful VBA is.

Despite my ignorance, I was able to recognise quite easily in the text of the HTML, the bit which was the main text and stuff you actually seewhen viewing the Word file.
Now, someone needed to send me every day an excel file, for me to get a lot of important info from that she had filled in, ( which she had to do anyway for very important health reasons) .
She quite liked writing me letters every day as well, back then, and a word doc for that was quite convenient with all the pretty format possibilities.
So armed with my fairly basic VBA knowledge, and the new thing I stumbled over, I did this.
I told her to write that word doc whenever it was convenient, save it always under a certain name and forget about it.

When she had finished filling in the excel table, just click a button. VBA does the rest. It goes off gets the word file , saves it as a HTML ( .htm ) , imports it as a text file, adds to it in a pretty table the info I need from the excel file, resaves it as a word doc and sends all that to me via Email, …….. it makes a summary of the most important info, adding that to the main text of the Email, with the Word doc as an attachment to that Email.

(Who knows how different things might now be, if I had learnt VBA from the start when I first learnt Excel to start making the Excel file… :) :( :) )