How to pass values

[Pookiemeister]

In my workbook I have seven sheets. Sheets four through seven contain values of product information for our four production lines.
The below code starts on sheet 4 and loops through each row of column B until it finds the value of the combobox located on the userform named Chattem or Chattemfrm.cmbPrdCde.Value. It then will select that cell containing that value and offset to obtain the new cells value and assign that value to the corresponding variable of either txtDz, txtCs, and txtUOM. These variables are needed for a formula located in on the Chattemfrm. Once the user clicks on the command button labeled Print or cmdPrint the formula is calculated. The txtbxdz.value comes from the Chattemfrm Userform.
An excerpt of the formula is below.

textValUp = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16

The problem that I am having is how do I pass the value of those integers inside my sub to be used inside the above formula located on Chattemfrm. I thought about possibly declaring those variables as Public but wasn't sure how to do that in this situation or maybe there might be a better way.

Thank you for any help offered.

Option Explicit

Sub Test()

Dim ws_count, i, FinalRow, x, txtDz, txtCs As Integer
Dim txtUOM As String


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                Cells(x, 2).Select
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
            End If
        Next x
    Next i
End Sub

 

[JonXL]

I have attempted to find a way to do this myself but could never find an alternative to using Public variables. But I am by no means well-experienced in VBA.

As to how to use Public variables (if you are stuck going that route as I was), you just declare them public at the top of your Module...

Public intMyInteger As Integer

You can then write to and read from them anywhere in your project without having to bother passing stuff back and forth.

 

[Pookiemeister]

I tried to declaring the variables as string and integer within the Sub but I get a Run-time error '11': Division by Zero. Which makes sense because apparently the values were not passed to the formula on another userform. Here's the updated code where I declared the variables as Public.

Option Explicit
Public txtDz, txtCs As Integer
Public txtUOM As String
Sub Test()


Dim ws_count, i, FinalRow, x, txtDz, txtCs As Integer
Dim txtUOM As String


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                Cells(x, 2).Select
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
            End If
        Next x
    Next i
End Sub

 

[JonXL]

You are using these as Public variables. There isn't anything 'passing' anywhere. If you are getting the divide by zero error, then it's because you are attempting to perform the calculation before the variables are given any value.

Also, this block:

Dim ws_count, i, FinalRow, x, txtDz, txtCs As Integer
Dim txtUOM As String

... needs to become this:

Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer

And this:

Public txtDz, txtCs As Integer

... needs to become this:

Public txtDz As Integer, txtCs As Integer

 

[Pookiemeister]

After making the corrections you suggested I'm still getting Run-time error '11': Division by Zero. It still not passing the values. Below is the updated change. The sub is located inside a module while the formula is located inside a userform command button, as stated above. Thank you for your quick response.

Option Explicit
Public txtDz As Integer, txtCs As Integer
Public txtUOM As String
Sub Test()


Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If Cells(x, 2) & " " & "(" & Cells(x, 3) & ")" = Chattemfrm.cmbPrdCde.Value Then
                Cells(x, 2).Select
                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value
                txtUOM = Cells(x, 2).Offset(0, 4).Value
                'Cells(x, 2).Interior.ColorIndex = 3
            End If
        Next x
    Next i
End Sub

 

[JonXL]

What is important is when the formula and sub (specifically that conditional) are firing relative to one another as your code runs.

If the formula evaluates before this code has run,

                txtDz = Cells(x, 2).Offset(0, 2).Value
                txtCs = Cells(x, 2).Offset(0, 3).Value

... then txtDz and txtCs will be 0 and that is the error you'll get. (I'm assuming you've already determined that your cells that provide values to those divisors aren't 0.)

 

[Pookiemeister]

The Sub procedure comes before the cmdPrint on Chattemfrm is clicked? At which point the formula will then evaluate(run). Yes, all the provided values needed for the formula are greater than zero. Is there any type of code needed on the Chattemfrm, that contains the formula, that will allow a passing of values (if you will)? Thank you for your help.

 

[JonXL]

I would recommend placing watches on those variables, then, and stepping through your code.

To add a watch, right-click your variable and select "Add Watch". To step through your code, click in the left grey column next to a line to create a breakpoint at the start of your cmdPrint procedure. After you click the button, the script will pause with the line you chose highlighted. Press F8 to move through line-by-line in your code keeping an eye on the watched variables to see how they change as the code progresses.

 

[Pookiemeister]

Ok will do. Thanks for the advice. I'll give it a go tomorrow.

 

[Pookiemeister]

Would it be better to pass the variables through an argument locally rather than globally which I am assuming is what Public does? I was told to always use byVal in an argument when passing locally and never, if possible, pass values globally. But I'm still kinda of new with VBA and don't quite understand how to pass values this way.
For example, I'll try to ask this question to where I hope it'll make sense.

Sub Main ()
Dim Count As Integer
Count=3
Call showName(Sally, Count)
End Sub

Sub showName(byVal name As string, howMany As Integer)
       Msgbox  Display this
End Sub

Why can't you use the same values your passing for both? So in Sub showName instead of name use Sally and instead of howMany use Count that way they both match. Or does it have to do with memory storage location?