dynamic array as public variable

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2010

• #2

I'm sorry but that doesn't make a lot of sense.

You want to create a public variable, but only want that available within one sub?

What do you actually want to do?

PS Arrays are zero-based in Excel VBA, so that might be the reason you are getting the error you describe.

 

Upvote 0

A

alkarkar

Board Regular

Joined

Sep 18, 2005

Messages

125

• Mar 6, 2010

• #3

Norie said:

I'm sorry but that doesn't make a lot of sense.

You want to create a public variable, but only want that available within one sub?

What do you actually want to do?

PS Arrays are zero-based in Excel VBA, so that might be the reason you are getting the error you describe.

Click to expand...

I want to calculate the MyVar() only once and be able to use it in all the subs in the same module.
So for my example the MyVar(1),MyVar(2),MyVar(3),MyVar(4) will be available in other subs as long as workbook stays open.
Hope this makes sense

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2010

• #4

I'm sorry but it doesn't, perhaps I'm missing something.

What do you mean by 'calculate the MyVar()'?

 

Upvote 0

A

alkarkar

Board Regular

Joined

Sep 18, 2005

Messages

125

• Mar 6, 2010

• #5

I get the error when I run the second macro,
if I understand it it says that MyVar is not declared (?)

 

Upvote 0

A

alkarkar

Board Regular

Joined

Sep 18, 2005

Messages

125

• Mar 6, 2010

• #6

Sorry I wasn't clear enough.
Where I say 'calculate the MyVar() only once' the real code performs calculations
so is something like this:
MyVar(x) = range("A1").Value * range("B1").Value ....etc

These calculations I want to perform only once

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2010

• #7

You have declared the variable/array but I really don't understand what you are trying to do which makes it a bit difficult to help.

The code you've posted just doesn't seem right and I''ve got a feeling it isn't the 'real' code you are trying to deal with.

 

Upvote 0

A

alkarkar

Board Regular

Joined

Sep 18, 2005

Messages

125

• Mar 6, 2010

• #8

Something like the below would be great but it does not work:

Public MyVar(1 To 4)


I'll try to explain better in a while .
Thanks Norie

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,801

Office Version

1. 365

Platform

1. Windows

• Mar 6, 2010

• #9

If you want your array variable available to all procedures in the module, declare the variable with the 'Dim' statement before the first procedure in the module. And, remove the second 'Dim' statement within the first procedure. Also, the second procedure uses a looping structure. The following should be more efficient...

Code:

Dim MyVar()
Sub test1()

ReDim MyVar(1 To 4, 0)

    For x = 1 To 4
        MyVar(x, 0) = "ffffff"
    Next x

End Sub
Sub test2()

    FirstRow = 1
    LastRow = FirstRow + UBound(MyVar) - 1
    Range(Cells(FirstRow, 1), Cells(LastRow, 1)).Value = MyVar
    
End Sub

 

Last edited: Mar 6, 2010

Upvote 0

A

alkarkar

Board Regular

Joined

Sep 18, 2005

Messages

125

• Mar 6, 2010

• #10

Thanks Norie for your time,
It was simple at the end.
Al I had to do was to delete the declaration inside the sub:
Dim MyVar()

So only left the :
Public MyVar()

end is working fine.If I get it right I declared it at the beginning as public but then inside the sud I declared it again so it stayed valid only inside the procedure (sub)

Thanks Norie


Edit: Thanks Domenic

 

Upvote 0