Use Argument in Module by generating the name of the argument in Vba

[padlocked17]

All,

I have a function that pulls in 12 arguments named Jan_ACC through Dec_ACC as variants.

I want my VBA to be able to build and reference the argument in an If/Else statement.

Basically I want to be able to reference the argument by doing something similar to below:

MonthName(Month(Date), True) & "_ACC"

Any ideas how I can pull this off?

 

[MarkK]

You can create a collection. Then you can assign a key to objects as you add them, and reference them using that key. But you need to use objects.
You could also use an array, where your function can put data into one or more of the 12 elements in the array--one for each month--and your code that consumes this array can reference it's members like ...

Function GetMonthDataArray as Variant
[COLOR="Green"]  'always returns a 12 member array
  'where first member is data for Jan, and so on...[/COLOR]
End Function

Sub YourSub
[COLOR="Green"]  'reference the array by month[/COLOR]
  If GetMonthDataArray(Month(Date)) = Something Then
[COLOR="Green"]    'do stuff[/COLOR]
  Else
[COLOR="Green"]    'do other stuff[/COLOR]
  End If
End Sub

But this looks cumbersome in that you create the 12 value array, of which only one value is used. Why not push the month into the function and only calculate what you need for that month ...

Function IsThisTheMonth(Month as long) as Boolean
[COLOR="Green"]  'use the month parameter to only calculate the data you are going to use[/COLOR]
End Function

Sub YourSub
[COLOR="Green"]  'pass the month to the function[/COLOR]
  If IsThisTheMonth(Month(Date)) Then
[COLOR="Green"]    'do stuff[/COLOR]
  Else
[COLOR="Green"]    'do other stuff[/COLOR]
  End If
End Sub

But in general I would always avoid naming something with what appears to be data, so an argument or variable named Bob_Name or ValueUnder1000 or GreenBox or Jan_ACC, these all seem to contain--in the name--data that you would expect to see in the value, and this is confusing and commonly indicates a poorly designed approach to the problem.
And if this doesn't help or isn't clear please explain in more detail what you're trying to do because I do think it's quite worthwhile to see a way around using something named Feb_ACC.
Cheers,

 

[padlocked17]

Thanks for the info thus far. The issue I'm running into is that the data that is given to me is in a text file, and unfortunately has 12 columns names Jan_ACC through Dec_ACC and I can't do anything really to change that.

So what I'm really trying to do is somehow determine which column of data to use in an If Else statement without using a Case Select and having 12 cases with an identical If Else statement with only the column referenced being different.

Does that help shed any light on what I might need to do?

Thanks!

 

[vbaInet]

I'm not getting the full picture here but what does your function look like?

 

[padlocked17]

Right now, the following is what I have.

Public Function RemMonth(Month_REQ As Variant, Qtr_REQ As Variant, SemiAnnual_REQ As Variant, Jan_ACC As Variant, Feb_ACC As Variant, Mar_ACC As Variant, Apr_ACC As Variant, May_ACC As Variant, Jun_ACC As Variant, Jul_ACC As Variant, Aug_ACC As Variant, Sep_ACC As Variant, Oct_ACC As Variant, Nov_ACC As Variant, Dec_ACC As Variant)
Dim CurMonNameVar As String
'Generates the name of the argument to use in the format Jan_ACC
CurMonNameVar = MonthName(Month(Date), True) & "_ACC"
    If Month_REQ > 0 Then
        If Month_REQ - CurMonNameVar > 0 Then
                    RemMonth = Month_REQ - CurMonNameVar
                Else
                    RemMonth = 0
                End If
    End If
 
End Function

 

[vbaInet]

And the problem with this is?

Why do you have lots of parameters? What are they for?

 

[padlocked17]

Unless I'm completely missing the big picture, the 12 arguments ending with "_ACC" are all columns in my tabular data that I need numbers from depending on the current month.

My current problem is that the following creates a string and doesn't simply use the argument that is passed into the function.

CurMonNameVar = MonthName(Month(Date), True) & "_ACC"

Does that make sense? If there is a better way of attacking this I'm all ears. I'm just stumbling through it the best I can.

Thanks.

 

[vbaInet]

If you're using a recordset you can easily do this. You can't evaluate a variable from its string name. However you can evaluate a function using Eval() but that will return a string.

Your other option is to use an associative array like that of a Dictionary object.

 

[boblarson]

Also, you can't put in parameters like that as it will expect a value inserted for each of them.

I think, if I read it correctly, you are looking for something like this (this sample assumes that you have the file linked as an Access table - or you could have imported the data into a table) and I'm not sure about your other input variables so this is without them. So, create a base query called qryMonthAcc and then save it and then you can use this code to modify it for use before using it as the basis for a report, form, etc.:

Function SetQDF(strInputMonth As String) 
Dim qdf As QueryDef
Dim strSQL As String
 
strSQL = "Select [" & strInputMonth & "_ACC] FROM TableNameHere"
 
Set qdf = CurrentDb.QueryDefs("qryMonthAcc")
 
qdf.SQL = strSQL
 
qdf.Close
Set qdf = Nothing

Then you can simply call the SetQDF whenever you want to modify the query's field you want it to refer to:

Call SetQDF("Apr")

or using a selection on a combo box that returns the appropriate month abbreviation:

Call SetQDF(Me.ComboBoxNameHere)

 

[padlocked17]

Bob,

I think that was what I was looking for.

Now would I have any issues using this for the call:

Call SetQDF(MonthName(Month(Date()),True))

 

[boblarson]

The month part would work if you used this:

Call SetQDF(Format(Month(Date),"mmm")

What's the TRUE part for?

 

[vbaInet]

Just what I was thinking Bob.

Fyi: If this was going to run for every record in a query then I would opt for the associative array method so it doesn't keep querying the db.

 

[boblarson]

Just what I was thinking Bob.

Fyi: If this was going to run for every record in a query then I would opt for the associative array method so it doesn't keep querying the db.

It wouldn't be something that should be running in a query. The function, as I have written it, would be called ONCE from a specific location and then you can open a report or form based on the changes to the QDF. If it ran for every record it would be ludicrous.

 

[vbaInet]

It wouldn't be something that should be running in a query. The function, as I have written it, would be called ONCE from a specific location and then you can open a report or form based on the changes to the QDF. If it ran for every record it would be ludicrous.

I hope not. But something tells me he might want to run it in a query

 

[boblarson]

I hope not. But something tells me he might want to run it in a query

That's why I SPECIFICALLY spelled out its use. This MODIFIES the query he will want to run - it doesn't go in the query. This changes the query dynamically so they can just run the report or query or form after this code has been run ONCE from a click event, a form open event, etc.

 

[boblarson]

Or at least I ATTEMPTED to spell it out.

 

[vbaInet]

Or at least I ATTEMPTED to spell it out.

I think he's got the message now