Free Microsoft Excel 2013 Quick Reference

Print Excel VBA code in color?

Is it possible to print Excel VBA code in color please? (i.e. comments in green etc.) Thanks!

Post your answer or comment

comments powered by Disqus
Is there a way to print the VBA code in color, without it being an add-in, or costing anything? So far all I could find were add-ins ranging from $39 to $75 (if not more.) All I want is to print it in color. I can't believe MS dropped the ball on that.

hi all,

At the moment I directly open the *pri file in our Portfolio program called Advent Axys, and manually update the ticker prices using a downloaded Excel format file from the BigCharts website which is saved as csv extension.
What I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts.
But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention.
Then once it updates, I import the updated *.pri file back in our Portfolio program.
I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). Would anyone have any ideas about how to use external files and Excel VBA code in Visual Basic?

Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet. Any thoughts or advice on this will be much appreciated.
Thank u so much
P.S Not able to upload the 2 files since their extention is different

Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String) 
     'Below are Excel VBA codes
     'Uses the  test.csv  to look up tickers in test.pri and update the price in it
    Dim PriceFile As Workbook, BigChartFile As Workbook 
    Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet 
    Dim MaxRows As Long 
    Dim PriceFileRow As Long 
    Dim BigChartRow As Long 
    Dim BigChartFound As Boolean 
    Call CheckBookOpen(BigChartPath & BigChartName) 
    Call CheckBookOpen(AxysPricePath & AxysPriceName) 
    Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv
    Set PriceFile = Workbooks(AxysPriceName) ' pri file imported from Advent Axys saved as test.pri
    Set BigChartSheet = BigChartFile.Sheets(Sheet) 
    Set PriceFileSheet = PriceFile.Sheets(Sheet) 
    MaxRows = w1.Range("a65536").End(xlUp).Row 
    For BigChartRow = 2 To MaxRows 
         'On Error GoTo NotFound
        BigChartFound = True 
        PriceFileRow = PriceFileSheet.Range("b:b").Find(BigChartSheet.Cells(BigChartRow, 2).Value).Row 
        If BigChartFound = True Then 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = BigChartSheet.Cells(BigChartRow, 4).Value 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = "not found" 
             'PriceFileRow = PriceFileRow + 1
        End If 
    End If 
Next BigChartRow 
Exit Sub 
 'w3.Cells(w3Row, 1) = w1.Cells(w1Row, 1)
 'w3.Cells(w3Row, 2) = w1.Cells(w1Row, 2)
 'w3.Cells(w3Row, 3) = "not found"
 'w3Row = w3Row + 1
 'w2Found = False
Resume Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi everyone,

I have a protected Word document in which an Excel workbook is embedded (also protected, both on the worksheet and workbook level) used as a rate chart in a form contract). In using the following code (that I got from this forum - thank you for that), the non-scrolling works properly while I have the Word document open, but after I re-protect and close the document, the VBA code in the embedded workbook is lost when I reopen it:

    Me.ScrollArea = "A1:H17" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am sure it is something simple that I am overlooking, but I would appreciate any help. Thanks!


As far as I know, there is no code in Excel that is
secure, even with passwords. There's always someone
selling password cracking software. You might be able to
write this Excel VBA code in VB6, and give to your client
an executable program that can't be cracked. I'm not sure
if it would work in your case, but you might want to
investigate this some more.

I'm new at writing Excel VBA code this way in VB6. I've
got some simple programs to work and they work perfectly.
If you need more specific questions, I hope one of the
experts with this type of programming can help.


>-----Original Message-----
>I write VBA code to automate certain functions in my
Excel file, and I use a
>lot of "template" sheets to generate charts and save them
as a new xls file.
>My client wants to run my programs on their site, which
requires that I give
>them a copy of the main Excel workbook that contains my
templates and VBA
>code. Is there any way for me to hide them so that my
client cannot copy my
>code? Ideally, I want to be able to password-protect
everything in my file.
>(The client really just has to press the keyboard
shortcut --- CTRL-M --- to
>run my main routine.).
>Thanks for your help.


When I print out my VBA code on a color printer, it comes out in b&w. Is
there some sort of limitiation or some option (that I can't find)?



This is not a question directly on VBA I guess, but I'd like to print a bunch of code in color. This is because I have a workbook with probably 50 or so Subs in 12 modules, and I've made some many changes over the past month or so that I've lost all sense of organization to the project, so I'd like to get it all in front of me.

Anyway, when I print to a color printer from VBA, I still get black and white.
Is there any way to get color?

I have a really simple question - am tryingto print my VBA code, but want to print it as it appears in the VBA window, in colour (i.e. green comments, blue operations, etc). Is there a way to set it so the code prints in colour?

Hi All,

Is it possible to create charts in power point using vba codes in excel?
Generally what i do, i create a chart in excel based on some data in excel and then i copy it and paste (special) in a power point as 'picture (enhanced metafile)'. But i want that the charts to be prepared in power point because it looks better than the excel one.


Is it possible to print VBA code in colour?

I can't seem to do this. I want to see the remarks and statements as they appear on screen. Mine only prints in Black

Any ideas?

I would like to print Excel Worksheet Borders in COLOR. [ I already know how
to select/change the border color! ] Once I have done so, the border is
displayed in the appropriate color on my monitor - for example, RED.
However, when I PRINT the worksheet, the borders print BLACK. (All other
items, text, objects, WordArt, etc. print properly.) I have tried this on
several PC's and printers - inkjet and laser - at the office and at home -
each with the same result.

NOTE: I have tried this with Office 2000 and Office XP ...

How can I use VBA to change VBA code in another excel file?



I am trying to create a vba code in Excel to go to a list of files and
compare the file name that I entered manually with the list to determine if
there is a file out there by that name. If there is not a file out there, an
error message will be generated telling me that the file does not exist and
to enter a new file name.


Is there any possibility to quit from application PowerPoint launched
through VBA codes in Excel?

I have the following code in Excel:

Sub LaunchPPT()
Dim PPSlide As Object
Set PPSlide = CreateObject("PowerPoint.application")
With PPSlide
.Visible = True
.presentations.Open filename:="C:InfoDataProgramasInfoGraph.pps"
.Run "InfoGraph.pps!UpdateAllLinks"
.presentations("InfoGraph.pps").Saved = True
End With

Help me, please!

Some time ago I was developing a worksheet with a large amount of code in it, and I got some kind of 'out of memory' error.
I seem to remember it turned out that the error was because I had too much vba code in a single worksheet.
I think I overcame the problem by moving routines out of the sheet and into a module.

I'm probably going to be approaching this limit again with something I'm wroking on now (although I haven't had any errors yet.)

Does anyone know what the maximum amount of code you can have in a single worksheet in Excel 2003 is?
And is there an easy way to find out how much code you have in a worksheet?

Thanks in advance for your help.

Hello all, I am new to VBA, well Actually I am new to Programming in general.
I've got this VBA code in Access which I like to use in Excel, But when I copy it
over to Excel I get a run-time error 483. I know Application.echo does not exist in Excel,so basically my question is: what is equivalence of Application.echo in Excel or what can I use in its place. any help would be greatly appreciated.
The code is as Follow:

PHP Code: 
Application.Echo True, "*** batch - " & sRutin 

I have VBA code in Excel which restrict access to a workbook depending on the user login detail, the workbook contains 11 sheets, worksheet are asigned to different user depending on the user login detail The problem I have is when i open other worksheet or workbook the VBA code starts to effect the other worksheet/ workbook, is there a way I can stop this from happening.

please help

Hi All,

I need an excel vba code to use for updating an access database file.

File Name> C:TEST.mdb
Table Name> Spec (It includes 5 columns and 400 rows)

I wrote a code but need the critical part.

    Dim con As ADODB.Connection, rs As ADODB.Recordset 
    Set con = New ADODB.Connection 
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:TEST.mdb;" 
    Set rs = New ADODB.Recordset 
    rs.Open "Spec", con, adOpenKeyset, adLockOptimistic, adCmdTable 
     'I need a code here that take the value from Range("A1") and export it to
     'access table "Spec" to row 52 and column 4 (column 4's name is "value")
    Set rs = Nothing 
    Set cn = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your attention.

I need to include some vba code in about 90 Excel workbooks. They each need to be standalone workbooks so I cannot reference one source. Anyone have an idea how to do this rather than copy/pasting the code into each workbook? Thanks

Hi guys can you help me with this one? I'm not too familiar with com addins.

It's a question from someone that I referred to Ozgrid, but instead of posting sent me a U2U instead.
I told him how helpful and friendly the board is so our rep is on the line.
Much appreciated, Doug

(I'll let him know about this thread).

Running vba code in Com Addin DLL
Message: I am trying to turn my vba xla addin into a Com Addin DLL , for better code security. I purchased Microsoft XP Developer for the Addin Designer feature. I installed a button on the excel tool bar, with the following code in the Add-in Designer module under Excel.
The COM Addin compiles and installs...the msgboxes work. However, I can't run simple vba subroutines from MyButton such as the one below this code, which creates a new test worksheet in a new workbook. I keep getting a Run-Time error-2147417846 decimal 8001010a hexidecimal. I unstalled Norton AV plug-ins, however, still get the error.

What code/structure changes are needed to do this?

CODE IN ADD-IN DESIGNER***************************

Dim oXL As Object 
Dim WithEvents MyButton As Office.CommandBarButton 
Private Sub AddinInstance_onConnection(ByVal Application As Object, _ 
    ByVal connectMode As AddInDesignerObjects.ext_ConnectMode, _ 
    ByVal addininst As Object, custom() As Variant) 
    On Error Resume Next 
    MsgBox "my addin start in " & Application.Name 
    Set oXL = Application 
    Set MyButton = oXL.CommandBars("standard").Controls.Add(1) 
    With MyButton 
        .caption = "MyButton" 
        .Style = msoButtonCaption 
        .tag = "my custom button" 
        .OnAction = "!" 
        .visible = True 
    End With 
End Sub 
Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _ 
    custom() As Variant) 
    On Error Resume Next 
    MsgBox "My Addin was disconnected by " & _ 
    IIf(RemoveMode = ext_dm_HostShutdown, _ 
    "Excel Shutdown. ", "end.user.") 
    Set MyButton = Nothing 
    Set oXL = Nothing 
End Sub 
Private Sub MyButton_click(ByVal ctrl As Office.CommandBarButton, _ 
    canceldefault As Boolean) 
    Call workbook_open 
End Sub 
End OF CODE In ADDIN DESIGNER**************************** 
CODE In WORKBOOK_OPEN************************************** 
Sub workbook_open() 
    Dim nwb As Workbook 
    Set nwb = Workbooks.Add 
    Dim paramsheet As String, rawdatasheet As String 
    paramsheet = "param" 
    rawdatasheet = "RawData" 
    nwb.Sheets.Add Type:="Worksheet" 
    With ActiveSheet 
        .Move After:=Worksheets(Worksheets.Count) 
        .Name = paramsheet 
    End With 
    Sheets.Add Type:="Worksheet" 
    With ActiveSheet 
        .Move After:=Worksheets(paramsheet) 
        .Name = rawdatasheet 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
END OF CODE IN WORKBOOK_OPEN**************************

I need to create a VBA code in Excel. I have two columns one with Jday values and the other with its respective Temperature values. I want to select those Jday values that have a decimal 5 (10.5, 11.5, 13.5, 3.5, etc) with its respective Temp values.

Can someone help me??



I have put together the following Print Setup VBA code behind a command button, but I can't get it to work properly:

Sub Print_Report()
Application.ScreenUpdating = False
With Worksheets("Rates").PageSetup
.DisplayPageBreaks = False
.PrintTitleRows = "A9:LastColumn"
.LeftMargin = Application.InchesToPoints(0.3)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.Orientation = xlPortrait
.FirstPageNumber = xlAutomatic
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.ScreenUpdating = True
Selection.PrintOut Copies:=1, Collate:=True
End Sub

I need to fix some of the code to incorporate the following rules:

I want the Macro to:
1: Include data in report from cells A5 to last column/last row
2. Print column titles on every page (Columns titles are now found on Row 9 starting from A9 to last column)
3. Autofit Columns to best Width
4. Scale Worksheet to fit all columns on One Page

I am trying to embed Excel .xls file objects that contain VBA code in pages of a .ppt file. Using the menus insert -> object -> from file.

This works fine if I use Powerpoint 2003, but when I try to use Powerpoint 2010 I can get the object inserted but when I try to edit it, I get the following errors:

First in Excel:
Error accessing file. Network connection may have been lost.

Then in PowerPoint:
Microsoft PowerPoint can't start the application required to open this object. An error occurred and this featue is no longer functioning properly. Would you like to repair this feature now? I click "no" as I know the function is fine.

then another PowerPoint error:
There isn't enough memory available to read Worksheet.

The files are stored on same disk drive.

My troubleshooting so far:
I have set all macro security for both Excel and Powerpoint 2003 and 2010 to none.I have checked any boxes that state trust VBA objects.If I insert the .xls object using PowerPoint 2003 on another system and then re-open the ppt file using 2010, I can then edit the .xls object.If I convert the .xls file to .xlsm, then insert, it will work fine.PowerPoint 2010 can insert and edit .xls objects that do not contain VBA code.I set the references in the Powerpoint VB editor to include Excel object library, no change.I have installed 2003 on the same system as 2010 (Win 7), and it will not work then, even in the 2003 applications.I have installed 2010 on the Win XP system and it did not work there either.I tried dragging and dropping the xls file onto the ppt page, that results in 7 of the 'Error accessing file. . ' messages before it inserts the object.If I insert the object in 2010, then try to edit the object on the 2003 system, I get the same edit errors.I can create a new Excel 97-2003 worksheet object, then add a macro to it after it is inserted and it will edit fine. It only errors when inserting from file.To reproduce the problem, open/create a powerpoint file (can be ppt or pptx). Then insert any xls file that contains some VBA code. Insert the file as an Excel 97-2003 worksheet object. After the object is inserted, select to edit the worksheet object. That is when the errors will occur.

Windows 7 system details:
OS - Windows 7 64-bit
Office ver - 2010 Professional Plus
Intel core2duo cpu
4GB ram

Windows XP system details:
OS - Windows XP Pro SP3
Office ver - 3002 Standard Edition SP3
Virtual machine on Oracle VM VirtualBox

Any help is greatly appreciated.


Hi, I was wondering if somwone would be able to give me a hand with some Excel / VBA Code. Im trying to write some code to delete rows in a worksheet that have negative numbers in a particualr column - in the attached worksheet (Delete any row that has a negative number in any cell in column J ). Im new to Excel Programming, so any help would be greatly appreciated.

Cheers and thanks in advance!

Can anyone tell me how to write a macro, which in turn write some VBA code in a worksheet?


No luck finding an answer? You could always try Google.