2

Is it possible to call Word's MsgBox function from Excel VBA? I am having trouble doing this as MsgBox is not a method of Word.Application.

I have tried this:

Dim objWord As Word.Application
Set objWord = New Word.Application
Dim objDoc As Word.Document
Set objDoc = objWord.Documents.Add
objWord.Visible = True
objWord.Activate
Call objWord.Run("MsgBox", "Hello World") ' Or:
Call objDoc.Run("MsgBox", "Hello World")

But I get the error "Object doesn't support this property or method", no matter which 'Call' line I include.

I am trying to do this so that I can display a message box in front of the open Word document saying that the document rendering has completed. If I just call Excel's MsgBox then I have to click on the flashing button in the Windows Taskbar before I can see the message box.

The word document is generated entirely by my Excel macro.

Is this possible?

user3586058
  • 75
  • 1
  • 8
  • Have you tried `MsgBox "Hello World"` *before* making the Word application visible and active? – mjsqu May 06 '14 at 14:59
  • Thanks for the suggestion, however I'd ideally like the Word document to be visible on screen while it is being generated. – user3586058 May 06 '14 at 15:29
  • 1
    `MsgBox` is a method on the `VBA.Interaction` class - it's not part of the Word/Excel object library. – Tim Williams May 06 '14 at 16:29

2 Answers2

3

I have some functions that export modules and re-import them. It is configured now for PPT but was hacked together from Excel functions that I found. There is also a way to use VBA to write VBA. Both are fairly advanced though. Probably both sets of functions are available here:

http://www.cpearson.com/excel/vbe.aspx

However, that's probably overkill. This is probably your best bet, to use a popup instead of a VBA msgBox.

Sub Test()
Dim wdApp As Object
Dim shl As Object

Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
wdApp.Activate
Set shl = CreateObject("wscript.shell")

shl.Popup ("Hello, world!")

Set shl = Nothing
Set wdApp = Nothing

End Sub

See also here for more detail about how to control it's timeout/etc.

Whats the best way to display a message box with a timeout value from VBA?

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Thanks David, that's even better than I was hoping for: the popup seems to appear whichever window is in focus. – user3586058 May 07 '14 at 08:49
1

It is! The problem with the way that you're doing it is that the run function of the word application looks for macros with that name and runs them. The way that I was able to accomplish this is by making a subroutine in the word doc that creates the message box.

Public Sub Testing()
 Dim objWord As Word.Application
 Set objWord = New Word.Application
 Dim objDoc As Word.Document
 Set objDoc = objWord.Documents.Open("C:\whatnot\Stuff.docm")
 objWord.Visible = True
 objWord.Activate
 objWord.Run ("Testing")
End Sub

Then in the Word document:

Public Sub Testing()
 MsgBox ("Hello World")
End Sub
Telestia
  • 300
  • 2
  • 12
  • Many thanks Telestia. However the Word Document is generated entirely by the Excel macro. I could create a blank macro-enabled template containing your 'Testing' macro and then generate my document using that, but it would be nice if I was able to distribute the Excel spreadsheet without having to include the word template. – user3586058 May 06 '14 at 15:27
  • 1
    Yeah, I thought that might be the case after I posted my answer. So I started looking the the Word documentation, and alas, I can't find anything that would either let you import a module. Or even something that I find to be much more fun, write code that writes code. I'll keep looking while I have some free time, though that may be better off as a new question. – Telestia May 06 '14 at 16:04
  • Thanks Telestia. The 'popup' idea below works for me but thanks for being so helpful! – user3586058 May 07 '14 at 08:50
  • You could write code in a Word document using Excel VBA, but you need to reference the Microsoft Visual Basic for Applications Extensibility library in the Excel VB project, and you need to Trust Access to the VBE Object Model in Word's Trust Center _on the computer running the Excel code_ (which is not trusted by default for good reason, and it can't be trusted using VBA). – Jon Peltier Nov 15 '16 at 12:54