Charts, partly because they're so flexible, are complicated objects. A chart object object exists either as a chart sheet or as an embedded chart object on a worksheet. The chart object has a chart property, which is a chart (class) that has an enormous number of properties.

The following example embeds an XY Scatter chart on the active worksheet. It assumes that the data is in columns, with the first row being a header. It will automatically determine the number of series from the number of headers, and will determine the number of data points from the number of X-values in  column A.

At the end of this topic there is a table of numbers making up the data for this example. If you are reading this online, you might be able to copy and paste them into an Microsoft Excel  worksheet and try the script. The precise numbers don't matter much. You can see that columns A, B, C were just filled from 1 to 40 (in rows 2 to 41). Column D uses a formula: =MAX(A:A)/2+(1+SIN(A2)*2*PI()), also filled from row 2 down to 41. Column E is a random distribution of the same numbers 1-40 (making Y4's graph plot interesting as the numbers flip all over the place).

Public Sub CreateEmbeddedChart()
	Dim oChartObj As ChartObject
	Dim rHeaders As Range
	Dim rData As Range
	Dim i As Long
   
	' Dynamically grab the data in columns
	With ActiveSheet
	'Find the headers in the first row
	Set rHeaders = .Range("A1").Resize(1, _
		.Cells(1, .Columns.Count).End(xlToLeft).Column)
	'Now find the number of data rows.
	Set rData = .Range("A2:A" & .Range("A" & _
		.Rows.Count).End(xlUp).Row).Resize(, rHeaders.Count)
	If rHeaders.Columns.Count = 1 Or _
		rData.Rows.Count = 1 Then Exit Sub  'no data
      
	'Create chart object
	Set oChartObj = .ChartObjects.Add( _
		Left:=400, _
		Top:=100, _
		Width:=500, _
		Height:=400)
 
	'Now build the Chart within the ChartObject
	With oChartObj.Chart
		.ChartType = xlXYScatterSmooth 'define chart type
 
		'Add each series
		For i = 2 To rHeaders.Count
			With .SeriesCollection.NewSeries
				.Values = rData.Columns(i)
				.XValues = rData.Columns(1)
				.Name = rHeaders.Cells(i)
			End With
		Next i
                   
		'Add Titles and Format Chart and Axes
		.HasTitle = True
		With .ChartTitle
			.Caption = "My XY Scatter Chart"
			.HorizontalAlignment = xlHAlignCenter
			With .Font
				.Name = "Verdana"
				.Size = 12
				.Bold = True
			End With
		End With
		With .Axes(xlCategory, xlPrimary)
			.HasTitle = True
			With .AxisTitle
				.Caption = "X Values"
				With .Font
					.Name = "Arial"
					.Size = 10
					.Bold = True
				End With
			End With
		End With
		With .Axes(xlValue, xlPrimary)
			.HasTitle = True
			With .AxisTitle
				.Caption = "Y Values"
				With .Font
					.Name = "Arial"
					.Size = 10
					.Bold = True
				End With
			End With
		End With
          
		'Format Plot area
		With .PlotArea
			With .Border
				.Color = vbBlue
				.LineWeight = 1
				.LineStyle = xlContinuous
				.Transparency = 0
			End With
			With .Fill
				.Visible = True
				.ForeColor.RGB = RGB(150, 200, 255)
				.Transparency = 0.5
			End With
		End With

		'Format Legend
		.HasLegend = True
		With .Legend
			.Position = xlRight
			With .Border
				.Color = vbBlue
				.LineWeight = 1
				.LineStyle = xlContinuous
				.Transparency = 0
			End With
			With .Fill
				.Visible = True
				.ForeColor.RGB = RGB(150, 200, 255)
				.Transparency = 0.5
			End With
			With .Font
				.Name = "Arial"
				.Size = 10
				.ColorIndex = 5
			End With
		End With
	End With
	End With
End Sub

Here is the AppleScript (make it on another sheet if you try both).

tell application "Microsoft Excel"
	--dynamically grab the data in columns
	tell active sheet
	--Find the headers in the first row:resize range A1 
	--to last used cell of row 1
		set rHeaders to get resize range "A1" column size ¬
			(first column index of (get end (cell ¬
			(count columns) of row 1) ¬
			direction toward the left))
		--Now find the number of data rows
		--and set rData to whole range from row 2
		set rData to get resize range ("A2:A" & first row index ¬
			of (get end range ("A" & (count rows)) direction ¬
			toward the top)) column size (count (columns ¬
			of rHeaders))
		if (count (columns of rHeaders)) = 1 or (count ¬
			(rows of rData)) = 1 then return --  no data, so quit

		--Create chart object
		set oChartObj to make new chart object at end ¬
			with properties {left position:400, top:100, ¬
			width:500, height:400}
          
		--Now build the Chart within the ChartObject
		tell chart of oChartObj
			set chart type to xy scatter smooth -- define chart type
              
			-- add each series
			repeat with i from 2 to (count (columns of rHeaders))
				set newSeries to make new series at end ¬
					with properties {series values:¬
					(column i of rData), xvalues:¬
					(column 1 of rData), name:¬
					(value of cell i of rHeaders)}
				--  need to specify _value_ (no default property)                                   
			end repeat
              
			--'Add Titles and Format Chart and Axes
			set has title to true
			tell its chart title -- needs 'its !!
				set caption to "My XY Scatter Chart"
				tell font object
					set name to "Verdana"
					set font size to 12
					set bold to true
				end tell
			end tell
              
			set categoryAxis to get axis axis type category axis ¬
				which axis primary axis
			tell categoryAxis
				set has title to true
				tell its axis title -- needs 'its' !!
					set caption to "X Values"
					tell font object
						set name to "Arial"
						set font size to 10
						set bold to true
					end tell
				end tell
			end tell
              
			set valueAxis to get axis axis type value axis ¬
				which axis primary axis
			tell valueAxis
				set has title to true
				tell its axis title -- needs 'its' !!
					set caption to "Y Values"
					tell font object
						set name to "Arial"
						set font size to 10
						set bold to true
					end tell
				end tell
			end tell
              
			--'Format Plot area
			tell plot area object
				tell its border -- needs its
					set color to {0, 0, 255} 
					-- will this work?, if not:
					--set its color index to 5
					set line weight to 1
					set its line style to continuous
					-- no transparency property in AppleScript
					--set transparency to 0
				end tell
				tell its chart fill format object
					set visible to true
					--set fore color to {150, 200, 255} 
					--can't set color
					--set transparency to 0.5
					set foreground scheme color to 23
					set transparency to 0.8 -- the same color
				end tell
			end tell
              
			--format legend
			set has legend to true
			tell legend object
				set its position to legend position right
				tell its border -- needs its
					set color to {0, 0, 255} 
					-- will this work?, if not:
					--set its color index to 5
					set line weight to 1
					set its line style to continuous
					-- no transparency property in AppleScript
					--set transparency to 0
				end tell
				tell its chart fill format object
					set visible to true
					--set fore color to {150, 200, 255} 
					--can't 
					--set transparency to 0.5
					set foreground scheme color to 23
					set transparency to 0.8 -- the same color
				end tell
				tell its font object
					set name to "Arial"
					set font size to 10
					set its font color index to 5
				end tell
			end tell
		end tell
	end tell
end tell

There are two things the script cannot do that the Visual Basic for Applications (VBA) macro can. There is no transparency property of border in AppleScript, so you can't set it. As it happens, the macro sets the transparency to 0 (that is, opaque), which is the default anyway, so you won't see any difference.

The fore color property of the chart fill format class, which is the class of the chart fill format object property of both the plot area and the legend of the chart (that is, the fill color for the chart and the legend box to the right of it), is read-only in AppleScript. You cannot set it directly, and there is no alternate color index property that can be set instead.

However, there is another option. You can set the foreground scheme color index. There is no table of indexed colors. In this case, the foreground scheme color  23, and transparency setting 0.8 matches the color and shade made by the macro — RGB(150, 200, 255).

There are at least 100 scheme indices, so through trial and error, you can find a match for any color Excel has. Also, there are 17 other color schemes that can be set for the chart group property of the chart, so there's no end of colors.

You have to omit the line trying to set the fore color, although it compiles, because otherwise the script will error. In the code sample, it is commented-out both times and the foreground scheme color is set instead. The difference here between AppleScript and VBA is that, in AppleScript, this foreground scheme color property is a simple color type (an {r, g, b}) list of three integers). In VBA, there is a ColorFormat object that needs a further RGB property to set the color.

There are two places in the script that will error if you follow the macro syntax exactly. One is the following line near the beginning.

Set rData = .Range("A2:A" & .Range("A" & _
	.Rows.Count).End(xlUp).Row).Resize(, rHeaders.Count)

The other is the last line of the following block adding the series a few lines below it.

With .SeriesCollection.NewSeries
	.Values = rData.Columns(i)
	.XValues = rData.Columns(1)
	.Name = rHeaders.Cells(i)
End With

If you translate those directly as:

set rData to get resize range ("A2:A" & first row index of (get end ¬
	range ("A" & (count rows)) direction toward the top)) ¬
	column size (count rHeaders)

and

set newSeries to make new series at end with properties ¬
	{series values:(column i of rData), xvalues:¬
	(column 1 of rData), name:(cell i of rHeaders)}

you will get a fatal error in the first case, and you won't get the names of the series (Y1, Y2, Y3, Y4) in the Legend box in the second case. It will show the default names Series1, Series2, Series3, Series4 instead.

This is because in VBA there are default properties of many objects, which do not have to be spelled out in code. The default property of a row, or of any range, is the Cells property. Since rHeaders is a range that's actually a one-dimensional row, to Count the range is to count the cells, as if written as rHeaders.Cells.Count.

But in AppleScript, counting a range makes no sense because AppleScript does not have default properties. So the script errors. You need to change it to count cells of rHeader or count columns of rHeader, as shown in the script.

In the case of making the new series, you lose the names in the legend. That's because the default property of a Cell must be its Value property. So setting .Name = rHeaders.Cells(i), where Name is of String type, evidently sets the name of the series to the string  Value of Cell(i).

But in AppleScript, where there is no default property for cell, trying to set a name to a cell (a range) instead of to a string or Unicode text, just doesn't work. It doesn't cause an error (although might if you asked for the name afterwards), but it doesn't do anything. It leaves the names at their defaults of Series1,..., etc. Just set the name of the newSeries to value of cell i of rHeaders, and it works.

One last caution: a great many properties of many different objects have the same term for their 'type' (that is, class) as for the property itself. For example, chart title, axis title, and line style properties all refer to classes of the same name. That may seem less cumbersome than the font object property whose type is class font, the legend object property whose type is class legend, and so on. However, these constructions save the scripter from possible confusion.

When the name of the property is the same as the name of a class, AppleScript can get confused under two conditions: in whose clauses (always), and also occasionally when using tell blocks to the parent object of the property rather than using of constructions. VBA scripters writing AppleScript often use nested tell blocks to mirror VBA With blocks, as shown throughout this script. That means you have to be on guard, and use its before the property. It never hurts to use its, so a few extra are shown, and it's alright to always use it in tell blocks to the parent object.

There is much more to learn about charts. When you hit snags, be creative in trying to think of possible workarounds, and analytic to see if you were trying to take a shortcut through the syntax and lost your way.

Here are the numbers to test the sample chart: