SpartanValor
asked on
Cohort Math: Translate an R function to VBA
Hi Everyone - thanks in advance for taking a look and helping.
I'm trying to build a UDF in VBA that handles cohort math for digital media biz.
It takes as input several 1d arrays, builds a 3d array, then sums along two dimensions of that built array to output a 1d array.
Here's what I wrote in R, which I need to translate into VBA.
Also attached is a sample workbook where I've tried to start this effort so you can see how I would use the function in Excel.
I don't know VBA so would appreciate help translating the function I wrote in R into Excel VBA.
Thank you in advance I really appreciate the help.
Cohort_Math.xlsm
I'm trying to build a UDF in VBA that handles cohort math for digital media biz.
It takes as input several 1d arrays, builds a 3d array, then sums along two dimensions of that built array to output a 1d array.
Here's what I wrote in R, which I need to translate into VBA.
maus <- function(
nreach,
conversion,
dispersion,
dl,
seasonal,
sl,
retention
) {
n <- length(nreach)
mau.array <- array(rep(0, n * n * n), dim = c(n, n, n))
for (i in seq(n)) {
for (j in seq(n)) {
for (k in seq(n)) {
if (!(i <= (k - j + 1))) {
next
}
mau.array[i, j, k] <-
nreach[i] *
conversion[i] *
(
(seasonal[((i + j) %% ds) + 1] * dispersion[j]) /
sum(
c(seasonal[(((max(i, j) - 1) %% ds) + 1) : ds], rep(seasonal, floor(dl / ds)))[1 : dl] *
dispersion[1 : dl]
)
) *
retention[k - i - j + 2]
}
}
}
maus <- colSums(colSums(mau.array))
return(maus)
}Also attached is a sample workbook where I've tried to start this effort so you can see how I would use the function in Excel.
I don't know VBA so would appreciate help translating the function I wrote in R into Excel VBA.
Thank you in advance I really appreciate the help.
Cohort_Math.xlsm
First of all: Use Option Explicit in all of your modules to avoid using the wrong (typed) variables. Check Require Variable Declaration in the VBA IDE under Tools/Options. Then it is created automatically for new modules. Add it manually in already existing ones.
Then: Variables must be declared explicitly. Otherwise they are of type Variant. E.g.
And also important: Integer in VBA can only hold numbers from -32,768 to 32767. Thus use always Long (-2,147,483,648 to 2,147,483,647).
Further more: do you want "=maus(C2:H2;C3:H3;C4:H4;C
And why using explicit length parameters for dispersion and seasonal?
Then: Variables must be declared explicitly. Otherwise they are of type Variant. E.g.
Dim n, i, j, k As Integer
' is equivalent to
Dim n As Variant
Dim i As Variant
Dim j As Variant
Dim k As IntegerAnd also important: Integer in VBA can only hold numbers from -32,768 to 32767. Thus use always Long (-2,147,483,648 to 2,147,483,647).
Further more: do you want "=maus(C2:H2;C3:H3;C4:H4;C
And why using explicit length parameters for dispersion and seasonal?
ASKER
@gowflow - no it's the function I'm trying to write in VBA. I gave a version of it written in R.
ASKER
Thanks @ste5an.
I want the output to be the resulting array - I assume I would accomplish that by entering in cell as array formula.
I don't know what explicit vs. other parameters in VBA means. I'm open to all suggestions.
Hopefully it's clear, I just want the fuction maus() I wrote in R to be written in VBA - plmk if that doesn't make sense.
I want the output to be the resulting array - I assume I would accomplish that by entering in cell as array formula.
I don't know what explicit vs. other parameters in VBA means. I'm open to all suggestions.
Hopefully it's clear, I just want the fuction maus() I wrote in R to be written in VBA - plmk if that doesn't make sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Really appreciate all the help, Ste5an. Here's what mau.array looks like before colSums()
, , k is a 2D projection of the array (i, j, k) where k is fixed.
colSums() sums the columns of a 2D array, so colSums(mau.array) would look like this:
So colSums(colSums(mau.array)
, , k is a 2D projection of the array (i, j, k) where k is fixed.
, , 1
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 65.57377 0 0 0 0 0
[2,] 0.00000 0 0 0 0 0
[3,] 0.00000 0 0 0 0 0
[4,] 0.00000 0 0 0 0 0
[5,] 0.00000 0 0 0 0 0
[6,] 0.00000 0 0 0 0 0
, , 2
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 32.78689 98.52217 0 0 0 0
[2,] 157.63547 0.00000 0 0 0 0
[3,] 0.00000 0.00000 0 0 0 0
[4,] 0.00000 0.00000 0 0 0 0
[5,] 0.00000 0.00000 0 0 0 0
[6,] 0.00000 0.00000 0 0 0 0
, , 3
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 22.95082 49.26108 42.1875 0 0 0
[2,] 78.81773 73.89163 0.0000 0 0 0
[3,] 93.75000 0.00000 0.0000 0 0 0
[4,] 0.00000 0.00000 0.0000 0 0 0
[5,] 0.00000 0.00000 0.0000 0 0 0
[6,] 0.00000 0.00000 0.0000 0 0 0
, , 4
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 19.50820 34.48276 21.09375 8.080808 0 0
[2,] 55.17241 36.94581 14.06250 0.000000 0 0
[3,] 46.87500 19.53125 0.00000 0.000000 0 0
[4,] 26.93603 0.00000 0.00000 0.000000 0 0
[5,] 0.00000 0.00000 0.00000 0.000000 0 0
[6,] 0.00000 0.00000 0.00000 0.000000 0 0
, , 5
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 17.55738 29.310345 14.76563 4.040404 8.196721 0
[2,] 46.89655 25.862069 7.03125 16.161616 0.000000 0
[3,] 32.81250 9.765625 28.12500 0.000000 0.000000 0
[4,] 13.46801 33.670034 0.00000 0.000000 0.000000 0
[5,] 65.57377 0.000000 0.00000 0.000000 0.000000 0
[6,] 0.00000 0.000000 0.00000 0.000000 0.000000 0
, , 6
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 16.679508 26.379310 12.550781 2.828283 4.098361 0
[2,] 42.206897 21.982759 4.921875 8.080808 16.393443 0
[3,] 27.890625 6.835938 14.062500 32.323232 0.000000 0
[4,] 9.427609 16.835017 48.484848 0.000000 0.000000 0
[5,] 32.786885 81.967213 0.000000 0.000000 0.000000 0
[6,] 157.635468 0.000000 0.000000 0.000000 0.000000 0colSums() sums the columns of a 2D array, so colSums(mau.array) would look like this:
[,1] [,2] [,3] [,4] [,5] [,6]
[1,] 12690.96 6367.06451 17143.587 10150.179597 20548.013 13384.0631
[2,] 0.00 13.49054 13497.288 6757.925104 18220.213 10773.0282
[3,] 0.00 0.00000 5710.932 2861.176769 7712.613 4566.1800
[4,] 0.00 0.00000 0.000 6.475461 6478.698 3246.4722
[5,] 0.00 0.00000 0.000 0.000000 1586.370 794.7713
[6,] 0.00 0.00000 0.000 0.000000 0.000 0.0000So colSums(colSums(mau.array)
[1] 12690.959 6380.555 36351.808 19775.757 54545.907 32764.515ASKER
Also,
is modulo arithmetic in R. So, in comparison to mod() in excel,
=mod(x, y) is equivalent to
And the colons,
is the subset of some_array from index x to index y.
creates an a 1d array of x's with length n.
%%is modulo arithmetic in R. So, in comparison to mod() in excel,
=mod(x, y) is equivalent to
x %% yAnd the colons,
some_array[x : y]is the subset of some_array from index x to index y.
rep(x, n)creates an a 1d array of x's with length n.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: ste5an (https:#a42193097)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: ste5an (https:#a42193097)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
gowflow