NOVICE QUESTION: Referencing Integer Arrays

[liamwhan]

Hi all,

This is an extreme novice question so please dont eat me.

I am trying to use an array to store columns integers, which are found based on column header text: i.e. Rows(1).Find(what:="Positions").Column

I have the following code:

Dim intColNum(2) As Integer, intArrayPos As Integer
intColNum(0) = Rows(1).Find(what:="Positions").Column
intColNum(1) = Rows(1).Find(what:="Pos Id").Column
intColNum(2) = Rows(1).Find(what:="Cost Centre").Column
For intArrayPos = LBound(intColNum) To UBound(intColNum)
MsgBox (Str(intColNum))
Next intArrayPos

Currently I am getting an "Object Variable with Block variable not set" error on Line 4 (intColNum(2) = Rows(1)... etc);

I've stepped through the code with the excel debugger but it doesnt tell me much.

Thanks for any help you can give to this frazzled learner.


Liam

[GTO]

Greetings Liam,

In short, "Cost Centre" is not being found. Thus. .Column creates an error, as Rows(1).Find(what:="Cost Centre") is not returning a Range Object.

Try checking to see if the .Find returns a Range Object before referencing the Object's properties. Does this seem sensible?

Option Explicit

Sub example()
Dim intColNum(0 To 2) As Integer, intArrayPos As Integer, n As Long
Dim rngFound As Range
'// Ignore errors and handle in-line for the moment //
    On Error Resume Next
    For n = 0 To 2
        '// Attempt to set a reference to the cell that .find locates.  //
        Set rngFound = Rows(1).Find(what:=Array("Positions", "Pos Id", "Cost Centre")(n), LookAt:=xlWhole)
        '// If the value is found...
        If Not rngFound Is Nothing Then
            intColNum(n) = rngFound(1).Column
        '// Else... (choose some value or 0 to note that the value was not found)
        Else
            intColNum(n) = 9999
        End If
    Next
    '// Turn error checking back on, so we aren't later mystified by unknown errors.    //
    On Error GoTo 0
For intArrayPos = LBound(intColNum) To UBound(intColNum)
        MsgBox intColNum(intArrayPos)
    Next intArrayPos
End Sub

[Bob Phillips]

You could wrap in an On Error

Dim intColNum(2) As Long, intArrayPos As Long
On Error Resume Next
    intColNum(0) = Rows(1).Find(what:="Positions").Column
    intColNum(1) = Rows(1).Find(what:="Pos Id").Column
    intColNum(2) = Rows(1).Find(what:="Cost Centre").Column
On Error GoTo 0
For intArrayPos = LBound(intColNum) To UBound(intColNum)
    MsgBox intColNum(intArrayPos)
Next intArrayPos

[Bob Phillips]

For intArrayPos = LBound(intColNum) To UBound(intColNum)
MsgBox intColNum(intArrayPos)
Next intArrayPosb[/VBA]

Glad to see you removed the parentheses on that MsgBox Mark. I was telling some guy on another forum this week why that is a bad practice, and he just thought I was saying it is a bad idea to use MsgBox

[GTO]

Glad to see you removed the parentheses on that MsgBox Mark. I was telling some guy on another forum this week why that is a bad practice, and he just thought I was saying it is a bad idea to use MsgBox

Hi Bob

I suspect your reasoning is more commonsensical than mine.

My reasoning has more to do with not confusing my poor self w/parenthesis when I am neither using the return, nor grouping stuff (to make it clearer and/or forcing the correct order of process).

Care to share?

Ahhh, the written word and the receiver's perception. (LOL on that last bit)

[liamwhan]

Thank you both sincerely for the responses; GTO In the end I took your advice and added a check to see if the column header was found -

Actually its helped me to better understand the error reporter; when I stepped through the code it did actually look like it got half way through the offending line before throwing an exception and that makes sense as it was .Column that caused it. So its a nice learning xp.

Just out of curiousity; and to help me learn, I'm interested to know why the parenthesis are bad practice in this case; you both sort of mention the reasons behind it implicity but I'm still too new to understand!

Thanks again for your help!

[Kenneth Hobs]

If you want to use ()'s to pass parameter values to a procedure, use Call. Otherwise, do not use ()'s. See the help for Call for these details.

Save yourself some issues and follow our advice for when to use ()'s or not.

[Paul_Hossler]

Save yourself some issues and follow our advice for when to use ()'s or not.

Got a link or other reference?

Paul

[Kenneth Hobs]

Paul, the VBE help should suffice but if not, look for Error, Expected "=", kind of posts to the web. I know as a new user a few years ago, it took me a while to know what was happening. Maybe an example would help?

You will see the problem if you move to the next line after typing the "hi)" in the call to the Test Sub routine. You can get the exact error message when you do the example. For a single parameter, you will not see the error from intellisense nor a compile nor run-time.

Sub test_test()
  test ("Hello World!", "hi")
End Sub

Sub test(s As String, ss As String)
  Debug.Print s, ss
End Sub