references between macros not working

[Guest]

I have a number of macros that I am using to perform a number of functions
within a workbook.
As part of that, I am using code to name cells with the idea that I would be
able tor reference the cell values later. According to the vba intermediate
window it works - until that macro ends. The very next macro that runs, even
within the module, does not retrieve the info from the cell referenced by
name.
I have no idea what I am doing wrong. I would try using a "public"
statement except I can't get those to work either. As far as I can tell,
their is no difference in syntax between a public statement and a dim
statement other than the word "public" instead of "dim".
The public thing may be a red herring anyway.

Please help!

 

[Tom Ogilvy]

Help with what?

You don't show any code.

In a new module put in code like this

Public MyName as String

Sub SetMyName()
Activecell.Name = "ABCD"
MyName = "ABCD"
End Sub

Sub ShowMyName()
msgbox Range(MyName).Address & " " & MyName
End sub

Perhaps that will give you some ideas. Using Public within a procedure will
have no effect. A public variable must be declared at the top of the module,
outside any procedure.

 

[Guest]

Thanks for your help Tom,
I have been playing with your code.
I am successful in some variations such as:
I can use code to name a cell. I can use code to perform a calculation.

Let's say that I name a cell "ABCD" and it contains the number 6. I have a
public statement at the beginning of the module that reads Public mynumber as
integer.

From here, I can enter "=ABCD" in another cell and get 6. That is what I
want.

In your code, "myname" was entered by the code-writer.

I think my trouble is:
I want code to read the value that the user enters into ABCD so that it can
be used in a calculation. Essentially, I want the code to automatically
enter the "=ABCD" for me.

Papa

 

[Guest]

I figured it out Tom.
Instead of trying to refer to the value of ABCD, I needed to use the
formular1c1="=abcd" statement.
Thanks.