Error with using VBA array values in formula

[Ruandi]

I want to create an array from a sheet called Data (with a named range "Database") every time the worksheet opens, and then all calculations can be done from the array rather than referring to the sheet all the time. To Create an array I did the following:

IN MODULE 1:
Public 1_comp As Variant

Public Sub Auto_open()
1_comp = Worksheets("Data").Range("Database").Value
End Sub


Then I want to create my own formulas and use array values in it, eg:
IN MODULE 2:
Function 2_molwt(id)

2_molwt = 1_comp(id, 17)

End Function


The array works fine by itself, I can paste it to another sheet and it's perfect, but once I try to use it in the function it is as if the function does not know the array exists. How do I fix this, can anyone please help me?

[Fotis1991]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Ruandi]

I want to create an array from a sheet called Data (with a named range "Database") every time the worksheet opens, and then all calculations can be done from the array rather than referring to the sheet all the time. To Create an array I did the following:

IN MODULE 1:

Please Login or Register  to view this content.

Then I want to create my own formulas and use array values in it, eg:
IN MODULE 2:

Please Login or Register  to view this content.

The array works fine by itself, I can paste it to another sheet and it's perfect, but once I try to use it in the function it is as if the function does not know the array exists. How do I fix this, can anyone please help me?