User Defined Function: multiply array by a constant

I am obvioulsy a beginner, but using excel VBA, how do i multiply an array by a constant?

I have tried many ways... why doesnt this work? I am getting it to return the third element in the array multiplied by 2 just to see if it works...user just selects an array


Function multiply(a)
For i = 1 To 4
a(i) = 2 * a(i)
Next i
multiply = a(3)
End Function

I would prefer not to do it element by element... any advice?

Thanks

Hi, and welcome to the forum.

Can you explain what you mean by an array. Are you talking about a VBA array or perhaps an array of cells on the sheet?

Perhaps if you explain what you're trying to achieve in words, and attach a sample workbook, there may be other ways of doing this.

Rgds

I am trying to create a function that does something very simple. Whenever you do the "sum" function, you just type =sum( and then highlight an array of numbers) and then the function sums it.

I am looking to do the same thing where i can create a function and then type =multiply( highlight cells), and the function goes through and multiplies each element in the array by a constant. I have it returning the third element just to see if i am able to create this new array, but this has not been working at all. The problem is actually much more complicated, but if someone could tell me how to do this step, then it would really help.

Function multiply(a)
For i = 1 To 4
a(i) = 2 * a(i)
Next i
multiply = a(3)
End Function


Thanks- any ideas?

Use an array function.

For example:
Enter 1 to 9 in cells A1:A9
Select B1:B9, enter this formula, =A1:A9*C1, confirm array formula with CTRL-SHIFT-ENTER. Should appear as {=A1:A9*C1} in the formula bar.
Enter your constant is C1.

See the attached

Hi,

The following is one solution. The function is
=(InputRange,constant)

The function will use the first column of an input range.

Please Login or Register  to view this content.

HTH