Picking up Excel named variables in VBA

[SimonLeefe]

I have an Excel workbook containing named variables across several worksheets. In one of these worksheets I have a "run solver" button, which kicks off a VBA routine. The first thing this routine does is allocate values of variables picked up from the workbook to local variables inside the VBA routine. I am using syntax as per the following example:

Private Sub RunSolver_Click()
Dim x As Double
x = Range("fred").Value
...
End Sub

This works fine PROVIDED the workbook variable 'fred' is in the same worksheet as the "run solver" button. However, I can't work out how to access variables from other worksheets in the same workbook. Any suggestions?

[vb5prgrmr]

Okay, it has been awhile sinced I attempted to answer a VBA question, but my mind sort-clicked as the dark recesses of it stired... I believe there are two ways in which to do this. One is to declare a varible public in this workbook and the other is something like x=sheet1:range("fred").value as called from sheet two. If the syntax is wrong, sorry, the dust in the corners is quite deep now... :)

Good Luck

[SimonLeefe]

Hi and thanks for the response. I've tried numerous variants on the second theme, to no avail. As for the first, the "name manager" dialogue in Excel says that all of my varisables already have workbook scope. I'm not sure if this is the same as making them "public", or if not how to do so.

Because time is pressing, I've had to resort to reorganising the spreadsheet so that all the variables required by the VBA routine are in the "owner" worksheet: a bit messy but that's life! If we can come up with a better solution, that would still be most useful for future reference.

[ggeu]

Dear,

Sometimes it's helpful to let Excel write the code by using the "Record New Macro" from the Tools menu.

in VBA there will be a module with your macro code from which you can learn a lot.

for your problem the macro is:
(on sheet1 there is a range with name: sht1val, on sheet2 there is a range with name: "sht2val",...)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/01/2010 by Guido Geurs
'

'
    Range("D10").Select
    ActiveCell.FormulaR1C1 = "=sht1val+sht2val+sht3val"
    Range("D11").Select
End Sub

for the command button we can write:

Private Sub CommandButton1_Click()

Range("B3").Select
ActiveCell.FormulaR1C1 = "=sht1val+ sht2val+sht3val"

End Sub

OR

You can collect the values in a var with definition of a type (if there are different types to collect ) like this (see attachment)

Private Type Type_Values
   sheet1_val As Integer
   sheet2_val As Integer
   sheet3_val As Integer
   sheet3_txt As String
End Type
Private Sub CommandButton1_Click()
Dim LIST_VALUES As Type_Values
'§ collect the values
   With LIST_VALUES
      Range("sht1val").Activate
      .sheet1_val = ActiveCell.Value
      Application.Goto Reference:="sht2val"
      .sheet2_val = ActiveCell.Value
      Application.Goto Reference:="sht3val"
      .sheet3_val = ActiveCell.Value
      Application.Goto Reference:="sht3OK"
      .sheet3_txt = ActiveCell.Value
'§ calculate
      Sheets("sheet1").Select
      Range("B3").Select
      If .sheet3_txt = "OK" Then
      ActiveCell.Value = .sheet1_val + .sheet2_val + .sheet3_val
      Else
      ActiveCell.Value = "IS NOT OK"
      End If
   End With
End Sub

I hope this will help You,
br,

[davesexcel]

x=worksheets("Sheet2").range("A1")

You are using a named range, it is strange it is not getting picked up.

[SimonLeefe]

Thanks also to ggeu and davesexcel. We can consider the problem solved.