How do I pass a variable and an array to another sub?

[ianpwilliams]

Hi all, quick question with hopefully a simple answer.

I'm trying to pass a variable and an array from one sub to another. I've got the variable passed over ok, but I can't find out how to pass the array as well:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

How do I pass the "backlog" array across as well as the "day" variable?

[MarvinP]

Hi ian,

Maybe you should use GLOBAL variables which are available to all procedures instead. See http://support.microsoft.com/kb/170721

I hope this helps...

[HaHoBe]

Hi, ianpwilliams,

pass them as a parameter of Type Variant like in http://social.msdn.microsoft.com/For...-3d3ce42f1121/

Ciao,
Holger

[ianpwilliams]

It's ok, I tried using public variables and arrays, and it works a treat, thanks! I do however have a follow-up problem...

I have a piece of code that opens a second workbook, and updates certain cells with certain values (using the Public array "backlog":

Please Login or Register  to view this content.

The problem is, after the figure has been updated in the second workbook, the subroutine is supposed to save and close the second workbook, and then return to the original workbook, but all it does at the moment is open the second workbook, update the figure and leave it like that. Which would lead me to believe that after successfully meeting one of the IF statement conditions, it just exits the subroutine completely. I did have it saving and closing the second workbook earlier though, when the code was quite different.

[HaHoBe]

Hi, ianpwilliams,

why set a variable and then use ActiveWorkbook? Could it be you missed the reference to the sheet in the given sample?

Please Login or Register  to view this content.

This should return to the worksheet which was active prior to open the workbook.

Ciao,
Holger

[ianpwilliams]

It still doesn't work for some reason. This is the full subroutine:

Please Login or Register  to view this content.

and the answer to your other question is because I'm still in the early stages of learning VBA, and I had modified some existing code, which I didn't entirely know what it was doing....

[HaHoBe]

Hi, Ian,

the code you posted will rely on the newly opened workbook (it´s the active workbook) but on the Activesheet of that workbook and not on Worksheet("Backlog Count") as no reference for the Range is set to that worksheet. That´s what the dots are for I placed in my code sample.

You dim wbk1 in your sample but neither set nor use it further on. If wbk1 is the workbook with the code you want to return to at the end you may use the follwoing lines

Please Login or Register  to view this content.

And maybe you should use two arrays for all the lines of code updating: one for the number of the backlog and the second for the number of the column to be updated.

Ciao,
Holger

[ianpwilliams]

I've tried using the code you mentioned:

Please Login or Register  to view this content.

If still fails to save and close the second workbook though, and also fails to return to the first workbook.

Also, the reason why wbk1 was dimmed but not used is because I took the code from online, where someone wanted to open two other spreadsheets. I only wanted to open one, so I only needed wbk2.

[ianpwilliams]

I don't even need to use variables when it comes to opening/closing/selecting the two worksheets. I just don't know what arguments like "With wbk2.sheets("Backlog Count") mean, and whether I could replace wbk2 with the name of the workbook.

[Norie]

Ian

In this code,

Please Login or Register  to view this content.

none of the ranges refer to the workbook/worksheet in the With statement.

Instead they all refer to the active sheet in the active workbook.

To fix that put a dot (qualifier) in front of each instance of Range.

[ianpwilliams]

There's no problem with the cells being updated in the second workbook that opens, that works fine. I did try putting dots in front of the Range commands, but it just did the same thing.

The problem I still have is, the second workbook opens, and the figures are updated. But that's it. The second workbook doesn't save, it doesn't close, and it doesn't return me to workbook 1.

I'm not sure I even need the "With wbk2.Sheets("Backlog Count") command either. All I'm really looking to do is what I've said above, and that although I don't know what that "With" statement does, it seems like it might be a bit pointless.

I've got a couple of books on VBA, but neither of them seem to give a clear idea of what options are available when it comes to listing arguments and combining arguments (is that what they are even called?) All this "With wbk2.sheets", "wbk2 = Workbooks.Open(strSecondfile)" etc is very confusing.

[Norie]

Ian

Have you stepped through the code with F8 to see what's happening?

Perhaps you could also put a breakpoint (F9) on the line of code that's supposed to close the 2nd workbook.

Either way you should be able to find out what's (not?) happening.

PS Those dots should be there.

[ianpwilliams]

I've been through the subroutine line by line. It does the IF statements one by one (updating a cell in workbook 2 during the first IF statement), then when it gets to the "End With", it returns to the subroutine that called it, which is below.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I tried changing the code to put the "save and close" command inside the "With" bit:

Please Login or Register  to view this content.

Put when I went through step by step again, it did the IF lines, then ignored the wbk2.Close command and returned to the other subroutine.

Out of interest, why should the dots be there? What are they for?

[ianpwilliams]

This is an old piece of code I had, which only updates one cell, but strangely it does save and close the second workbook and return to the first workbook:

Please Login or Register  to view this content.

I did try moving the ActiveWorkbook.Close command to my latest code, but it doesn't reach the command either way.

[ianpwilliams]

I've also altered the code to make "day" a global variable (as it is used in both subroutines), which means that "day" is no longer passed across. But that made no difference either.

[JosephP]

comment out the 'On Error Resume Next' in the calling routine and run the code

[ianpwilliams]

I've just tried that, but when I run it, it says "Subscript out of range" and highlights the next line

[JosephP]

replace

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

then test again-you shouldn't really leave an on error resume next as the handler for a long routine

[ianpwilliams]

I've tried that, but then it says "Subscript out of range" on the line:

If backlog(102) > 0 Then Range(daycolumn & 24) = Range(daycolumn & 24) + backlog(102)

[JosephP]

exactly-that's why the workbook is not saving. the called code has no error handling so when an error occurs processing jumps back to the calling routine-where your on error resume next was suppressing it previously

[Norie]

I think you need to check the dimensions of backlog.

[ianpwilliams]

Ok I think it's working now. I un-commented (is that the right word?) the "On Error Resume Next" statement in UpdateLegalAndMeans(), and I checked the global "Public backlog" statement. It turns out that backlog was set as 1 to 100, so it seems that when it got to the IF statement with 102, it was no doubt going wrong there.

So what does "On Error Resume Next" actually do? That was part of a piece of code that I got from online and modified, so I don't really know.

[JosephP]

it ignores any errors and simply tries to execute the next line of code in the procedure

[ianpwilliams]

Ah ok. I am intending to work my way through some VBA books, so I should gradually start to understand more of what's actually going on!

This was driving me mad yesterday, and it finally works, so thanks for the help everyone, it's much appreciated!