VBA UDF/Array

[jbehmoaras]

It's been a few years since I set one of these up but I referenced my old code that worked and cant figure out what I'm doing wrong now.

I created the following structure:
Public Type STRArr
occ As Variant
adr As Variant
revpar As Variant
period As Variant
year As Integer
End Type

delared an object
Public strFY() As STRArr

and just tried to redim the array

Public Sub popArr()
ReDim Preserve strFY(10)
End Sub

and am getting a variable not defined error ... not sure i understand why? Also i think i have the correct reference libraries installed.

 

[shg]

Works fine for me. Where are all these bits and pieces?

 

[jbehmoaras]

well, because i was having issues, i created the sub above popArr just to test it, so thats all i'm running.

maybe i dont have the right references setup after all and otherwise ... wtf

 

[Marcelo Branco]

Hi,

I think this is ok

Option Explicit
 
Public Type STRArr
    occ As Variant
    adr As Variant
    revpar As Variant
    period As Variant
    year As Integer
End Type

Public Sub popArr()
    Dim strFY(10) As STRArr
    
    strFY(1).occ = "something1"
    strFY(2).occ = "something2"
    'and so on
End Sub

HTH

M.

 

[jbehmoaras]

Hmmm...
I've tried rebooting XL to no avail.
Can anyone posted suggested references that should be active so I can verify, I'm using a new installation of XL 2010 on my work machine so its possible it hasn't been configured correctly.

 

[Marcelo Branco]

My Active References are:

Visual Basic For Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library

HTH

M.

 

[jbehmoaras]

Hi,

I think this is ok

Option Explicit
 
Public Type STRArr
    occ As Variant
    adr As Variant
    revpar As Variant
    period As Variant
    year As Integer
End Type

Public Sub popArr()
    Dim strFY(10) As STRArr
    
    strFY(1).occ = "something1"
    strFY(2).occ = "something2"
    'and so on
End Sub

HTH

M.

when i try this i get a user-defined type not defined error

 

[jbehmoaras]

I tried copying the exact same text into a new file and it worked fine ....

This is very strange.... I need it to work in the original file.

 

[Marcelo Branco]

If you have the same Active References in both workbooks its very strange indeed...

I've made some search in the internet and have found that the "user-defined type not defined error" is mostly related with References.

If its not your case i dont know what to look for...

M.

 

[jbehmoaras]

If you have the same Active References in both workbooks its very strange indeed...

I've made some search in the internet and have found that the "user-defined type not defined error" is mostly related with References.

If its not your case i dont know what to look for...

M.

FYI

I've tried copying the code into a new module and it seems to be working fine for now. I dont think its possible to corrupt a module ...