VBA: Is it possible to define a range of string variables dynamically based upon the number of elements in array?

Thank you for looking at my question,

Currently I am working for an organisation (global!!!) that will not allow anybody access to the SQL data tables that are the basis of their ERP system - but I am supposed to be extracting data from the ERP system for custom reports, won't even consider something like Crystal Reports.

So I have to export a range of standard reports from the ERP system to .csv files - these are, in essence, a reproduction of what would be the printed document with each single line of the printed report written into a csv file and a comma being placed at the end, ending up with all data in column A of the .csv - see attached example

Data extraction is then a case of string manipulations.

I am currently working on something that responds to user-defined ranges (monthly output by year and month - Start Year and Month to end Year and Month)

Is it possible to define a range of string variables in response to the range set by the user - if the user specifies a range of 6 months then 6 string variables are defined eg strMonth01 to strMonth06?
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>if the user specifies a range of 6 months then 6 string variables are defined eg strMonth01 to strMonth06?

try define your variable as array instead?


Dim Arr() as String
Redim Arr(5)

Open in new window

[ fanpages ]IT Services ConsultantCommented:
...Is it possible to define a range of string variables in response to the range set by the user - if the user specifies a range of 6 months then 6 string variables are defined eg strMonth01 to strMonth06?

I am unclear how this will help with your predicament, but I will try to address your question...

What would the content of these string variables be; just Month Name (or three-character literal) values ("January"/"Jan", "February"/"Feb", "March"/Mar", & so on)?

By "range set by the user", do you mean an MS-Excel range of cells, or simply a defined quantity of months (or the first month & the last month) required?

If you are just looking for a Month Name, there are a few methods to achieve this without using variables for each within a given range:

MonthName(Month As Long, [Abbreviate As Boolean = False])

For example,

MsgBox MonthName(1) ... will display January
MsgBox MonthName(1, True)... Jan


Dim strMonth As String
strMonth = Format$(DateSerial(2015,1,1),"Mmmm")

Will set strMonth to "January"


...to "Jan"


...also "January"

As I said, I am not really sure what you are looking to achieve, so it is difficult to advise properly without clarification.
Gary CroxfordOperations Support AnalystAuthor Commented:
Ryan Chong, Fanpages,

By 'range set by the user' I mean a defined quantity of months.

I've not described what I'm trying to achieve very well.

I have a two-dimensional array that contains Year and Month Number for each month in the user specified period.

What I want, if it possible, is to dynamically create a string variable name (stringX where X is a number that increments from 1 to the number of element pairs in the array) and then declare those string variables (Dim stringX as String).

If there are 6 element pairs in the array then something that creates:

Dim string1 as String
Dim string2 as String
Dim string6 as String

Can this be done?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gary CroxfordOperations Support AnalystAuthor Commented:
I am breaking various nuggets out of the example csv file I attached earlier and arranging them in columns across a worksheet for further analysis - making the data more suitable for interrogation by VLookup from another workbook etc and need to get the correct value in the relevant Year, Month Column.

I could declare 12 string variables up front and tell the user that he must choose a spread of 12 months but I wanted to be a bit less restrictive and let the user define the spread that he/she needs.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>stringX where X is a number that increments from 1 to the number of element pairs in the array) and then declare those string variables (Dim stringX as String).

Since the idea of declaring "dynamic" variable is to store the value, a dynamic array should be good enough to meet your purpose. That's why I suggest to use array as stated in my first comment above: ID: 40927880

for example, if you're declaring a period of 5 years, you can try:
Dim NoofYear As Integer
Dim ArrYear() as String
NoofYear = 5
Redim ArrYear(NoofYear -1)

Open in new window

so now, ArrYear contains 5 allocations to store the year's values.

If you have string variable strYearN:

Dim strYear1 As String
Dim strYear2 As String
Dim strYearN As String

ArrYear(0) will be similar to strYear1
ArrYear(1) will be similar to strYear2
ArrYear(N-1) will be similar to strYearN

This is similar for month's array.

I hope this is what you want and make sense to you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
You can dynamically create variables at run-time (see below), but I am still not sure how this will help (you).

Why are you generating a new set of variables when you already have a two-dimensional array?

How are the users selecting the date range?  Surely you just need a start & a finish month, not every month between.

Sorry, I think I must be missing what you are attempting to do, but perhaps there is some information you are keeping away from this question due to sensitivity with your data requirements.

Option Explicit
Sub Q_28705256()

  Dim lngLoop                                           As Long
  Dim objVBIDE_CodeModule                               As Object
  Dim objVBIDE_VBComponent                              As Object
  Set objVBIDE_VBComponent = ThisWorkbook.VBProject.VBComponents.Add(1)             ' 1: vbext_ct_StdModule
  Set objVBIDE_CodeModule = objVBIDE_VBComponent.CodeModule
  objVBIDE_CodeModule.DeleteLines 1&, objVBIDE_CodeModule.CountOfLines              ' Removes "Option Explicit" if added automatically
  objVBIDE_CodeModule.InsertLines 1&, "Public Sub Q_28705256_Create_Variables()"
  objVBIDE_CodeModule.InsertLines objVBIDE_CodeModule.CountOfLines + 1&, ""
  For lngLoop = 1& To 6&
       objVBIDE_CodeModule.InsertLines objVBIDE_CodeModule.CountOfLines + 1&, "  Dim string" & CStr(lngLoop) & " As String"
  Next lngLoop
  objVBIDE_CodeModule.InsertLines objVBIDE_CodeModule.CountOfLines + 1&, ""
  objVBIDE_CodeModule.InsertLines objVBIDE_CodeModule.CountOfLines + 1&, "End Sub"

  Set objVBIDE_CodeModule = Nothing
  Set objVBIDE_VBComponent = Nothing
End Sub

Open in new window

It is difficult to do exactly what you want. Variable names are for internal program use, so what they are called is immaterial outside of that.

If you want to assign a user-visible name to a variable which holds a value, you could create an array of a user type.

Type MyType
   strName As String
   intValue As Integer
End Type

Sub xxx()
   Dim MyTypes(6) As MyType
   MyTypes(1).strName = "Something"
   MyTypes(1).intValue = 123
 MsgBox "The value of " & MyTypes(1).strName & " is " & MyTypes(1).intValue

Open in new window

Gary CroxfordOperations Support AnalystAuthor Commented:

I sat and thought about it a little while longer and taking into account all that you'd said I realised how to achieve what I was trying to get to and that I didn't need to create variable names on the fly. You all contributed to my understanding the issue so I'd like to share the points equally between you - hope that's OK.
[ fanpages ]IT Services ConsultantCommented:
Fine with me, thank you,  Crxfrd.

Good luck with the rest of your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.