Defining a Variable in a userform

[chris-evans]

Hi all

I was hoping someone could help me. Many years ago i built a userform, through which i can select two sets of numbers and the form will then analyse the numbers for me. It has always worked fine and continues to do so.

I want to change it though and add another calc to give me the % difference between the two sets of numbers. My problem is i am quite rusty on my VBA and intuitively i am doing it right i cannot remember where i would have declared the variables for the userform. Where would i declare this? I can write the code to do the calc and pass it through to the label, i just can't find where or how i would have defined the other variables!

I have two simple scripts, i have underlined and made bold my amends, I know everything else works fine:

1) to compare numbers

Private Sub cbComparison_Click()

lbOriginalValueHolder = WorksheetFunction.Text(lbNumberSumHolder, "#,##0.000000000000000;(#,##0.000000000000000)")
lbComparisonValueHolder = WorksheetFunction.Text(WorksheetFunction.Sum(Selection), "#,##0.000000000000000;(#,##0.000000000000000)")
lbDeltaValueHolder = lbOriginalValueHolder - lbComparisonValueHolder
lbDeltaValuePercentHolder = lbOriginalValueHolder / lbComparisonValueHolder - 1


If lbDeltaValueHolder = 0 Then
lbDeltaGood.Caption = "No Delta"
Else
lbDeltaGood = "Delta"
End If

Format_Labels

End Sub



2) to format the datalabels

If OBWholeNumber = True Then
Select Case SBDecimal
Case 0
lbNumberSum = WorksheetFunction.Text(lbNumberSumHolder, "#,##0;(#,##0)")
lbOriginalValue = WorksheetFunction.Text(lbOriginalValueHolder, "#,##0;(#,##0)")
lbComparisonValue = WorksheetFunction.Text(lbComparisonValueHolder, "#,##0;(#,##0)")
lbDeltaValue = WorksheetFunction.Text(lbDeltaValueHolder, "#,##0;(#,##0)")
lbDeltaValuePercent = WorksheetFunction.Text(lbDeltaValuePercentHolder, "#,##0.0;(#,##0.0)")
Case 1 - note, this then goes through various case analyses depending on what decimal place i select on the user form.

When i now launch the form it just says "compile error : Variable not defined"

Any ideas where i'm going wrong would be appreciated.

Many thanks in advance!

Chris

 

[RoryA]

It's impossible to be sure without the form, but I imagine those are the names of controls on the form, not variables.

 

[chris-evans]

Yes they are the names of the labels on the forms. As an example, lbOriginalValue is one of the label names that i am populating with the value. So i created a new label on the form to take the new value called lbDeltaValuePercent in order to ensure there was a label for it.

 

[RoryA]

The 'variables not defined' error suggests that you didn't use the correct name in your code. If you type 'Me.' without the quotes, you should see a list of properties for the form once you type the period, and that will include the control names.

 

[chris-evans]

sorry for the dumb question, but where do i type this?

 

[RoryA]

In the VB editor where your code is. Instead of manually typing the control name, if you type 'Me.' you will get a list of available properties for the userform including all its controls. It will also scroll the list as you type, so if you type 'Me.lb' the list will scroll to any controls starting with 'lb'.

 

[chris-evans]

Thank you, i have solved it, there was a hidden label that i was using for some reason that i hadn't duplicated. Many thanks for your help.