Compile Error: Argument not optional

[Glitch5618]

Using excel 2007.

I've been banging my head against the wall on this one for awhile now. I'm rather new to VBA so forgive me if this is a simple matter.

Basically I have a program that filters data, and based on the filters applied you click a button aptly named Get Data. This program has over 1600 lines of code so far so I have split up the code and placed it in standard modules based on its function, grouping similar code together.

In order to reference the code on the main user form from any given module I use code like this...

Sub RandomSub(f As UserForm)

So if I wanted to reference a combo box located on the user form I just use "f.combobox.value" within the module code, this has been working for me no problem.

What I'm trying to do now is when you click the Get Data button it shows another user form that contains a multi-column list box with the information. So the idea is on the main user form I run a sub that shows the "DataBox" user-form and then calls a subroutine contained within a module. That modules code searchs the data based on a selected filter from the main user-form and then loads that data to an array which I then assign to the list-box contained within the "DataBox" userform. I figured this would be a simple matter of just adding another letter to designate which userform it was referencing, such as...

Sub RandomSub(f As UserForm, D as DataBox)

However if I place this in any module and try to compile the code I get the error mentioned in the title. When I try to debug it just highlights the subroutine of the main user-form that is calling the module sub. What am I missing here?

 

[RoryA]

I suspect that you haven't altered the code that calls this routine to add the new argument.

 

[Glitch5618]

I'm not sure what you mean. Most of the code is contained within the modules. The userform code only calls the subs contained in the modules based on user selected conditions, so a bunch of IF statements essentially. I should mention that for some reason the only way it will let me call those routines is by

RandomSub Me

as opposed to Call whatever. I have no idea why this is.

Anyway all i'm trying to do is reference objects on another userform, but it throws an error as soon as I put the code d as databox and this doesn't make any sense to me. Since this doesn't occur if I do the same thing but for only one userform with no reference to objects or changes to the code.

 

[RoryA]

Your Sub now expects 2 arguments but that call is only providing one.

 

[Glitch5618]

Again I'm not sure I get what you mean...is there a way to do what I'm trying? I've attached the file and removed all sensitive information, if you try to compile the file you'll see what I'm talking about. https://app.box.com/s/ssglanlta0x8p3g4ffq8831fgsnxb4kk

The GetAgentQuestion sub is the first sub within the GetData module.

I'm sorry but it appears I need this broken down barney style

 

[Glitch5618]

anyone?

 

[jefffroe2]

hi, im some what new to the vba coding thing. so im trying to create a macro that automatically refreshes, copies the current time, pastes it into a sheet, sheet 2(column A) goes back to sheet 1 copies a number in a certain cell and pastes it in the column next to the time(column B). so far i cant get it to copy and paste it every hour. i tried loops and time stamps that i found on the internet, nothing has worked. i've even tried to wait as excel recorded everything for me and it didnt work. any suggestions?

 

[Glitch5618]

hi, im some what new to the vba coding thing. so im trying to create a macro that automatically refreshes, copies the current time, pastes it into a sheet, sheet 2(column A) goes back to sheet 1 copies a number in a certain cell and pastes it in the column next to the time(column B). so far i cant get it to copy and paste it every hour. i tried loops and time stamps that i found on the internet, nothing has worked. i've even tried to wait as excel recorded everything for me and it didnt work. any suggestions?

Well i'm rather new to, your best bet is to make a new thread and post this question. Its less likely to be viewed by someone who can answer buried within another thread, it's also against the forum rules.

 

[Glitch5618]

I ended up finally figuring this issue out. Too whom it may concern, this code seems to solve my issue.

Dim d as databox
Set d = databox

then It allows me to reference objects by d.object

of course this can be replaced by anything, even just using the form name databox.object appeared to work. To save space I just declared the form databox globally by putting this code at the top of my module.

public d as databox

and then within the sub set d = databox
Hope this helps someone with my same issue.

 

[RoryA]

Apologies I was busy with other things.

When you write this:

Sub RandomSub(f As UserForm, D as DataBox)

your sub now expects you to pass two things (arguments) to it when you call it. For example:

RandomSub Me, Databox

rather than just:

RandomSub Me