PDA

View Full Version : [SOLVED] NOVICE QUESTION: Referencing Integer Arrays



liamwhan
05-22-2012, 07:14 PM
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
05-22-2012, 09:17 PM
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
05-23-2012, 01:28 AM
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
05-23-2012, 01:30 AM
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 :bug:

GTO
05-23-2012, 03:09 PM
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:hi:

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?: pray2:

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

liamwhan
05-23-2012, 04:08 PM
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!
:yes

Kenneth Hobs
05-23-2012, 04:33 PM
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
05-23-2012, 07:16 PM
Save yourself some issues and follow our advice for when to use ()'s or not.

Got a link or other reference?

Paul

Kenneth Hobs
05-23-2012, 09:35 PM
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