GRRR VBA Public array variable not working in userform

Question

I am creating a macro that counts how many hours I have assigned to a job over a period of a month. I have the code for the list of jobs and time spent on each job working, which saves each job name and the related hours in the array JobHours(i,j). Once I try and access this array in the userform it is not available.

My code in the module

Option Explicit
Public JobHours() As Variant

    Sub MonthlyHoursShow()
    Monthly_Hours.Show
End Sub

Sub MonthlyHours(SelMonth As Variant)
'Code which assigns values to JobHours() - 
    ReDim JobHours(1, 0)
    JobHours(0, 0) = "Job Number"
    JobHours(1, 0) = "Hours"
'There are a few loops to determine what values to collect, and they are then
'assigned to JobHours() as follows:
    cValue = UCase(ws.Cells(j, 2).Text)
    ReDim Preserve JobHours(1, JobNum)
        JobHours(0, JobNum) = cValue
        JobHours(1, JobNum) = ws.Cells(j, 27).Value
    Worksheets("25 Mar").Cells(8 + JobNum, 30).Value = JobHours(0, JobNum)
    Worksheets("25 Mar").Cells(8 + JobNum, 31).Value = JobHours(1, JobNum)
'where JobNum is a counter for the number of jobs, and is incremented as part of the loop.

    Debug.Print "Upper Bound of JobHours() = " & UBound(JobHours, 2) - 'Returns as 38
    Debug.Print "Total Number of jobs is " & Application.CountA(JobHours) - 'returns as 78

End Sub

and in my userform:

Private Sub CalcButton_Click()
SelMonth = MonthBox.Value

Call MonthlyHours(SelMonth)

'Debug.Print "Upper Bound of JobHours() = " & UBound(JobHours, 2) - 'Returns error
Debug.Print "Total Number of jobs is " & Application.CountA(JobHours) - 'Returns as 1

End Sub

If I don't comment the UBound command in the first Debug.Print line the I get the following error:

Compile Error: Expected Array

My understanding was that if the Array was declared as a public variable it will pass to a userform - In fact I had this working! I then saved the workbook, closed it and reopened and now have the problem??

I have no idea why this isn't working - it is my first real attempt at using an array. If you have any ideas they would be greatly appreciated.

Thanks, Dan

What code assigns the value to JobHours? Where is ubound being used? — shahkalpesh
– shahkalpesh, Commented Mar 22, 2016 at 6:47
You must define your "JobHours" array (with at least a ReDim statement) before acceding to it (like when you try to get its bounds). Besides when you exit the macro, you lose all variable values, be they Public or not. If you want to keep variables values from one macro session to another, you have to store them (maybe in a sheet, or in a txt file) and then retrieve them — user3598756
– user3598756, Commented Mar 22, 2016 at 7:40
The only way I can see you getting that error is if you have used JobHours somewhere else as a routine/module name. — Rory
– Rory, Commented Mar 22, 2016 at 10:59
Thanks @shahkalpesh. I have added some code to show how JobHours() is dimensioned and assigned values. I have also added the uBound line to the module code, as well as the form code, to show the different values they return. — Dan W
– Dan W, Commented Mar 22, 2016 at 22:27
@Rory, I checked all my code and couldn't find anything. It turns out I assigned a text field in the userform the name JobHours which was causing the problem. All fields in the userform are now labelled `Job_Hours' and it is all working :) Thanks! — Dan W
– Dan W, Commented Mar 22, 2016 at 22:31

Dan W · Accepted Answer · 2016-03-22 22:33:56Z

1

Thanks to @Rory I identified that a text field in the userform itself was called JobHours, which was causing the errors. It has since been labelled Job_Hours and it is all working :)

Thanks everyone for your comments and assistance.

Dan

answered Mar 22, 2016 at 22:33

Dan W

1311 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Lepelletier Over a year ago

you could still keep the same variable name and textbox label , if you use it with a reference like this : UserformName.JobHours (or Me.JobHours inside the form) , and ModuleName.JobHours. But i wouldn't recommend it (as for the possible confusion)

Thomas G · Accepted Answer · 2016-03-22 12:38:16Z

0

SInce we don't see all your code and how you populate the array, we can only do guesses.

My guess is that your issue is the ,2 in UBound(JobHours, 2)

Debug.Print "Upper Bound of JobHours() = " & UBound(JobHours, 2)

The second parameter of Ubound() is there to optionnaly specify the dimension. This is only usefull if you have a multidimensionnal array. I believe this is not your case and that you tell VBA to access a dimension that does'nt exists.

If I am wrong, edit your question and show us everything.

answered Mar 22, 2016 at 12:38

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

1 Comment

Dan W Over a year ago

Thanks Thomas G, I do have a 2D array, and I have edited my post to show how it is dimensioned and assigned values.

Collectives™ on Stack Overflow

GRRR VBA Public array variable not working in userform

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related