How to pass values

P

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
Code: 
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.


Code: 
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
 
Sort by date Sort by votes
Passing as a value vs a reference affects how the function treats the variable you pass it. If you pass it as a value, it basically works off a copy of your variable. If you pass it as a reference, your original variable will be updated to reflect any of the changes made to it during the function's run.

If you want to pass things around by value without using Public variables, you can try passing them through a function.

Code: 
Public Function Test(TextBox as Double) as Double
Dim txtDz As Integer, txtCs As Integer
Dim txtUOM As String
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
    
        With Worksheets(i)
        
            FinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
            
            For x = 1 To FinalRow
            
                If .Cells(x, 2).Text & " " & "(" & .Cells(x, 3).Text & ")" = Chattemfrm.cmbPrdCde.Value Then
                    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
            
        End With
        
    Next i
    
Test = ((TextBox) / txtDz / txtCs)
    
End Function

Adjust your formula to look like this:

Code: 
textValUp = Test(txtbxdz.Value) + 0.5 - 1E-16

I'm very doubtful this will solve your error, but maybe some of the adjustments I made to your procedure will make a difference.

I'm also not sure this will produce the desired result even if it does get rid of the divide by zero error, since I'm not really sure what you're trying to accomplish.

Best of luck, though. :)

ABE: Also, what is your intention with txtUOM? Once the function finishes, it's done and I don't see you making use of it anywhere. Does it need to be a Public variable?
 
Last edited:
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I am displaying all the code that might be causing the problem. I don't see any issue but I'm still new at VBA.
This is the code that comes before the module.

Code: 
Private Sub cmdbtnDone_Click()    
    'Declare variables
    Dim r As Range, r1 As Range
            
    'Sets variables to a range
    Set r = Range(RefEdit1)
    Set r1 = r(1)
    
    wbName = ActiveWorkbook.Name
    
    Load Chattemfrm
    Chattemfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
    Chattemfrm.cmbPrdCde.Value = r1.Offset(0, -6).Value
    Chattemfrm.txtbxPrdctNm.Value = r1.Offset(0, -5).Value
    Chattemfrm.txtBxLtNum.Value = r1.Offset(0, -3).Value
    Chattemfrm.txtBxShopNumber.Value = r1.Offset(0, 1).Value
    Chattemfrm.txtbxVndrLtNu.Value = r1.Offset(0, -2).Value
    Chattemfrm.txtbxdz = Me.txtbxRangeTotal.Value
        
    Select Case Chattemfrm.cmbSDPFLine.Value
        Case Is = "SDPF - LINE 1 (SLAT).xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Slat"
        Case Is = "SDPF - LINE 2A.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Uhlmann"
        Case Is = "SDPF - LINE 3.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "Korber"
        Case Is = "SDPF - LINE 4.xlsx"
            Chattemfrm.cmbSDPFLine.Value = "IMA"
    End Select
    
    Unload Me
    Workbooks(wbName).Activate
    ActiveWorkbook.Close
[B][COLOR=#ff0000]    Call Test[/COLOR][/B]
    Chattemfrm.Show
    
End Sub
This is the module code again.
Code: 
Option Explicit
Public [B]txtDz[/B] As Integer, [B]txtCs[/B] As Integer
Public [B]txtUOM[/B] 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
            End If
        Next x
    Next i
End Sub
And lastly is the cmdbtnPrint_Click() I bold faced typed the Dim of the same declared variables as the Sub.
Code: 
Private Sub cmdbtnPrint_Click()
    
    Dim MyCount As Long
    Dim iDate As String
    Dim ShopNum As Integer
    Dim iDateNum As Integer
    Dim Rtn_num As Integer
    Dim rtn_ans As String
    Dim myWrkBk As Workbook
    Dim mySheet As Worksheet
    Dim textValUp As Long
    Dim textValDown As Long
    Dim numLenA As Long
    Dim numLenF As Long
    Dim startNum As Integer
    Dim [B]txtUOM[/B] As String
    Dim [B]txtCs[/B] As Long
    Dim [B]txtDz[/B] As Long
    Dim totRows As Long, i As Long
    
  'Converts month names to numerical value equivalent
   Select Case cmbMonth
        Case Is = "January"
            iDateNum = "1"
        Case Is = "February"
            iDateNum = "2"
        Case Is = "March"
            iDateNum = "3"
        Case Is = "April"
            iDateNum = "4"
        Case Is = "May"
            iDateNum = "5"
        Case Is = "June"
            iDateNum = "6"
        Case Is = "July"
            iDateNum = "7"
        Case Is = "August"
            iDateNum = "8"
        Case Is = "September"
            iDateNum = "9"
        Case Is = "October"
            iDateNum = "10"
        Case Is = "November"
            iDateNum = "11"
        Case Is = "December"
            iDateNum = "12"
    End Select
    
    'Places/unloads inputed data from textboxes into its corresponding cell
    'frmChattem.Hide
    
    iDate = iDateNum
    strE2 = "'" & cmbPrdCde.Text
    Worksheets("Placard").Activate
    Range("'Placard'!$E$2").Value = CStr(Left(strE2, 9))
    Range("'Placard'!$E$4").Value = txtBxLtNum.Text
    Range("'Placard'!$E$6").Value = txtBxShopNumber.Text
    Range("'Placard'!$E$8").Value = iDate & "/" & "      " & "/" & Year(Date) & "     " & CheBxX & "   " & CheBxY & "   " & CheBxZ
    Range("'Placard'!$E$11").Value = txtbxPrdctNm.Text
    Worksheets("Finished Goods Summary").Activate
    Range("'Finished Goods Summary'!$C$2:$E$2").Value = iDate & "/" & "      " & "/" & Year(Date)
    Range("'Finished Goods Summary'!$C$3:$E$3").Value = Range("'Placard'!$E$2").Value
    Range("'Finished Goods Summary'!$C$4:$E$4").Value = txtbxPrdctNm.Text
    Range("'Finished Goods Summary'!$C$5:$E$5").Value = txtBxLtNum.Text
    Range("'Finished Goods Summary'!$C$6:$E$6").Value = txtBxShopNumber.Text
    Worksheets("Solid Dose Usage Record").Activate
    Range("'Solid Dose Usage Record'!$C$3:$D$3").Value = txtbxStckNum.Text
    Range("'Solid Dose Usage Record'!$C$5:$D$5").Value = txtBxLtNum.Text
    Range("'Solid Dose Usage Record'!$C$7:$D$7").Value = txtbxVndrLtNu.Text
    Range("'Solid Dose Usage Record'!$G$3:$K$3").Value = txtbxPrdctNm.Text
    
    textValUp = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16
    textValDown = ((txtbxdz.Value) / txtDz / txtCs) - 0.5 + 1E-16
    startNum = 1


    Set myWrkBk = ActiveWorkbook
    Set mySheet = myWrkBk.Sheets("Finished Goods Summary")
    Do
        If textValUp >= 30 Then
            numLenA = 30
        Else
            numLenA = textValUp
        End If
        If textValDown >= 30 Then
            numLenF = 30
        Else
            numLenF = textValDown
        End If
        
        With mySheet
            .Range("A9:A38").ClearContents
            .Range("E9:E38").ClearContents
            .Range("F9:F38").ClearContents
            .Range("G9:I38").ClearContents
            .Range("J9:K38").ClearContents


            If numLenA > 0 Then
                .Cells(9, "A") = startNum
                .Range(.Cells(9, "A"), .Cells(numLenA + 8, "A")).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
                .Range(.Cells(9, "E"), .Cells(numLenA + 8, "E")).Value = txtUOM
                .Range(.Cells(9, "G"), .Cells(numLenA + 8, "G")).Value = txtDz
            End If
            If numLenF > 0 Then
                .Range(.Cells(9, "F"), .Cells(numLenF + 8, "F")).Value = txtCs
                .Range(.Cells(9, "J"), .Cells(numLenF + 8, "J")).Value = txtCs * txtDz
            End If
        End With
        textValUp = textValUp - 30
        textValDown = textValDown - 30
        startNum = startNum + 30
        
        'Put code here to print
        If textValUp > 0 Then
            Sheets("Finished Goods Summary").PrintOut
        Else
            Sheets("Finished Goods Summary").PrintOut
        End If
    Loop While textValUp > 0
        MyCount = ((txtbxdz.Value) / txtDz / txtCs) + 0.5 - 1E-16
        Call IncrementPrint(MyCount)
End Sub

Thank You.
 
Upvote 0
So I figured out where the problem occurs but now I'm not sure why? OK...as I am stepping through the code.
Code: 
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
                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
            Cells(x, 2).Select
        Next x
    Next i
End Sub
The below code never retrieves a value and I don't know why.
Code: 
txtDz = Cells(x, 2).Offset(0, 2).ValuetxtCs = Cells(x, 2).Offset(0, 3).Value
txtUOM = Cells(x, 2).Offset(0, 4).Value
Thank you.
 
Upvote 0
First, did you try the version of this loop I pasted earlier that doesn't involve activating and selecting sheets and cells?

Code: 
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
    
        With Worksheets(i)
        
            FinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
            
            For x = 1 To FinalRow
            
                If .Cells(x, 2).Text & " " & "(" & .Cells(x, 3).Text & ")" = Chattemfrm.cmbPrdCde.Value Then
                    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
            
        End With
        
    Next i
End Sub

Second, while you're stepping through the code, you can always figure out what your cell references are pointing to by typing them into the immediate window (View>Immediate Window; or Ctrl+G) preceded by a ?; for example:

Code: 
?Worksheets(i).Cells(x, 2).Offset(0, 2).Address

... will tell you the address of the cell in question. You might find that your code isn't referencing the cells you expect it to be referencing. You can also use it to check the value of your strings in your If statement to make sure that they are, indeed, identical and so properly causing the Then to fire.

Third, is there a reason you are using Offset to reference the cells you want the values from? I don't use Offset unless I'm going to include a variable somewhere in the Offset argument, but since your Offset is the same through every iteration of the loop, you can probably get rid of it and just directly reference the column you want along with the variable row (x):

Code: 
                    txtDz = .Cells(x, 4).Value
                    txtCs = .Cells(x, 5).Value
                    txtUOM = .Cells(x, 6).Value
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
How to pass variable between subs that are stored as array element through CreateObject("Scripting.Dictionary")
Replies
1
Views
524
Micron
M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
S
  • Question Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
M
  • Solved
New problem - FinalRow based on two criteria?
Replies
8
Views
700
johnnyL
J
V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
168
VinnyDoesntKnowExcelCode
V

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top