PDA

View Full Version : [help][Important to learn VBA] export the list of classes from VBA Object libraries



mikeleung110
11-11-2015, 08:23 AM
Actually I am a new starter to study VBA.

I started to learn it by reviewing a lot of examples from different websites/ notes.
However, I found that there are lots of unknown items that I wanna to know and familiar with them.

TO be more efficient to learn the VBA, I tried to browse the web and see if there is any summary of the VBA coding skills, there were, but only has some looping skills and other statement techniques and etc.

What I want to know is that I want to construct a database table to review all of the items that will be appeared in the VBA.

Thereby I went to the libraries of the VBA in the VBA Project and found that there are lots of items and I want to export them in a more systematic table for the sake of effective learning(I want to print this table as a reference notes whenever I am studying the VBA)

Actually I had already known the F1 help can display the each details for each command and examples, however the help window only display one items and show ONLY this items' examples and details. It will be a disaster if I copy & paste every items for every one click and finally to construct a reviewing table.

Here is general idea for the construction of the VBA reference table: (Easy to understand) by attaching a picture



Noted that to view the libraries of the VBA objects,
1) Alt+F11 to open the VBA windows
2) F2 to browse the VBA Object libraries

SamT
11-11-2015, 11:35 AM
Visual Basic For (MS) Applications.

VBA Is written to work with the Object Oriented programs of Microsoft Office. IMO, the best thing to learn first is Object model of Excel. All the MS office applications have an Object Model which is different for each.

The attached is an excel workbook with the complete Excel Object model, the MSO Office Object Model and the VBE Object model, which I would ignore for now. Every help from the Excel Help file is in the Comments of each Cell.

This particular model is based on Excel 97, but it is still relevant as there have been very few substantial negative changes in any application's Object model since that time. IOW, there's more to it now.

The basis of Using VBA to automate Excel is
[ParentObject/ParentObject].Object.[Property|Method]

Property Examples:
Application.ThisWorkBook.WorkSheets.Item("sheet1").Range("A1").Interior.ColorIndex = 3

SomeVariable = Application.ThisWorkBook.WorkSheets.Item("sheet1").Range("A1").Interior.ColorIndex

Many Properties/Objects are the default Property of the Parent Object. For example, If your code is in a Worksheet Code Page, the Worksheet is the Parent of the Code and the Range, so all you need is Range("A1").Interior.ColorIndex = 3.

Like wise for any code in the Workbook: Sheets("sheet1").Range("A1").Interior.ColorIndex = 3

Method Example:
ThisWorkBook.WorkSheets.Add

Paul_Hossler
11-11-2015, 12:54 PM
Another approach would be to use the online help and selected topics under the Excel Object Model heading, or https://msdn.microsoft.com/en-us/library/office/ff194068.aspx

I would not read in alphabetical order, but the major objects to start browsing with would probably be

Application

Workbooks / Workbook

Worksheets / Worksheet

Range

The rest you can look up as you need

They do get updated with new versions

14741

mikeleung110
11-11-2015, 06:30 PM
Visual Basic For (MS) Applications.

VBA Is written to work with the Object Oriented programs of Microsoft Office. IMO, the best thing to learn first is Object model of Excel. All the MS office applications have an Object Model which is different for each.

The attached is an excel workbook with the complete Excel Object model, the MSO Office Object Model and the VBE Object model, which I would ignore for now. Every help from the Excel Help file is in the Comments of each Cell.

This particular model is based on Excel 97, but it is still relevant as there have been very few substantial negative changes in any application's Object model since that time. IOW, there's more to it now.

The basis of Using VBA to automate Excel is
[ParentObject/ParentObject].Object.[Property|Method]

Property Examples:
Application.ThisWorkBook.WorkSheets.Item("sheet1").Range("A1").Interior.ColorIndex = 3

SomeVariable = Application.ThisWorkBook.WorkSheets.Item("sheet1").Range("A1").Interior.ColorIndex

Many Properties/Objects are the default Property of the Parent Object. For example, If your code is in a Worksheet Code Page, the Worksheet is the Parent of the Code and the Range, so all you need is Range("A1").Interior.ColorIndex = 3.

Like wise for any code in the Workbook: Sheets("sheet1").Range("A1").Interior.ColorIndex = 3

Method Example:
ThisWorkBook.WorkSheets.Add

Yayaya, thanks to Sam, this is exactly the same what the materials I really what to lookup and act as a reference card. Because I want to print it out to have a deep study.


Another approach would be to use the online help and selected topics under the Excel Object Model heading, or
I would not read in alphabetical order, but the major objects to start browsing with would probably be

Application

Workbooks / Workbook

Worksheets / Worksheet

Range

The rest you can look up as you need

They do get updated with new versions

14741

Thanks Paul too, I saw this websites (msdn) just before posting my 1st post in the forum, as I found this website useful but it is hard to export all the study materials to a excel sheet/word/notepad for the sake of printing it to study just for ONE CLICK (Because every items details need to CLICK ON it everytime! If I copy & paste everytime for each click, this will cost much of time and deter my study progress.)

Also, I would focus on the keywords of those wordings as suggested Paul. Thanks!
By the way, I thought learning all the stuffs about excel is a long way or even a endless road to go.:banghead:

SamT
11-11-2015, 09:57 PM
By the way, I thought learning all the stuffs about excel is a long way or even a endless road to goNah, it only took about 1500 hours to create the workbook I attached. :D

mikeleung110
11-12-2015, 06:17 AM
Nah, it only took about 1500 hours to create the workbook I attached. :D

This workbook is created by your own Sam? Just Awesome!
I have a few questions to ask:
1. Each items in for each sheets you are all just typing in MANUALLY?
2. As well as all the comments in each cells too MANUALLY?
3. By the way of printing it out to study, how can I print it properly as the printing area is too wide (If I use the landscape orientation fitting the sheet on ONE PAGE, is that possible?)

Now probably I have a question: in general, how is yours the speed of writing/generating a Brand-new code for each problems? (I mean take most of your cases in encountering each problems faced):eek:

Learning VBA is a hard stuff and a long way to go especially you must master the general idea of how the basic function as well as Excel formula FIRST.

Oh, just have a look on your excel, ONLY Excel Guru can only do this task.

SamT
11-12-2015, 07:53 AM
I don't do Formulas, only VBA. VBA is my hobby, a pastime, for pleasure only. Many other persons here are professional programmers.

I made that Excel Object Model workbook while I was recovering from major surgery to prevent becoming crazy for nothing to do.

mikeleung110
11-12-2015, 11:27 PM
Actually, VBA is a kind of Visual Basic language right? I found that it is useful for increasing the productivity for MS office. MS office is a platform for me to practice this language and give me a lot of trial.

However, what about C++ and JAVA? I heard this famous programming language and want to learn it, but I cannot find any platform for me to have a practice...

SamT
11-13-2015, 07:45 AM
However, what about C++ and JAVA?Linux. Start with LFS, (Linux From Scratch,) compiled from Red Hat 7 Fedora.

mikeleung110
11-13-2015, 10:46 PM
ok, but I found that the language of C++ is different from that of visual basic right? In the web, some of the people said that it is better for you to struggle and familiar with only ONE language FIRST than studying TWO different programming language simultaneously for the sake of avoiding confuse. What is yours opinion then?