CODEWITHSUNDEEP
excelvbachartsseries
rcmv
rcmv

Reputation: 151

How to set a series data with a variable in VBA (Excel)

I know that there are already some topics on this but I already try some solutions and nothing seems to work so I will just try my luck here.

I'm trying to set a series data from a chart through a variable. If I don't use a variable I have something like this:

   ActiveChart.FullSeriesCollection(1).XValues = _
        "='sheetname_xpto'!$A$2:$A$" & lw

The code above works. Now, I want to swap the 'sheetname_xpto' with a variable. So I already tried several options like:

Dim sname As String
sname = ActiveSheet.Name

    ActiveChart.FullSeriesCollection(1).Values = _
        "="sname"!"$C$2:$C$" & lw

or

 ActiveChart.FullSeriesCollection(1).Values = _
        "=Sheets(sname)!"$C$2:$C$" & lw

or

 ActiveChart.FullSeriesCollection(1).Values = _
        "='&sname&'!"$C$2:$C$" & lw

and many other. All of them, when pass the syntax test just put the 'sname' on the series data value and not the actual name of the sheet. Can someone help me?

Upvotes: 0

Views: 1016

Answers (1)

FunThomas
FunThomas

Reputation: 29652

You have to distinguish between the part that is executed in VBA and the formula that is executed by Excel. For VBA, your formula is just a string.

The VBA runtime treats everything between quotes as part of a string. It will not look into the string if it finds a variable or a function name or whatever. On the other hand, Excel does not know anything about variables used in VBA and therefore cannot read the content.

If you need the content of a variable as part of a string, use string concatenation (you do this already for the variable lw).

ActiveChart.FullSeriesCollection(1).Values = "=" & sname & "!$C$2:$C$" & lw

When dealing with formulas in VBA, i strongly advice that you write the formula into an intermediate variable - if something fails, it is easy to check the content of that variable with the debugger

Dim formula As String
formula = "=" & sname & "!$C$2:$C$" & lw
ActiveChart.FullSeriesCollection(1).Values = formula

Upvotes: 1

Related Questions