Declaring variable names dynamically

[Mbrolass]

Hi,

Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:

Please Login or Register  to view this content.

however VB doesn't seem to like my node&i

Can you help?

Thanks,

Mbrolass

[jetted]

Did you try it with spaces node & i

[Mbrolass]

I did. I received a "compile error Expected: =" on the first run and if I click run immediately after (which I did to check the error message as I had removed it without taking much notice of what it exactly said) I get an "compile error: Syntax error".

[jetted]

What is the content of cell A2, I am assuming a number if not we have a problem.

[jetted]

Try this format Assuming the value in range a2 is a number this should work

Please Login or Register  to view this content.

[Mbrolass]

it's an integer.

To illustrate simply I have provided a couple of programs, the first works and the second illustrates the problem. I have an integer of 4 in cell A1 of Sheet1

This works:-

Please Login or Register  to view this content.

This does not:-

Please Login or Register  to view this content.

although it is simple with only 4 variables but I can have any number of variables, perhaps thousands; which is far too time consuming and not very automated if required to list everytime the program starts!!! The number of variables is determined by the user.

[jetted]

Not sure what you are trying to do. A sample of your work would be needed
The following code compiled with no error.

Please Login or Register  to view this content.

[MrShorty]

Is there some reason you haven't tried doing this with an array?

Please Login or Register  to view this content.

Memory is cheap these days, so you can simply Dim the array for more elements than you will ever need. You can also use the Redim statement to make an array larger or smaller.

[merlinmagic]

I think what is trying to be done is to use 'i' to dynamically create a unique variable name i.e. nodei is a variable name in itself whereas node & i should become node1.

However I believe and I am only new to VBA but have years of working on automated testing tools that you can't dynamically assign variables in this way as the compiler doesn't know to assign node & 1 together for example.

The first example works because each declared variable is exclusive and unique. The way around all of this problem would be to use an array either one dimensional or two dimensional. Then you can use 'i' values and have a single declaration. Also doesn't matter how big it gets because you can dynamically assign the limits.

I'm sure that's all as clear as mud now.

[Mbrolass]

Thanks for trying to help me solve this problem. You are right that your code works but unfortunately you have only defined 1 variable (nodei) whereas I need m variables (in my example above m = 4, so my four variables will be named: node1, node2, node3, node4).

I am currently trying to solve this problem using an array but I'm not sure if VBA allows arrays of objects, where each object can have a number of characteristics (e.g. probability tables, number of states, name of variables, parents, etc). Do you know if this is possible???

[Mbrolass]

Mr Shorty and Merlinmagic you are both correct. I was originally trying to do this without an array but now know I can not. I've tried Mr Shorty's code and it is doing exactly as I require in this simple case. I'm currently trying to extend this notion with an object in each array cell.

Thanks for your help

[merlinmagic]

Just knocked this up if you are interested:

Please Login or Register  to view this content.

[merlinmagic]

Thanks for trying to help me solve this problem. You are right that your code works but unfortunately you have only defined 1 variable (nodei) whereas I need m variables (in my example above m = 4, so my four variables will be named: node1, node2, node3, node4).

I am currently trying to solve this problem using an array but I'm not sure if VBA allows arrays of objects, where each object can have a number of characteristics (e.g. probability tables, number of states, name of variables, parents, etc). Do you know if this is possible???

You can also declare two dimensional arrays and more if needed.

To address the array it will be something like myarray(1, 1) myarray(1, 2) myarray(1, 3).

It does get a lot more complicated though. Sometimes best to draw out a grid on a bit of paper with the addresses and the values they hold.

[Mbrolass]

Hi,

I have attached a copy of my file. If anyone wants to run the code you'll need to download the latest version of Netica from www.norsys.com and in VBA you'll need to reference the Netica 1.0 Object library (via tools/references). Please ignore any code in sheets 1 and 2... the only code of interest is in Sheet3 and module1. I'm working with sheet3 at the moment and referencing the static version in module1 for clarification and validation only.

The problem I am having, which I tried to simplify above, is to dynamically create the network with varying number of nodes.

The code:

Please Login or Register  to view this content.

does not work, however further down the program I have used a static variation (semi dynamic) which does work:

Please Login or Register  to view this content.

I'm getting an error 1004 and am completely lost now!!! I'm not sure if anyone can help... ideally I would like the program to read the relevant information from Sheet3 and produce the same output as the program in Module1

Oh well... here's hoping

Mbrolass.

[Mbrolass]

Oops... the m = 1 should be commented out in the code above and the application on Norsys is the free version of Netica VB API

[Mbrolass]

All sorted now... I managed to get it working. Thanks for all your help

[sam80]

Hello Mbrolass,
I am also very interested to make dynamic variable names as an object variable and not as an array container. Did you find any solution?

Br,
Sam