"Offset" in VBA arrays

[Misca]

How can I get something like the OFFSET -function to work in a VBA array?

I'm trying to come up with a UDF that counts the days between two weekdays.

My function gets three arguments, starting day as a string and ending day as a string and the third one is minimum number of days between those two.

The array goes from "MON" all the way to the "SUN" three weeks away.

What I'm trying to do is get the UDF to count the days from say "FRI" to "MON" if the minimum days between those two was 4 (= FRI to MON would be 3 but since the minimum days needs to be 4 the function should return 3 + 7 = 10 days).

Solving this on a worksheet would look something like this:

Excel Workbook
	BM	BN	BO	BP	BQ	BR	BS
2	MON	TUE	WED	THU	FRI	SAT	SUN
3	*	*	*	*	*	*	*
4	*	From	To	Min days	Answer	*	*
5	*	FRI	MON	4	10	*	*
Sheet

How could I solve this in VBA (hopefully without SelectCase arrays).

 

[Jerry Sullivan]

Hi Misca,

What are the dimensions of your Array?
Is it 1x21 or 3x7?

To do the equivalent of Offset in a One-Dimensional Array you just need to add the Index numbers.

If MyArray(idxFrom)="FRI" then
MyArray(idxFrom+10) would evaluate to "MON"

You might simplify your UDF by taking advantage of some of the built-in date functions, but I'm assuming there's a reason you are taking this approach.

 

[msaldut]

Hi Misca,

What are the dimensions of your Array?
Is it 1x21 or 3x7?

To do the equivalent of Offset in a One-Dimensional Array you just need to add the Index numbers.

If MyArray(idxFrom)="FRI" then
MyArray(idxFrom+10) would evaluate to "MON"

You might simplify your UDF by taking advantage of some of the built-in date functions, but I'm assuming there's a reason you are taking this approach.

Is there a way to evaluate all of the values in that offset range(1:10), as opposed to just the 10th?

...if it were an OFFSET formula it, would be the "height" element instead of "rows".