PDA

View Full Version : two questions.....public variables and naming charts



divingdingo
11-26-2007, 05:12 AM
with the understanding that i might be abusing the collective knowledge of this forum by asking question after question without being able to offer any advice of my own (yet!), i ominously offer up a few more problems to those who care!!!

1.) first of these is regarding declaring public variables.

i have a module now which includes many subfuctions. some of these functions rely on the same variable to run. that of the array size of the original sheet. at the moment at the beginning of each sub i declare a variable then set the variables number.

i know that i should be able to declare the variable at the top of the module so that all subs can use it however my efforts have been met with limited success (and when i say limited, i mean no success!!)


2.) the second is regarding naming charts when created so that conflicts don't exist

when my first chart is created it is named chart 1, then the next; chart 2....etc. however if i want to re-run the marco, or run my chart producing marcos in a different order then i get a naming conflict.

i've tried a few things like:

ActiveSheet.Shapes.AddChart.Select
charta = ActiveChart.Name
Chart(charta).Select
Chart(charta).Name = "speed"

similar to how i name a sheet, but this hasn't worked.
has anyone got any suggestions?



this is probably basic to many of you, and i do try looking in the MS help files, but i still don't seem to get anywhere.

thankyou kind people


mark

Simon Lloyd
11-26-2007, 05:18 AM
For your Public Variable enter something like this:

Public MyRange As String
in a standard module, it will then be available throught your workbook, so if you used this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MyRange = ActiveSheet.UsedRange.Address
End Sub
in the ThisWorkBook module then you could always call on MyRange in a module to represent the used range of the active sheet. As for charts i have no idea what i'm doing with them!

figment
11-26-2007, 09:10 AM
for the second question try something like this

Dim charta As Chart
Set charta = Charts.Add
charta.Name = "Speed"

i dont know what version of excel