ByVal Reference Issue

[JPARKHURST]

I continue to have problems getting the hang of VBA's structure (my fault, I've not taken the time to run though some lessons properly, been running and grabbing just to get certain parts working).

The following gives me a byRef argument type mismatch. The last section is my call, and it is returning the error. For the life of me, I can't see what I have done. I have gone through and marked everything I can as public, in case it wasn't able to see a definition, I will turn that off by sections as able.

What am I missing? I declare the wsWorkingDispatch as a worksheet in the general, on workbook open I set it to the appropriate sheet, then I feed it to a sub which calls for a worksheet, so I'm missing something.

I can replace this with sheets(workingdispatch), but that's not ideal - I'd like to know what I am missing here if anybody can help. TIA!

===============General Declarations====================
Option Explicit
Public wbThis As Workbook
Public wsDashboard As Worksheet
Public wsWorkingDispatch As Worksheet
Public wsJobDispatch As Worksheet
Public wsShortages As Worksheet


===============Workbook====================
Public Sub Workbook_Open()
Set wbThis = ThisWorkbook
Set wsDashboard = Sheets("Dashboard")
Set wsWorkingDispatch = Sheets("Working_Dispatch")
Set wsJobDispatch = Sheets("Job_Dispatch")
Set wsShortages = Sheets("Shortages")
End Sub


===============Module1====================
Public Sub ClearEm(wsClear As Worksheet)
Dim lrow As Long
Dim clRange As Range
Dim cpRange As rnage

wsClear.Active

lrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
MsgBox (lrow)
Set clRange = wsClear.Range("A1:S" & lrow)
MsgBox (lrow + 1)
Set cpRange = wsClear.Range("A" & lrow + 1 & ":S" & lrow + 1)
End Sub


===============Button--> Sub Call====================
Public Sub btnClearRangeWD_Click()
Call ClearEm(wsWorkingDispatch)
End Sub

Thank you,

Jon

 

[Fluff]

Where are your "General Declarations"?

 

[hotabae]

Where are your "General Declarations"?

While we wait for that...

See these edits:

Public Sub ClearEm(wsClear As Worksheet)
Dim lrow As Long
Dim clRange As Range
Dim cpRange As [COLOR="#FF0000"]Range[/COLOR]

[COLOR="#FF0000"]wsClear.Activate[/COLOR]

 

[JPARKHURST]

Hi Fluff, thank you.

ThisWorkbook(Code) is where my general declarations are.

 

[JPARKHURST]

Ah, thank you. Corrections made.

While we wait for that...

See these edits:

Public Sub ClearEm(wsClear As Worksheet)
Dim lrow As Long
Dim clRange As Range
Dim cpRange As [COLOR=#FF0000]Range[/COLOR]

[COLOR=#FF0000]wsClear.Activate[/COLOR]

 

[Fluff]

Hi Fluff, thank you.

ThisWorkbook(Code) is where my general declarations are.

They need to be in a regular module.

 

[JPARKHURST]

Putting them in the Module1 (where ClearEm is located) gives me a run-time error 91, variable not set, points to wsClear.Activate.

When I declare my variables as public in the General stage above the ClearEm module, (or in it), are they not global, or are they?

 

[JPARKHURST]

I forgot to run my workbook open code, looks like it may be working.

Thank you much for the help!

 

[Fluff]

As long as they are at the very top of the module (ie before any code) like

Option Explicit
Public wbThis As Workbook
Public wsDashboard As Worksheet
Public wsWorkingDispatch As Worksheet
Public wsJobDispatch As Worksheet
Public wsShortages As Worksheet

Public Sub ClearEm(wsClear As Worksheet)
Dim lrow As Long
Dim clRange As Range
Dim cpRange As Range

wsClear.Activate

lrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
MsgBox (lrow)
Set clRange = wsClear.Range("A1:S" & lrow)
MsgBox (lrow + 1)
Set cpRange = wsClear.Range("A" & lrow + 1 & ":S" & lrow + 1)
End Sub

Then they will have global scope

 

[JPARKHURST]

**I should take a moment and address that the subject is incorrect - I was looking at ByReference, not byValue.

Thank you, Fluff. I mostly understand what I was doing, though I do not understand the whys of it not working.

My background has taught me to use small, renewable pieces of code, and it has either been compiled literally or web based and so run sequentially, and on demand. This environment is quite strange to work in (VBA/Excel/Office).

For the record if anybody is looking into why later:

My [main] problem is that I was attempting to use modules to control the structure of my document in procedure based bits. I have read one reason this doesn't work is that ALL variables are declared (guessing to 'save space' for them originally) upon opening of the document, not upon calling upon that code. However, not all variables are shared equally across all modules - only in the same module can you access that module's variables. Of course, the variables were of global scope, and so had already been defined....

I have now restructured it, and my 'Sheet Mechanics' or procedures which manipulate the sheet to prepare it for writing are all in one module. I still have some variables which I may wish to share, but putting everything together on one sheet, but I can follow that up later.

Thanks for the help!