Reusing array? 1

[sedgely]

i have the following in an excel module...

For Each sht In Array("first sheet", "second sheet", "third sheet", "fourth sheet")
        'do stuff
Next

i have other modules that use the same range of sheets, how do i access the array in one module from code in another module.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[HarleyQuinn]

Declaring the array as Public or Global outside of any Sub's or Functions should do it.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[sedgely]

Hi Harleyquinn
I have set the array as follows:

Public Function setArray()
Dim sheetArray As Variant
sheetArray = Array("first", "second", "third")
End Function

how do i now use that within another module? i have tried the following:

Function doSomething()
For Each sht In sheetArray
Sheets(sht).Range("a1").Value = "hello"
Next
End Function

but get a type mismatch error when i run it. got any ideas? i know it's probably me being a bit dense but there's nothing new there. lol

Cheers, Craig
Si fractum non sit, noli id reficere

 

[HarleyQuinn]

Try this:

Option Explicit

Public sheetArray As Variant

Public Function setArray()
sheetArray = Array("first", "second", "third")
End Function

Function doSomething()
setArray
For Each sht In sheetArray
Sheets(sht).Range("a1").Value = "hello"
Next
End Function

That should do it for you.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[sedgely]

Hi harleyquinn
tried that and get a "variable not defined" error when it gets to "For Each sht"
I have tried adding "dim sht as worksheet" but that gives "object required" error.
I just can't get my head round it, maybe i should just give up and go home

Cheers, Craig
Si fractum non sit, noli id reficere

 

[HarleyQuinn]

For the minute take Option Explicit off and then try it.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[sedgely]

Hey harleyquin
THANKS, don't understand why, but that worked!

have a star



Cheers, Craig
Si fractum non sit, noli id reficere

 

[HarleyQuinn]

If you take off Option Explicit it means that you don't have to declare variables and Vb takes them as Variants (as far as I know). This can be a bad programming practise as it can be very difficult to see where variables are set/changed etc.
To use the code I supplied with Option Explicit try:

Function doSomething()
Dim sht As Variant
setArray
For Each sht In sheetArray
MsgBox sht
Next
End Function

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[sedgely]

Cheers harleyquinn
that worked and i understand now ;-)

one last question do i need to use 'option explicit' on each module?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[HarleyQuinn]

Glad I could be of help

In each module yes. However, if you put all of your functions into one module you will only have to declare it once...


Harleyquinn

---------------------------------
For tsunami relief donations