Varying the size of an array depending on source range

[BlueSkies]

Hi guys and girls,

I'd be really grateful for help on this one... I don't dare guess how many hours (days?!) I've been trying to solve this.

Background
I have made a Budget workbook with four sheets: one for the household, one for me, one for my partner and a "Reference" sheet for calculations, information storage etc. I have designed a UserForm for inputting real income/spend data into the Budget.

My procedures to prepare and populate the UserForm for data entry do the following:
StartingCell: determines where on the relevant Sheet to extract the first value from, depending on the month. e.g. March happens to be "G4".
ExtractData: uses a For/Next loop to hop down that column (sometimes skipping cells not intended to hold data -different Offsets) and on each iteration adds the activecell.value to an array, arrExtracted.
DataToRef: pastes these values into the Reference sheet (which will keep track of changes made during UserForm manipulation)
RefToArray: copies values from the Reference sheet to another array.
ArrayToForm: copies from this array into the UserForm.

We all have a different number of expenditure categories (my partner has the fewest, of course!) and I'd appreciate help explaining if/how I can vary the size of the array (just 1-D) depending on the active sheet at the time the button to begin the sequence is pressed. A variable, x (Integer) is declared Public and is set to reflect the size of the array I need (e.g. 20) early on but I can't figure out how to vary the array itself accordingly.

The whole point was to avoid triplicating the UserForms, code etc. and now I'm plain curious to know if this can be done.

As an aside, is it possible to use another For/Next loop in ArrayToForm to feed values into the textboxes of the UserForm along these lines:

With frmEditBudget

For n = 1 to x

.txtIn.value = arrExtracted
​

Next
​

?

Sorry for all the questions. And many thanks...



Alex

 

[DonkeyOte]

I must confess I don't follow everything here... but I think you are talking about trying to ReDim the array where UBound is based on ActiveSheet ?

Option Base 1
Dim iArray As Integer

Sub RoutineX()
Dim arrExtracted As Variant
Select Case UCase(ActiveSheet.Name)
    Case "SHEETX"
        iArray = 20
    Case "SHEETY"
        iArray = 10
    Case "SHEETZ"
        iArray = 15
End Select
'ReDim Prior to Population
ReDim arrExtracted(iArray)
'now populate etc...
'...your remaining code...
End Sub

Option Base 1 is used above, if you don't use this remember than setting iArray to 20 creates an Array of 21 (0 to 20 inclusive) so if you want 20 use 19... obviously change Array type to suit, ie arrExtracted() As String etc...

 

[BlueSkies]

Thank you very much for your fast response! I must admit I have been impressed by how mutually helpful and benevolent people are in VBA forum circles - and you're no exception.

Despite my feeble attempt at explaining the code issues I was having, you actually understood the problem perfectly. I think the key must be two-fold: Firstly, to declare the Array arrExtracted as a variant, and secondly to reDim it with a variable in the ().

I'll let you know how I get on.


Alex

(is there anything more addictive than Excel VBA programming?!)

 

[BlueSkies]

Here is the code, adapted from your example, which passed my testing

---in Module A---
Option Explicit
Option Base 1

Sub RoutineX()

Dim n As Integer
Dim Sheet As String

Sheet = "Alex" 'necessary only to set condition for testing purposes

Select Case Sheet
Case "Household"
iArray = 20
Case "Sally"
iArray = 16
Case "Alan"
iArray = 21
End Select

'ReDim Prior to Population
ReDim arrTest(iArray)

For n = 1 To iArray 'to verify correct assumption of array size
arrTest = n
Debug.Print arrTest
Next

'now populate etc...
'...your remaining code...

End Sub

---in Module B---

Public arrTest() As Currency
Public iArray As Integer


I have now implemented it into the main workbook and can safely say that if it doesn't work, I've made the mistake as the logic seems fine.