workday function in vba

[jerl100]

I have the workday function working perfectly in code, along with the eomonth function. When they have to be called a large number of times, it takes quite a while for excel to do it.

I have noticed that whenever the code calls the function, it makes an entry on the immediate window. This is currently the only thing that I can think of that might be causing it to slow my code down. Is there a command I can use to prevent it from making the entry in the immediate window? Or alternatively, can anyone think of another reason why it may be taking so long to run?

Thanks

James

 

[Norie]

James

Can we see your code?

The only way anything would go in the Immediate window would be if you had code to put it there.

You would normally only have that when you were developing the code.

 

[jerl100]

This is a stripped down bit of the code where it uses the function:

Sub paydate()

Dim j As Integer
Dim date_x As Date
Dim date_pay_date() As Double

ReDim date_pay_date(1 To 10, 1 / 1 / 6 To 31 / 12 / 6)

For j = 1 To 10
For date_x = 1 / 1 / 6 To 31 / 12 / 6
date_pay_date(j, date_x) = workday(eomonth(date_x, 0), 10)
Next date_x
Next j

End Sub

Whenever it gets to the "workday(eomonth(date_x, 0), 10)" bit of it, the immediate window has

[GetMacroRegId] 'EOMONTH' <
[GetMacroRegId] 'EOMONTH' -> '-363659210' >
[GetMacroRegId] 'WORKDAY' <
[GetMacroRegId] 'WORKDAY' -> '2142634039' >

appear in it. So if the code is looped through many times, the immediate wondow is sat there continually chuging away.

Any thoughts you have about how I can get it to stop doing this are much appreciated.

Many thanks for your time.

James

 

[Norie]

James

Is that all the code you have?

 

[jerl100]

Nope. There's a ton more. That's just the relevant piece of code, which I adjusted so that it no longer required globals defined elsewhere etc.

If you stick that in a module by itself and tab through it, you'll see what I mean about it updating the immediate window every time the functions are called. Whether there's something I can switch on or off to prevent it from doing that, I don't know.

Thanks for your help.

James

 

[Norie]

James

That code won't even compile for me.

Perhaps the functions are writing to the Immediate window?

 

[jerl100]

It needs the atpvbaen.xls reference ticked before it'll compile (I think it is referencing a function from the analysis toolpack).

Cheers

James

 

[Norie]

James

I know.

Even then, when I run it nothing is sent to the Immediate window.

By the way what's the code meant to do? All it seems to do is create a 10x0 array with every element being 45.

 

[jerl100]

Seems not to like stepping through the dates there (the date_x = ) bit.

Does that fine in my code (but it wasn't obviously thought through well enough when I ran it above!). It should loop through j commodities, and for each one, for each date between 01/01/06 and 31/12/06 work out the date that the delivery on that day is paid for (the 10th business day of the month following delivery). So, whilst it is not working, it should have a 365 * 10 array produced.

The code on my machine is running fine. However, when I run stick the code above in a new workbook and run it by itself, it presumably runs through exactly the same as yours does, but the immediate window gets

[GetMacroRegId] 'EOMONTH' <
[GetMacroRegId] 'EOMONTH' -> '-363659210' >
[GetMacroRegId] 'WORKDAY' <
[GetMacroRegId] 'WORKDAY' -> '2142634039' >

produced in it 10 times. If that doesn't appear in yours (I'm running Excel 2003 & VBA 6.3), I can probably assume that it's something those wonderful people who decide exactly how every machine should be built have turned on because they though they were being 'helpful'! I have run it on several machines here, too, just to check it's not just mine mucking about.