Free Microsoft Excel 2013 Quick Reference

Adding charts via VBA

I am trying to add a chart to Excel workbook and get "An Out of memory error" when I try and name it. I have also tried to do this without the select statement but no joy. Thanks


With ActiveChart

.Name = "Orders"
.ChartType = xl3DColumnClustered
.SetSourceData Source:=Graph_Values1

End With

Post your answer or comment

comments powered by Disqus

I've tried for one entire day to refresh properly a chart via VBA but
couldn't make it ....

here's the problem : i have a big spreadsheet with several cpu-intensive
ranges and i refresh them via vba with range(...).select ,
selection.calculate to save precious cpu-time

but this way, a chart that is attached to this range is not updated so i
tried to do this via vba but all the solutions i tried sucked in at
least one major way

a hint from a pro ?

Dear all,
why is this failing?

Sub Test()
1) myform = "=$A$4+$A$10"
2) Cells(1, 1).formula = "=$A$4+$A$10"
3) myform = "=COUNTIF($AJ$2:$AJ$643;$A$653)"
4) Cells(1, 2).formula = myform
End Sub

Step 1) and 2) are working OK. When the code is executed the formula is inserted into the cell as expected. But, step 3) and 4) are failing with Run-time error 1004

Are there any limitations on the formula types you are allowed to enter via VBA?

br Thomas Borggreen

I have a list of data which is arranged as column headers with pricing points under each. There may be a different number of pricing points depending on the column heading (i.e. for ABC we had 18 prices, for DEF we had 20, etc...) The reason the data is maintained in this way is because we will have more than 256 pricing points which precludes me from setting the data up going left to right and because I want the chart to be set up a certain way.

Anyway, I update the data once a day via VBA and would like to tack on the updating of the series data. Knowing that the data range will be variable, is there a way to do this?


I have a problem to add the the second trendline in a "lines on 2
axes" Chart by using VBA.
The result is snapshot here.

As you see from this picture, the red trendline did not appear. In
addition, even though I want to add the second trendline manually, I
can not. I am not sure the reason.

Here is the VBA code :

Private Sub Draw_Graph_1()
Dim RangeY1, RangeY2, RangeX As String
Dim WS1 As String

WS1 = ActiveSheet.Name
RangeX = "=" & WS1 & "!R" & d1 & "C1:R" & d2 & "C1"
RangeY1 = "C" & d1 & ":C" & d2
RangeY2 = "=" & WS1 & "!R" & d1 & "C7:R" & d2 & "C7"

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="lines
on 2 axes"
ActiveChart.SetSourceData Source:=Sheets(WS1).Range(RangeY1),
ActiveChart.SeriesCollection(1).XValues = RangeX
ActiveChart.SeriesCollection(1).Name = "=""Revenue"""
ActiveChart.SeriesCollection(2).XValues = RangeX
ActiveChart.SeriesCollection(2).Values = RangeY2
ActiveChart.SeriesCollection(2).Name = "=""Search"""
ActiveChart.Location Whe=xlLocationAsObject, Name:=WS1
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
.SeriesCollection(2).AxisGroup = 2
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
End With

ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
ActiveChart.SeriesCollection(1).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 6
.Weight = xlMedium
.LineStyle = xlContinuous
End With

ActiveChart.SeriesCollection(2).Trendlines.Add(Typ e:=xlPolynomial,
Order:=6 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
ActiveChart.SeriesCollection(2).Trendlines(1).Sele ct
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With

End Sub

Can any excel VBA master help me on this? Thank you.

- Tony


I have a slick peice of code that will automatically plot charts in each seet of my workbook, but I cannot seem to add titles to the charts.

I have tried adding another sub routine and using a with statement , with no sucess.

Anybody out there have a solution?

Title needs to = the commented out titles above each peice of code



Sub EmbedChart()
Dim EChart As ChartObject
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=50, Top:=30, Width:=400, Height:=300)
' Just have to change sheet number in each

' Linear Transfer R^2
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=50, Top:=30, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$N$1,$N$39,$N$77,$N$115,$N$153,$N$191,$N$229,$N$267")
EChart.Chart.ChartType = xlXYScatter

' Non-linear transfer R^2
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=1050, Top:=30, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$AN$1,$AN$39,$AN$77,$AN$115,$AN$153,$AN$191,$AN$229,$AN$267")
EChart.Chart.ChartType = xlXYScatter

' Linear training R^2
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=50, Top:=400, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$N$21,$N$59,$N$97,$N$135,$N$173,$N$211,$N$249")
EChart.Chart.ChartType = xlXYScatter

'Non-linear training R^2
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=1050, Top:=400, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$AN$21,$AN$59,$AN$97,$AN$135,$AN$173,$AN$211,$AN$249")
EChart.Chart.ChartType = xlXYScatter

' Linear R^2 x block
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=550, Top:=30, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$N$1,$N$21,$N$39,$N$59,$N$77,$N$97,$N$115,$N$135,$N$153,$N$173,$N$191,$N$211,$N$229,$N$249,$N$267")
EChart.Chart.ChartType = xlXYScatter

' Non-linear R^2 x block
Set EChart = Sheets("1211").ChartObjects.Add _
(Left:=550, Top:=400, Width:=400, Height:=300)
EChart.Chart.SetSourceData Source:=Range("$AN$1,$AN$21,$AN$39,$AN$59,$AN$77,$AN$97,$AN$115,$AN$135,$AN$153,$AN$173,$AN$191,$AN$211,$AN$229,$AN$249,$AN$267")
EChart.Chart.ChartType = xlXYScatter

End Sub


I have a set of data (date, price) for a number (the actual number
depends on the user input from a previous form) of bonds, and I plot
this, via VBA on a single chart, with multiple series.

I then normalise this data, and then plot the normalised data on
another chart. All in the same workbook. I therefore created a
sub-procedure to create the graphs. Part code is below:

'Previous code reads in data from Db and creates 2 worksheets
'One with standard data, the other with Normalised.
'Then the Code calls the sub to create the charts.
Call MakeGraphs("MultiPriceActual", "BondData", lngRowCheck)
Call MakeGraphs("MultiPriceNormalised", "Normalised", lngRowCheck)

'Sub that creates the chart, and adds the series of data for
Sub MakeGraphs(strChtTab As String, strWks As String, intRef As Long)

Dim cht As Chart
Dim i As Integer, intj As Integer

Set cht = ActiveWorkbook.Charts.Add
cht.ChartType = xlLine
cht.Name = strChtTab

cht.HasLegend = True
cht.Legend.Position = xlLegendPositionTop
cht.HasTitle = False
'more code that does the series adding and formatting.

Basically what happens is the first call works well, the chart is
created, and the series plotted. On the second call, the chartsheet is
created, but not filled with a 'skeleton' chart (it is blank), so when
the .haslengend property is set, the code falls down, as there is no
chart to set the property for.

I am sure i am missing something basic here, and can post more code if

Many Thanks
Ian R

Excel 2003

I've got a lot of Excel charts to document. I'm doing it all via VBA procedures and saving the results into an Access table. Access VBA is my main proficiency, so I've got to learn a lot about the Excel Object Model. I've tracked down a lot of data, but here are a couple I just can't find.

1. The formula used in a ChartTitle. I see it in the formula bar when it's selected, but everywhere I look it only returns the currently showing text: the formula result. I need the formula, and I've got to get it using VBA. It's obviously saved somewhere!

2. The value that will be returned by each Option Button in each group of Option Buttons. I see it in the LinkedCell (1, 2, 3, 4, etc., as I click each button), but no matter where I try in VBA, all I get is 1 for checked and -4146 for unchecked. Surely Excel can tell me what that button will return. In Access all I'd have to do is ![optionbutton].OptionValue. (I'd also like to be able to change it, but that may be asking too much!)

I realize #2 could be retrieved indirectly by looping through the buttons, checking each, then reading the result from the cell, but it's got to be somewhere. For #1 I'd even settle for jumping to the formula bar and using API calls to put it into, then retrieving it from the Clipboard, but I don't even know how to get to the Formula bar.

I can see by my research that question #1 has been asked before, but nowhere could I find that it was answered. Any help would be greatly appreciated.


(Also tried: PUP v6 Chart Report returns result, not formula.)

Q: does anyone know of an article/link to VBA which will allow me to automate
the creation of an org chart in MS Excel 2003?


I am adding a scrollbar to a chart using vba. I'v been successful getting the scrollbar added; however when I view the chart, the scrollbar has the "sizing handles" around it, and the "bar" (object that moves back and forth) is missing until I resize the scrollbar manually. I've tried changing the initial size of the scrollbar to no avail. I've also set the visible property to True and the enabled property to True, also to no avail. Does anyone have a clue as to what I need to do? I'd like for the scrollbar to be fully functional after the vba code has run.
Thanks in advance for any help!

Hi all,

I'm having problems selecting / activating a chart that is added via code.

The chart is actually copied and pasted from another worksheet to prevent the whole thing having to be redrawn from scratch and the code to do this is in a class module 'chartwithevents' that then allows the added chart to respond to user clicks..

What I have so far is:

In the main code..

In the chartWithEvents class module:

Private pTargetSheet As Worksheet

Public Function addNewChart(targetWorksheet As Worksheet)

Set pTargetSheet = targetWorksheet

    While targetWorksheet.ChartObjects.Count <> 0                       'delete any existing charts on the worksheet

    Worksheets("HIDDENDATA").ChartObjects("ChartTemplate").Copy            'copy and paste chart from
    pTargetSheet.Paste Destination:=pTargetSheet.Range("B2")

   'add data to graph etc etc


End Function

Private Sub pChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
        'evaluate what was clicked and run appropriate code
        Call addNewChart(pTargetSheet)
    End If
End Sub

All works fine up to a point.. the chart is copied and pasted as expected and the data all populates fine however after the code has executed the chart remains unselected. This is annoying as it means that to continue working with the chart and have it respond to mouse events the user then has to click the chart again.. (this is more annoying than it sounds as clickin usually seslects various bits of the chart which then get dragged around etc!).

I've run the code in debug mode and also added a debug.print to the activate handler of the chart just to check and it appears the chart DOES get activated however as soon as the function exits the chart gets deactivated..

.. or more corectly the worksheet gets activated and in particular the cell that was directly behind the point on the chart that was clicked..

This is driving me nuts!

Any ideas?

Further info..

I added code to a button:

which is essentially the identical code to that in the class and it works fine.. I tried using the named sheet instead of
pTargetSheet inside the class module but without success.

Dear Forum,

I have a file in which the Sheet Name Can be Dynamically be Changed by typing any name in a Cell, but this works only for three sheets.This file was found this in the forum i dont have the source anymore

Firstly, since I am not VBA expert am not able to tweak it..
But My requirement is to have a file with a Single Sheet-> Main Sheet where I have the following details
Col A ---Col B-------- Col C
Sr.No----Sheet No----Sheet Name <----Row 1
1 ------ Sheet 2 ---- A

Lets say I type Sheet 2 in cell B2 and type the name in cell C2, then a New Sheet with this name should be added and if I just change the name in cell C2then still this name should be changed..

I know this is possible but only thru VBA?
Can someone please help me on the same...


Does anybody know how to change the source range of chart? Using VBA.

For instace...

Initially I have:
Green Yellow Red Purple
1 6 4 2
2 5

And I generate the chart:

Sub Macro1()

    NbItems = Range("Sheet1!K7")
    NbPoints = Range("Sheet1!K8")
    Set rg1 = Range(Cells(1, 1), Cells(NbPoints + 1, NbItems + 1))
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.PlotBy = xlColumns
    ActiveChart.ApplyDataLabels ShowSeriesName:=True, ShowValue:=False
End Sub
Later one, I have:
Green Yellow Red Purple Pink
1 6 4 2
2 5 2

How can I update the same chart? I.e., adding 2, Pink 2 to the existing chart (Using VBA).

Any help is welcome. :-)
Check the files.


I'm currently struggling to change the Title and the Legend of pie
charts that are generated by passing data from Word to Excel, then
copying the chart over to Word. (I'm using Word + Excel 2000).

The VBA is use works OK, and generates a reasonable looking chart.

However, no matter what I try, I cannot alter the chart title, or the
legend. Let me expand on this a little.

1. The Chart Title is always centered. However, the legend (if there a
lot of entries in the chart) spills over the title and obscures it.
Therefore, I need to position the title on the far left of the chart.

2. If I have more than, say, eight elements in the chart, the legend
spills beyond the bottom of the chart, but there seems to be huge
spacing gaps between each entry on the legend.
However, if I create a chart using an Excel Range and the Chart Wizard,
it seems to easily accomodate ten or more entries, and space them nice
and evenly.

It does not seem to matter whether I set "Legend.AutoScaleFont" to
true or false, or whether I try and position the title. It always shows
up the exactly the same.

I thought I had cracked this, but I obviously need to do some fine
tweaking. Any suggestions appreciated.

Code below:

//// cNumRows and cNumCols are variables used to populate an Excel
Range from a Word VBA array of data

Set oChart = oSheet.ChartObjects.Add.Chart
oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows,
cNumCols), PlotBy:= _
oChart.ChartType = xl3DPieExploded
oChart.RightAngleAxes = True
oChart.PlotArea.Height = 215
oChart.PlotArea.Width = 215
oChart.PlotArea.left = 5
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Fill.Visible = False
oChart.PlotArea.Border.LineStyle = -4142
oChart.Elevation = 30
oChart.Rotation = 80
oChart.Pie3DGroup.VaryByCategories = True
oChart.HasTitle = True
oChart.ChartTitle.Top = 0
oChart.ChartTitle.left = 0
oChart.ChartTitle.Characters.Text = "Current Asset
oChart.ApplyDataLabels Type:=xlDataLabelsShowPercent,
LegendKey:=False _
, HasLeaderLines:=True
oChart.SeriesCollection(1).DataLabels.Font.Size = 8

oChart.HasLegend = True
With oChart.Legend
.Legend.Shadow = True
'.Legend.Position = xlLegendPositionRight
.Legend.AutoScaleFont = False
.Legend.Font.Size = 3
End With


I am trying to create a panel chart in VBA to replicate what another program does with its plots. I have seen a couple of sites that discuss panel charts, but I haven't been able to find anything related to coding them with VBA. Please see the attached spreadsheet. Sheet 1 contains the data to be plotted (voltages and currents), and Sheet 2 contains a screen shot of what I am trying to replicate with Excel via VBA. This screenshot is of the actual data in Sheet 1. The first column of the data is the time in cycles (common X axis), IA, IB, and IC are the currents, and VA(kV), VB(kV), and VC(kV) are the voltages. Thanks in advance for your help.

This thread is also posted at:

I am creating several charts and I get an error when I try to add a title to some graphs.

        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=10, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A1:R2")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC1 " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=10, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A3:R4")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC2 " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=245, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A5:R6")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC5 " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=245, Height:=225)
        .Chart.SetSourceData Source:=Sheet22.Range("A7:R8")
        .Chart.ChartType = xlColumnClustered
        .Chart.ChartTitle.Characters.Text = "SC4 " & StartDate & "-" & EndDate
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Hours"
        End With
        'Graph Line
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=480, Height:=225)
        .Chart.SetSourceData Source:=Sheet8.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC4 " & StartDate & "-" & EndDate     'ERROR
        .Chart.SeriesCollection(1).XValues = Sheet8.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=480, Height:=225)
        .Chart.SetSourceData Source:=Sheet9.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC4 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet9.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        With Sheet1.ChartObjects.Add(Left:=390, Width:=375, Top:=515, Height:=225)
        .Chart.SetSourceData Source:=Sheet10.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .Chart.ChartTitle.Characters.Text = "SC4 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet10.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
        With Sheet1.ChartObjects.Add(Left:=775, Width:=375, Top:=515, Height:=225)
        .Chart.SetSourceData Source:=Sheet11.Range("T" & iStart & ":T" & iEnd)
        .Chart.ChartType = xlLineMarkers
        .HasTitle = True
        .ChartTitle.Text = "SC1 " & StartDate & "-" & EndDate
        .Chart.SeriesCollection(1).XValues = Sheet11.Range("B" & iStart & ":B" & iEnd)
        .Chart.Axes(xlCategory).CategoryType = xlCategoryScale
        .Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Inch2/min"
        End With
The first 4 graphs are executed perfectly, but when I got to the fifth, I get an error:

"This object has no title"

Does anyone know why this happens on that particular graph (and all others after it). I used the same line in previous charts and it worked just fine.


Hi, I have been a long time reader, first time poster.

I am having some issues with a macro I created. I have an excel file that generates a report. My goal is to "export" the main content sheets of the page via vba.

The problem is that the values-only exported copy is unstable and crashes after attempting to close the file immediately after export. This happens on multiple PCs, not only my installation of Excel. I have used this method in the past and have no issues whatsoever. Any suggestion on how I could make this more stable?

     'Sheets to export
    genFile = ThisWorkbook.Name 
    exportLoc = Workbooks(genFile).Worksheets("Control").Range("B14") 
    sDate = Format(Workbooks(genFile).Worksheets("Control").Cells(2, 2).value, "yyyy-mm-dd") 
    exportSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4") 
    For Each ws In ActiveWorkbook.Worksheets 
        ws.Range("A1:AZ1000").PasteSpecial Paste:=xlPasteValues 
    Next ws 
    Application.DisplayAlerts = False 
    ActiveWorkbook.SaveAs exportLoc & "FileNameString_" & sDate & ".xlsx" 
    Application.DisplayAlerts = True 
End Sub 

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

I am trying to create a program that does similar things to another program. One thing I need to do is to plot digital data (binary 1s and 0s). Please see the attached samples. The word document shows a sample plot from the program I am trying to imitate. The spreadsheet has all the sample data used to come up with the plot in the word document. In the plot, when the lines are bold, the digital element is picked up (binary 1), and when they are thin, the digital element is not picked up (binary 0). Ideally, the plot that I create in Excel with VBA will look at least somewhat like the sample plot I have provided. Thanks in advance for your help!

I posted this on and got a response on how to do it manually. Does anybody have other options, in particular with VBA? I am new to VBA and on a deadline to get this done, otherwise I would try to dig deeper into it myself. One other thing: the digitals to be plotted will come from a listbox where the user picks which digitals to plot (there could be tons of them). I had gotten help on to plot voltage and current waveforms from a listbox, so I figured that my answer to plotting the binary numbers is somewhere in between the two posts. Please see the links to my posts for the sample documents. Thanks in advance for your help!


I am fairly new to VBA and need help with a report to create multiple charts. In the report you will see two tabs one marked Data the other charts. In the charts tab I have manually created two charts using some of the data from the Data tab. You can see it is a fairly simply pattern. Columns C and D are the titles for the series, and I need charts for every 4 rows. After every 4th row I would like to create a new chart. Row E6:BE6 (date) serves as the X value, while the data below are the values for the chart. I have only created two charts but you can see that I need to create charts for all the values. The problem is that the number of entries, will change. Here I have 3 set of 4's. I could have X number of sets of 4's. I need to write code to make sure I catch all the data. I have tried many different types of loops but can't seem to get it. It would be great if the charts could form right under each other, but that is not that important, as long as they don't form right on top of each other. Also exactly how the charts look is not that important either, something like what I have would be great. Chart title and series names are important...i.e. they need to keep changing as the data changes. If anyone needs more info or has questions please just let me know. Please any help anyone can give is greatly appreciated.


Sorry I asked this question on another board.... here is the URL so no one has to duplicate work. You can check there before answering as well. I really do appreciate any and all help. I just was looking for help from as many places as I could get.

Everytime I use the Charts.Add method, I get the pop up "Method or data member not found." and it highlights the word 'Add' in the code. I'm using Office 2000. Would appreciate any help. Thanks.



Hopefully a quick question

I'm trying to enter a formula via vba, which is

"",LEFT(C2,20)&" - "&LEFT(D2,13),"") 

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

having problem with the mid section ( &"-"&)

I think I need to use Ascii charaters ( as with entering "")

but no certain how to as far as

& Chr$(38) & "-" & Chr$(38) & 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but this isn't correct

Any help appreciated.



I am trying to change the weight of the border of a column chart in VBA using the following code:

 Worksheets("Overall Chart Time").ChartObjects("ChartTimeQty").Chart.SeriesCollection(SCItem) 
    .Values = ValueStr 
    .Name = NameStr 
    .XValues = xValueStr 
    .ChartType = xlColumnStacked 
    .Format.Line.Weight = 1.75 
    .Border.LineStyle = xlLineStyleEn 
    .Border.Color = RGB(0, 0, 0) 
    .Interior.Color = Colour 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The .Format.Line.Weight seems to ignore the decimal places i.e. .Format.Line.Weight = 1.75 is the same as .Format.Line.Weight = 1

I am using Excel 2007.


I have two problems.

1) i have an application that should import data from multiple excel files (with the same data scheme, data not ordered in colums or rows, but labeled) into one access table. I want to write a vba script to do that, is it possible to run excel via vba, get the data I need as a variable (e.g. variable1 = range(A26)) and then build a sql query with those variables, inserting the data into access database - like insert into table values(variable 1, variable2, variable3.... and so on?

2) is it possible to do it in the opposite direction? i mean, to select the data with an sql query and insert it into the excel file via vba?

Thank you for your help,


I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed.

The logic, not in true VBA syntax, is as follows;

If  Is installed Then 
    Printout using  
    Printout using  

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.

Any suggestions?



I am trying to import a text file via VBA, I have to import via vba as
the file i am importing is not consistently formatted
the code im trying to use is shown here

    Dim strImport As String 
    Dim lngChars As Long 
    Dim intFile As Integer 
    intFile = FreeFile 
    Open "C:Documents and Settings****Desktop****.txt" For Input As intFile 
    lngChars = LOF(intFile) 
    strImport = Input(lngChars, intFile) 
    sImportAll = strImport 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the problem with the above code is its not importing all the text that were in the text file.
only part of the text file is its impoting.
need help.
Thanks in Advance

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