When controlling an office program from Excel, why it's not needed to declare variables like WordApp.Paragraph?

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
For example when you make the application, you have to use the application's name in the datatype:

Dim App As PowerPoint.Application
Set App = New PowerPoint.Application

But here not:

Dim Pres As Presentation 'why not PowerPoint.Presentation

How will VBA know if that datatype belongs to whom?

Let's say Word and Excel has a Range object, how will VBA know which range I mean, if i don't specifiy? Or these same named objects can be used in all office programs without specifiing the app, since they mean the same?

Thank you very much for your idea, have a nice day
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
There is a default hierarchy to search which application (technical word for that is namespace) the object like range belongs to.
1. current application. If there is a macro1 and it has a dim range1 as range. If the macro is currently stored in excel, it will be treated as an excel range object. If the macro1 is saved in a word document, the dim range1 as range (assuming there is such an object in word) will be treated as word application range object. So,look at where it is saved.
2. If you say add a project reference to adodb, and use a dim rs1 as recordset, it tries to search all project references included and tries to find what a recordset object is.
3. obviously all micosoft office applications are in the project reference search path, so it understands those objects. Application object is present in multiple places like excel, powerpoint, word etc. so, you have to be specific, whether it is a powerpoint.application or excel application or word applicaton. otherwise if that macro is in an excel, an excel application will be assumed.(see rule 1 above)
4. Probably presentation object is present only in powerpoint and not in excel or word, so system was able to find it powerpoint namespace and did not give error.
This is just an introduction to namespace and may be someone with Mircosoft certification can correct any mistakes/expand/give you a reference url to learn more or you can google.
 

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
There is a default hierarchy to search which application (technical word for that is namespace) the object like range belongs to.
1. current application. If there is a macro1 and it has a dim range1 as range. If the macro is currently stored in excel, it will be treated as an excel range object. If the macro1 is saved in a word document, the dim range1 as range (assuming there is such an object in word) will be treated as word application range object. So,look at where it is saved.
2. If you say add a project reference to adodb, and use a dim rs1 as recordset, it tries to search all project references included and tries to find what a recordset object is.
3. obviously all micosoft office applications are in the project reference search path, so it understands those objects. Application object is present in multiple places like excel, powerpoint, word etc. so, you have to be specific, whether it is a powerpoint.application or excel application or word applicaton. otherwise if that macro is in an excel, an excel application will be assumed.(see rule 1 above)
4. Probably presentation object is present only in powerpoint and not in excel or word, so system was able to find it powerpoint namespace and did not give error.
This is just an introduction to namespace and may be someone with Mircosoft certification can correct any mistakes/expand/give you a reference url to learn more or you can google.
Thank you very much.

The only thing i dont understand is that it did not allow to declare

"Dim x as PowerPoint.Presentation"

This is not allowed, however your explanation would make completely sense if it would. I guess i maybe miss something yet. But thank you very much, if i figure out, i will post here.
 

Jamualson

Board Regular
Joined
Feb 17, 2021
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
There is a default hierarchy to search which application (technical word for that is namespace) the object like range belongs to.
1. current application. If there is a macro1 and it has a dim range1 as range. If the macro is currently stored in excel, it will be treated as an excel range object. If the macro1 is saved in a word document, the dim range1 as range (assuming there is such an object in word) will be treated as word application range object. So,look at where it is saved.
2. If you say add a project reference to adodb, and use a dim rs1 as recordset, it tries to search all project references included and tries to find what a recordset object is.
3. obviously all micosoft office applications are in the project reference search path, so it understands those objects. Application object is present in multiple places like excel, powerpoint, word etc. so, you have to be specific, whether it is a powerpoint.application or excel application or word applicaton. otherwise if that macro is in an excel, an excel application will be assumed.(see rule 1 above)
4. Probably presentation object is present only in powerpoint and not in excel or word, so system was able to find it powerpoint namespace and did not give error.
This is just an introduction to namespace and may be someone with Mircosoft certification can correct any mistakes/expand/give you a reference url to learn more or you can google.
Thank you very much.

The only thing i dont understand is that it did not allow to declare

"Dim x as PowerPoint.Presentation"

This is not allowed, however your explanation would make completely sense if it would. I guess i still miss something. But thank you very much, i try to figure out.
 

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
i wrote a macro with one line dim app1 as powerpoint.presentation, it gave an error user defined object not found or something like that. I included project reference to powerpoint via tools references (see image), now dim app1 as powerpoint.presentation did not give error.
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    52.1 KB · Views: 1

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,544
For example when you make the application, you have to use the application's name in the datatype:

Dim App As PowerPoint.Application
Set App = New PowerPoint.Application

But here not:

Dim Pres As Presentation 'why not PowerPoint.Presentation

Instead of:
Dim App As PowerPoint.Application
Set App = New PowerPoint.Application
You could, of course, use:
Dim App As New PowerPoint.Application
though I'd name the variable 'ppApp' or something such instead of just 'App'. I'd also prefer to use:
Dim ppPres As PowerPoint.Presentation
instead of:
Dim Pres As Presentation
Let's say Word and Excel has a Range object, how will VBA know which range I mean, if i don't specifiy? Or these same named objects can be used in all office programs without specifiing the app, since they mean the same?

Thank you very much for your idea, have a nice day
When it come to ranges, your variable declaration really does need to be specific. If, for example, you're running the code from Excel, then:
Dim Rng as Range
will refer to an Excel Range. To refer to a Word range, you'd need something like:
Dim wdRng as Word.Range
and, when you use both, I'd also prefer to make both references more apparent in their names:
Dim xlRng as Range
Dim wdRng as Word.Range

Likewise, if you're automating multiple apps (e.g. Word and PowerPoint from Excel) and the automated apps have the same object types (e.g. paragraphs), you need to be specific about which app's objects you're referencing:
Dim wdPar as Word.Paragraph
Dim ppPar as PowerPoint.Paragraph
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,485
Messages
5,636,608
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top