Creating vectors of numbers

D

David Stickland

Hi,

I have recently started to try to learn VBA for Excel. I have
experience in Matlab.

I am trying to create a simple column vector. I take a user input from
my spreadsheet and then create an evenly spaced vector which starts
10% below the input and ends 10% above the input.

If anyone knows Matlab, this could be done in one line, like this:
IL = (ILi-ILi*0.1):100:(ILi+ILi*0.1)

So far I have only managed to do this in a rather ugly loop (code
below). Is this really the easiest way to do this?

Thanks,

David

------------------------------
Sub Temp_correct()

' Define variables
Dim IL(), ILi As Integer

'Get initial values from user input
ILi = Range("C5")

'Set up boundaries +/-10% for trial solutions
i = 1 'Counter
IL(i) = ILi - ILi * 0.1
Do While IL(UBound(IL)) < (ILi + ILi * 0.1)
IL(i + 1) = IL(i) + 100
i = i + 1
Loop

End Sub
 
T

Tom Ogilvy

Yes, you would need to use a loop.

The code you show does not dimension your array, so it should create an
error.
 
K

keepITcool

You could try like this:

i,ve added margins on ONE sides..
not too sure how matlab does this :)
adapt to suit your needs..
it's about 50% faster then the loop,
BUT you're limited to 65636 elements.

Sub Test()
Dim x
'Start at 120, 7500 elements +10%
x = MakeArray(123, 7500, 10)
Stop
End Sub

Function MakeArray(initial, elements, margin%)
Dim n&
On Error GoTo oops
n = elements * (1 + margin / 100)
MakeArray = Evaluate("=ROW(1:" & n & ")+" & initial)
Exit Function
oops:
MakeArray = CVErr(xlErrValue)
End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
T

Tom Ogilvy

This does +10% of the number of elements specified (of a non-existent array
???) and spacing is fixed at 1. Doesn't seem to match the problem
statement.
 
T

Tom Ogilvy

From what I can see,
in matlab

start_Value is first value stored
end_value is last value stored
increment - spacing between values
x = start_value:increment:end_value

number of elements is derived.

so
0:10:100

would be 0, 10, 20, . . . , 90, 100
 
H

Harlan Grove

David Stickland said:
I am trying to create a simple column vector. I take a user input from
my spreadsheet and then create an evenly spaced vector which starts
10% below the input and ends 10% above the input.

If anyone knows Matlab, this could be done in one line, like this:
IL = (ILi-ILi*0.1):100:(ILi+ILi*0.1)
Click to expand...
....

From web help for MatLab,

j:i:k is the same as [j,j+i,j+2i, ...,k]

So the easy way to do this in VBA for Excel would be

Dim v As Variant
v = Evaluate(0.9 * 3800 & "+(ROW(1:" & Int(0.5 + 0.2 * 3800 / 100) & _
")-1)*" & 100)

assuming there'd be 65536 or fewer elements in your array.
 
K

keepITcool

Harlan (.. and the others..)

I've seen some of your posts, and it appears as though you like to use
excel's worksheetfunctions whereever possible and efficient..

My first / gut reaction to the problem was similar to yours, however I
guessed wrong as to what the MAtlab function was supposed to do. Thomas
Ogilvy enlightened me :)

IN this case however using the Evalaute(ROW()) construct is noticably
slower than a simple vba loop:

Note: to get equivalent arrays I needed to do a transpose on the array,
PLUS convert the decimal if increment is not an integer and decimal is not
us english... I've included a simpler alternative as well.

See test.. Below takes 7secs for FullEval the , 3.5 secs For SimpleEval
but <.5 sec for the loop.. differences get a little smaller when the
arrays are 50000 elems..but always the loop wins hands down.

My conclusion: I'll stick to the loop.



Option Explicit

Sub Test()
Dim x(3), t!(3), i
t(0) = Timer
For i = 1 To 100000
x(0) = EvalArray(0.9 * 3800, 1.1 * 3800, 100)
Next
t(0) = Timer - t(0)

t(1) = Timer
For i = 1 To 100000
x(1) = SimpArray(0.9 * 3800, 1.1 * 3800, 100)
Next
t(1) = Timer - t(1)

t(2) = Timer
For i = 1 To 100000
x(2) = LoopArray(0.9 * 3800, 1.1 * 3800, 100)
Next
t(2) = Timer - t(2)


t(3) = Timer
For i = 1 To 100000
x(3) = EvalGrove
Next
t(3) = Timer - t(3)

Stop
End Sub

Function EvalArray(startvalue, endValue, increment)
Dim n, s$

On Error GoTo oops
n = CLng(0.5 + (endValue - startvalue) / increment)
With Application
If .DecimalSeparator <> "." Then
s = .Substitute(increment, .DecimalSeparator, ".")
Else
s = increment
End If

s = "=" & startvalue & "+(ROW(1:" & n & ")-1)*" & s

EvalArray = .Transpose(Evaluate(s))
End With
Exit Function
oops:
EvalArray = CVErr(xlErrValue)
End Function
Function SimpArray(startvalue, endValue, increment)
Dim n
On Error GoTo oops
n = CLng(0.5 + (endValue - startvalue) / increment)
SimpArray = Evaluate("=" & startvalue & "+(ROW(1:" & n & _
")-1)*" & increment)
Exit Function
oops:
SimpArray = CVErr(xlErrValue)
End Function

Function LoopArray(startvalue, endValue, increment)
Dim n, i&, r()
On Error GoTo oops:
ReDim r(1 To CLng(0.5 + (endValue - startvalue) / increment))
For n = startvalue To endValue Step increment
i = i + 1
r(i) = CSng(n)
Next
LoopArray = r
Exit Function
oops:
LoopArray = CVErr(xlErrValue)
End Function

Function EvalGrove()
EvalGrove = Evaluate(0.9 * 3800 & "+(ROW(1:" & _
Int(0.5 + 0.2 * 3800 / 100) & ")-1)*" & 100)
End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Harlan Grove said:
David Stickland said:
I am trying to create a simple column vector. I take a user input from
my spreadsheet and then create an evenly spaced vector which starts
10% below the input and ends 10% above the input.

If anyone knows Matlab, this could be done in one line, like this:
IL = (ILi-ILi*0.1):100:(ILi+ILi*0.1)
Click to expand...
...

From web help for MatLab,

j:i:k is the same as [j,j+i,j+2i, ...,k]

So the easy way to do this in VBA for Excel would be

Dim v As Variant
v = Evaluate(0.9 * 3800 & "+(ROW(1:" & Int(0.5 + 0.2 * 3800 / 100) & _
")-1)*" & 100)

assuming there'd be 65536 or fewer elements in your array.
Click to expand...
 
H

Harlan Grove

keepITcool said:
I've seen some of your posts, and it appears as though you like to use
excel's worksheetfunctions whereever possible and efficient.. ....
IN this case however using the Evalaute(ROW()) construct is noticably
slower than a simple vba loop:
Click to expand...
....

I said easy, not fast. If you want fast, this is a case in which VBA looks
are faster. However, if fast is the goal, there are faster approaches than
your LoopArray function.

First, only use error checking when necessary. In this case, it's not. You
can (and should) check your arguments before allocating your return array
and looping through it. Error checking slows things down.

Second, use integers (best, Longs) for For loop control variables, and it's
almost always best to loop on the array index, not the values that would be
stored in the array.

So here are functions to return horizontal and vertical arrays.


Function ha(strt As Double, inc As Double, stp As Double) As Variant
Dim i As Long, n As Long, rv() As Double

If strt = stp Then
ReDim rv(1 To 1)
rv(1) = strt
ha = rv
Exit Function
ElseIf (stp - strt) * inc <= 0 Then
ha = CVErr(xlErrNull)
Exit Function
End If

n = Int(1 + (stp - strt) / inc)
ReDim rv(1 To n)

rv(1) = strt
For i = 2 To n
rv(i) = rv(i - 1) + inc
Next i

ha = rv
End Function


Function va(strt As Double, inc As Double, stp As Double) As Variant
Dim i As Long, n As Long, rv() As Double

If strt = stp Then
ReDim rv(1 To 1, 1 To 1)
rv(1, 1) = strt
va = rv
Exit Function
ElseIf (stp - strt) * inc <= 0 Then
va = CVErr(xlErrNull)
Exit Function
End If

n = Int(1 + (stp - strt) / inc)
ReDim rv(1 To n, 1 To 1)

rv(1, 1) = strt
For i = 2 To n
rv(i, 1) = rv(i - 1, 1) + inc
Next i

va = rv
End Function
 
K

keepITcool

Harlan Grove said:
I said easy, not fast. If you want fast, this is a case in which VBA
looks are faster. However, if fast is the goal, there are faster
approaches than your LoopArray function.

First, only use error checking when necessary. In this case, it's not.
You can (and should) check your arguments before allocating your
return array and looping through it. Error checking slows things down.

Second, use integers (best, Longs) for For loop control variables, and
it's almost always best to loop on the array index, not the values
that would be stored in the array.

So here are functions to return horizontal and vertical arrays.
Click to expand...

not unexpected ...
... i just thought you might come back with some 'pure' code.
<VBG>

you're correct.. my code wasn't optimed for speed either.
my point was merely to demonstrate the relative slowness of the
the evaluate/row construct

re the errorhandler..
the way I used the errohandler to jump out of illegal input, may not be
gracefull but does NOT slow it down, at least i tried to test this but
could find no difference (even with large arrays or very high number of
calls to the function.

which I'd like to explain like this:
when I dont define an errorhandler the default errorhandler is
implicitly active e.g on error goto 0..

as long as my code doesnt jump back and forth between labels due to
trapped but resumed or handled code there should be no difference.


my bonus.. your 'pure' code


thanks!



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Top