Every time I think I've got the concepts and syntax for Names in VBA
straight I discover
something I don't know how to do. The current problem is exemplified below:

Private Sub TheButton_Click()
Dim nIn As Integer, nOut As Integer

nIn = [mixer1!nInputs] ' This works... nIn is 6

Dim str As String
str = ActiveSheet.name & "nInputs" ' this forms "mixer1!nInputs"
nIn = [str] ' Fails, obviously because str is a string.

MsgBox (nIn)

End Sub

nInputs has been defined as a Name local to Worksheet mixer1, and the cell
which it RefersTo has the
integer 6 in it. Thus [mixer1!nInputs] gives, as it should, 6. However, I
want to generalize this
so as to pick up the nInputs value from the ActiveSheet, as opposed to
hard-wiring a particular
sheet name.

The (obviously lame) attempt builds "mixer1!nInputs", but since it is a
String there is a type mismatch for
the Evaluation operator [expression]. Now, I can understand that, as I do
understand the difference
between xxx and "xxx". What I can't come up with is the proper syntax for
what I want to do.

Help!

TIA

Ed

I think the context you are looking for is:

"mixer1!" & nInputs

Allan Koodray
-----Original Message-----
Every time I think I've got the concepts and syntax for
Names in VBA
straight I discover
something I don't know how to do. The current problem is
exemplified below:

Private Sub TheButton_Click()
Dim nIn As Integer, nOut As Integer

nIn = [mixer1!nInputs] ' This works... nIn is 6

Dim str As String
str = ActiveSheet.name & "nInputs" ' this
forms "mixer1!nInputs"
nIn = [str] ' Fails, obviously because str is a
string.

MsgBox (nIn)

End Sub

nInputs has been defined as a Name local to Worksheet
mixer1, and the cell
which it RefersTo has the
integer 6 in it. Thus [mixer1!nInputs] gives, as it
should, 6. However, I
want to generalize this
so as to pick up the nInputs value from the ActiveSheet,
as opposed to
hard-wiring a particular
sheet name.

The (obviously lame) attempt builds "mixer1!nInputs", but
since it is a
String there is a type mismatch for
the Evaluation operator [expression]. Now, I can
understand that, as I do
understand the difference
between xxx and "xxx". What I can't come up with is the
proper syntax for
what I want to do.

Help!

TIA

Ed



.

the square brackets are short for evaluate.

This seemed to work ok for me:

str = ActiveSheet.Name & "!nInputs" ' this forms "mixer1!nInputs"
nIn = Application.Evaluate(str)
MsgBox nIn





Jag Man wrote:

Every time I think I've got the concepts and syntax for Names in VBA
straight I discover
something I don't know how to do. The current problem is exemplified below:

Private Sub TheButton_Click()
Dim nIn As Integer, nOut As Integer

nIn = [mixer1!nInputs] ' This works... nIn is 6

Dim str As String
str = ActiveSheet.name & "nInputs" ' this forms "mixer1!nInputs"
nIn = [str] ' Fails, obviously because str is a string.

MsgBox (nIn)

End Sub

nInputs has been defined as a Name local to Worksheet mixer1, and the cell
which it RefersTo has the
integer 6 in it. Thus [mixer1!nInputs] gives, as it should, 6. However, I
want to generalize this
so as to pick up the nInputs value from the ActiveSheet, as opposed to
hard-wiring a particular
sheet name.

The (obviously lame) attempt builds "mixer1!nInputs", but since it is a
String there is a type mismatch for
the Evaluation operator [expression]. Now, I can understand that, as I do
understand the difference
between xxx and "xxx". What I can't come up with is the proper syntax for
what I want to do.

Help!

TIA

Ed

--

Dave Peterson