EXCEL ARRAYS

[punteray]

I have imported "Toteboard" odds from Brisnet to an excel sheet (I am just learning Excel). I changed the Tote fractions to a decimal format. The decimal format is in an ARRAY. I am trying to code a program using VBA but I am having trouble using the info from the sheet array since VBA doesn't want to recognize the sheet array.:mad:

Any inciteful info to get the tote odds to the VBA format would be gratefully appreciated.:)

Thanks

Ray Scalise

[raybo]

I have imported "Toteboard" odds from Brisnet to an excel sheet (I am just learning Excel). I changed the Tote fractions to a decimal format. The decimal format is in an ARRAY. I am trying to code a program using VBA but I am having trouble using the info from the sheet array since VBA doesn't want to recognize the sheet array.:mad:

Any inciteful info to get the tote odds to the VBA format would be gratefully appreciated.:)

Thanks

Ray Scalise

I assume you mean the formulas, for the conversions, were arrays. Did you make sure you wrote them correctly, as arrays? Check the help file in Excel and make sure your formula syntax is correct for array formulas.

[punteray]

The syntax is correct and I do have the arrays in cells on Sheet 1. read in a book on excel that the arrays made in the sheet are different than the arrays made in VBA. This appears true since I have not been able to use the sheet array in VBA. I get a message saying the file is not compatible.

Ray Scalise

[punteray]

As my wife always tells me "if all else fails READ THE DIRECTIONS"!!:mad:

So I am reading the book section on ARRAYS and I see where I screwed up (so far). Setting them up is different than VB6 (surprise).:D

Ray Scalise

[DJofSD]

In computer parlence, it's often referred to as RTFM.

[raybo]

In computer parlence, it's often referred to as RTFM.

Yeah, it's always a good idea to read up on things that are confusing or don't work like they should. I don't use array formulas but I have read about them and know that at some point in the formula you have to press "ctrl" and some other key to tell Excel that it is an array formula. Otherwise Excel just assumes it's a regular formula.

[hcap]

I recommend books by John Walkenbach.
Here is an example from one of his Power Programming books.

Sub WriteReadRange()
Dim MyArray()
Dim Time1 As Date

Range("A:A").ClearContents
NumElements = InputBox("How many element?")
If NumElements = "" Then Exit Sub
ReDim MyArray(1 To NumElements)

' Fill the array
For i = 1 To NumElements
MyArray(i) = i
Next i

' Write the array to a range
Application.ScreenUpdating = False
Time1 = Timer
For i = 1 To NumElements
Cells(i, 1) = i
Next i
WriteTime = Format(Timer - Time1, "00:00")

' Read the range into the array
Time1 = Timer
For i = 1 To NumElements
MyArray(i) = Cells(i, 1)
Next i
ReadTime = Format(Timer - Time1, "00:00")
Application.ScreenUpdating = True

' Show results
Msg = "Write: " & WriteTime
Msg = Msg & vbCrLf
Msg = Msg & "Read: " & ReadTime
MsgBox Msg, vbOKOnly, NumElements & " Elements"
End Sub


Or go here for now.

http://www.cpearson.com/excel/VBAArrays.htm
http://msdn.microsoft.com/en-us/library/aa164778%28office.10%29.aspx

BTW, manipulating worksheet data is quite fast without loading or unloading the data into or from vba arays.
It is easier to use vba to manipulate the data in worksheet cells directly.
At least for me.

[punteray]

Walkenbach is the book I have. Thanks for the code!!

Ray