Excel/Default printer

Advertisement


Question
QUESTION: Tom, I don't know VBA, but was wondering if you could give me a tiny routine that would return the name of the current default printer.

Perhaps you can put it in the variable CurrPntr. I run your routine, and then use that variable afterwards.

Please tell me if this is too much to ask. Thanks.

ANSWER: Niko

currPntr = Application.ActivePrinter


demo'd on my computer in the immediate window of the Excel visualbasic editor

currpntr = application.ActivePrinter
? currpntr
HP Officejet 7300 series on Ne01:

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom, I think something is missing. If I put that line into the VBA Editor and run it, CurrPntr doesn't get assigned.

I've seen Subs and Functions before (though I don't know their difference), so I enclosed that line between Sub/End Sub and also Function/End Function. Again, neither of those worked.

How do I use this line? I'd like to run this routine (function, whatever), and then be able to use CurrPntr elsewhere.

Thanks.

Answer
Niko

Nothing is missing in the answer I provided from the question you asked.  You asked me how to assign the name of the current printer to the variable CurrPntr.  

Sub abc()
Dim CurrPntr as String
CurrPntr = Application.ActivePrinter
End Sub

will assign the string to CurrPntr, but CurrPntr has local scope.  It is only visible to the procedure abc.  How you use it is up to you.  

If you want to use it elsewhere, then you need to give it public scope (or a scope that support what you want to do).

for public scope, at the top of a general/standard module (in the visual basic editor you would do Insert=>Module in the menu to get a general or standard module).

so at the very top of that module above any procedures you would put

Public CurrPntr as String


now you would need to call the procedure that assigns it a value.  I am just showing you the procedure - I haven't illustrated calling it.  

Sub abc()
CurrPntr = Application.ActivePrinter
End Sub

Notice I have made no declaration for CurrPntr in that procedure because I am using the Public version of the variable.

So you would not declare CurrPntr anywhere else in your workbook.  Also, CurrPntr won't have a value until you run the procedure abc (or other procedure that assigns it a value).  After that it will have workbook/project scope and any other code will be able to see and use the value (or alter it if you want).  

So if in a module I had

Public CurrPntr as String

Sub abc()
CurrPntr = Application.ActivePrinter
End Sub

Sub Demo()
' call abc to initialize CurrPntr variable
abc
s = "The default printer is: " & vbNewline & abc
msgbox s
End Sub

Sub Demo2()
' this would not be run until you know CurrPntr has a value
s = "The default printer is: " & vbNewline & abc
msgbox s
End Sub



All that said, why even have the value.  Anywhere you need to get the value of the  default printer, just use   Application.ActivePrinter

then Demo would be

Sub Demo()
'Don't call abc to initialize CurrPntr variable
'abc
s = "The default printer is: " & vbNewline & Application.ActivePrinter
msgbox s
End Sub

Sub Demo2()
' this would be run anytime
s = "The default printer is: " & vbNewline & Application.ActivePrinter
msgbox s
End Sub


--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.