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.
arrVariables(1)is "iFPYCol. andiColis a number. Do you just want to check ifiCounter >= iCol? I understand thatarrVariables(i) (wherei` is any value), won't ever return a number...so just check the counter? I may be misunderstanding...Longs to store all your values. You could set up a (large) set ofConstas array indexes to access a specific value in that array, or even aDictionaryof indexes and strings. Then you can loop through all the indexes of the array to make a quicker comparison.iFPYColto the value of iCol. They are both typeLong. I tried to use an array to reference each variable name instead of having 20+ individualIfstatements.ConstorDisctionary, so I will have to look into that. This may be the easiest way to go though.iFPYcola value. I'm assuming that it is set somewhere to be likeiFPYCol = [some number]?