1

What I am attempting to do is use an if statement to compare a specified value to a long list of variables. I want to implicitly reference the variable names for the if statement (as well as further processing) so that I don't have to make 20+ if statements. This is my code, and I get a type mismatch.

Public iFPYCol As Long
Public iTimeCol As Long
Public iBrgPosCol As Long
Public iBrgPosFailCol As Long
Public iDisLoadCol As Long
Public iDisLoadFailCol As Long
Public iMaxBrgLoadCol As Long
Public iBrgMaxLoadFailCol As Long
Public iBrgTravelCol As Long
Public iTravelFailCol As Long
Public iPlateLoadCol As Long
Public iPlateLoadFailCol As Long
Public iDepartureCol As Long
Public iDepartureFailCol As Long
Public iPlaneCol As Long
Public iPlaneFailCol As Long
Public iCamIndexFailCol As Long
Public iCycInterruptCol As Long
Public iCamAssistCol As Long
Public iCamAssistFailCol As Long
Public iCoverNoCol As Long

Sub Main()

'unrelated code

sNothing = IncrementColNum(iBrgPosFailCol, 2)

'unrelated code
End Sub

Function IncrementColNum(iCol As Long, iNum As Long) As String

Dim arrVariables() As String
arrVariables = Split("iFPYCol,iBrgPosCol,iBrgPosFailCol,iDisLoadCol,iDisLoadFailCol,iMaxBrgLoadCol,iBrgMaxLoadFailCol,iBrgTravelCol,iTravelFailCol,iPlateLoadCol,iPlateLoadFailCol,iDepartureCol,iDepartureFailCol,iPlaneCol,iPlaneFailCol,iCamIndexFailCol,iCycInterruptCol,iCamAssistCol,iCamAssistFailCol,iCoverNoCol,iTimeCol", ",")

For iCounter = 1 To UBound(arrVariables)

    If arrVariables(iCounter) >= iCol Then
        arrVariables(iCounter) = arrVariables(iCounter) + iNum
    End If

Next iCounter

'Return Nothing
IncrementColNum = "Nothing"

End Function

The type mismatch is because my array and iCol are different types. The problem is that I don't want to compare arrVariables(iCounter) to iCol, I want to compare the value of the variable whose name is arrVariables(iCounter) to iCol.

I have it in a function because I am going to be doing this many times; I looked up how to have a function return nothing, and it doesn't look like it is possible with VBA.

ilarson007
  • 65
  • 6
  • 1
    `arrVariables(1)` is "iFPYCol. and `iCol` is a number. Do you just want to check if `iCounter >= iCol`? I understand that `arrVariables(i) (where `i` is any value), won't ever return a number...so just check the counter? I may be misunderstanding... – BruceWayne May 12 '16 at 20:50
  • 1
    How about creating an array of `Long`s to store all your values. You could set up a (large) set of `Const` as array indexes to access a specific value in that array, or even a `Dictionary` of indexes and strings. Then you can loop through all the indexes of the array to make a quicker comparison. – PeterT May 12 '16 at 21:38
  • @BruceWayne I want to compare the value of `iFPYCol` to the value of iCol. They are both type `Long`. I tried to use an array to reference each variable name instead of having 20+ individual `If` statements. – ilarson007 May 13 '16 at 13:31
  • @PeterT Hmm, I have never heard of `Const` or `Disctionary`, so I will have to look into that. This may be the easiest way to go though. – ilarson007 May 13 '16 at 13:32
  • Ohhh I think I see what you mean. However I don't see anywhere where you assign `iFPYcol` a value. I'm assuming that it is set somewhere to be like `iFPYCol = [some number]`? – BruceWayne May 13 '16 at 13:50
  • In other words, somewhere in your macro, `iFPYCol` is set to some numerical value (`iFPYCol = 10` let's say). Then, when the `For` statement does `arrVariables(1)`, this will return `iFPYCol` as text. You want it to actually return the *value* of `iFPYCol`, being `10`. Correct? – BruceWayne May 13 '16 at 14:10
  • Hm, unfortunately I don't think you can do this in the way you want (with an array). It looks like you'll want to use a [dictionary](http://stackoverflow.com/questions/23226630/vba-excel-loop-through-variables). [This page too](http://stackoverflow.com/questions/11622648/using-a-for-loop-to-call-consecutive-variable-names-ie-car1-car2-car10-in) describes a little on how you can't quite do what you're trying to with an array. It looks like this is called "variable variable" in PHP, so try searching for like "variable variable VBA". – BruceWayne May 13 '16 at 14:23
  • @BruceWayne yes, to both questions. The code that I edited out sets a value for every name in my array, based on column headers. I have changed it to an array with each of those column numbers now, as @PeterT suggested. Just to get my code going, I have commented out a crossreference at the top and just use the array in my `If` statement now. I am interested in reading about the dictionaries that you mention. – ilarson007 May 13 '16 at 17:41

0 Answers0