PDA

View Full Version : [SOLVED] Need Help passing a userform.texbox object to a sub



werafa
02-24-2017, 05:09 AM
Hi,

I'm attempting to pass a textbox from a user form to a subroutine that will strip out any numeric values (remove alpha characters etc) from the textbox.value, and format the resulting value.

I've tried a number of suggestions from the web, but keep getting Error 424, object required.

can anyone point me at the correct method for doing this?

my code is:

Private Sub FormatNMIForm()
Dim myTextbox As msforms.TextBox

With frmNMIDetails
Set myTextbox = .tbxFacilityBillkWh: FormatAskWh (myTextbox)
FormatAskWh (.tbxAnnualkWh)

FormatAskVA (.tbxFacilityBillKVA)

FormatAsDollars (.tbxFacilityBillCharges)
FormatAsDollars (.tbxkWhCharges)
FormatAsDollars (.tbxDemandCharges)
FormatAsDollars (.tbxFacilityAnnualCharges)

FormatAsCperkWh (.tbxckWh)

FormatAsDperkVA (.tbxKVADollars)
End With

End Sub

and


Public Sub FormatAskWh(ByRef myTextbox As Control)
Dim myVal As Double


myVal = ParseNumbers(myTextbox.Value)
myTextbox.Value = Format(myVal, "0.00 ""kWh""")


End Sub


Public Sub FormatAskVA(ByRef myTextbox As msforms.TextBox)
Dim myVal As Double


myVal = ParseNumbers(myTextbox.Value)
myTextbox.Value = Format(myVal, "0.00 ""kVA""")


End Sub

thanks in advance

SamT
02-24-2017, 09:07 AM
Try losing the Parens

Set myTextbox = .tbxFacilityBillkWh: FormatAskWh myTextbox

FormatAskWh .tbxAnnualkWh

FormatAskVA .tbxFacilityBillKVA
The Subs are not returning anything

werafa
02-24-2017, 12:44 PM
Thanks SamT
But I'm beat to know why this works.

but it does

SamT
02-24-2017, 01:50 PM
I don't know why you got that particular error message, but I have learned that while the VBE doesn't always "know" exactly where the error is, it always tells you what point in the code that the error is before.


The Parens around Procedure Input Parameters are required when you expect a result back

X = Function(InputValue)
Otherwise they aren't allowed

Function InputValue
Sub InputValue

BTW, you can make your Procedures "one liners" like

Public Sub FormatAskVA(ByRef myTextbox As msforms.TextBox)
myTextbox.Value = Format(ParseNumbers(myTextbox.Value), "0.00 ""kVA""")
End Sub

SamT
02-24-2017, 02:07 PM
This method will format the input as soon as it is entered. Note the Parens around the InputValue to the Function


private sub tbxFacilityBillkWh_Change()
with Me.tbxFacilityBillkWh
.Value = FormatAskWh(.Value)
end with
end sub

Since you had this as a Public Procedure, I left it available for use by other than the UserForm itself. This meant declaring the InputValue as a variant so it could handle Strings and numbers.

public function FormatAskWh(FormatWhat As Variant) As String
FormatAskWh = Format(ParseNumbers(FormatWhat), "0.00 ""kWh""")
end function

SamT
02-24-2017, 05:42 PM
This call

FormatAskWh (.tbxAnnualkWh)

gave it a TextBox.Caption
?!?!

Parens around Procedure Parameters in a Call, must be used in a Set type Statement, eg. There's an equals sign before the Call. Tersely, If there's Parens, there hasta be an =. If there's an =, there hasta be Parens.

You can't use a Sub Procedure in a Let type or Set type Call.

That Call above is calling a Sub using Set type Syntax.

werafa
02-24-2017, 09:22 PM
hmm.

it would seem I still have some to learn.
Have always got away with parens before, and thought this was merely a user preference.

shall have to read this a few times to realy get what you are saying methinks

werafa
02-25-2017, 01:05 AM
I get most of what you are saying, however I write many subs that do not return a value, and in which I do use parens.
(and I do use option explicit and early binding)

eg.


....
call DoSomething(1)
....

sub DoSomething (myInput as string)
myrange = myinput
end sub


This has always worked before.

I usually set objects within the relevant procedure or pass them in - and have not yet had recourse to doing this by function, so I'll take your word on this and be the wiser for it :)

I also take your point about reducing the number of lines of code. I do like to keep syntax that is readable and self evident, but am gaining enough experience that I can now follow the more compressed code. I'm using these functions in a userform to manage entered data (users may enter the units, thus rendering the entry as text.) I've set code to strip off the number and then apply the number formatting, and will shortly see how this transfers back to the excel sheet.

Thanks to your help I've now one less bug to crack :) :)

werafa
02-25-2017, 01:18 AM
and, for the benefit of all:

If you have an open parenthesis preceded by a space, you should remove the parentheses.
When you put parentheses around an object, VBA evaluates that object and, absent a property, returns the default property. This often works anyway, but can fail in unexpected ways.

This was the cause of the failure to pass the textbox object correctly

SamT
02-25-2017, 07:25 AM
(users may enter the units, thus rendering the entry as text.) I've set code to strip off the number and then apply the number formatting, and will shortly see how this transfers back to the excel sheet.

Almost all Controls on UserForms return Stings as their Value. VBA is lax enough that it will sometimes "auto" convert obvious numerical values to the appropriate number Type.

Excel stores almost numerical data as number Type Double, regardless of how it is displayed. In order to guaranty compatibility with Excel, before inserting a formatted number into a Cell covert it to a Double Type.

If entering a Control number Value into a Cell, always convert it to a Double, (actually, most any numerical type will work but developing a habit of using Excel's native Type is a "Good Thing.")

Range("A1") = CDbl(TextBox1)

werafa
02-25-2017, 09:59 PM
This is the code I left out of the initial post


Public Function ParseNumbers(myString As String) As Double'extract number only content from a string
Dim myCount As Integer
Dim myNum As String


For myCount = 1 To Len(myString)
If IsNumeric(Mid(myString, myCount, 1)) = True Or Mid(myString, myCount, 1) = "." Then
myNum = myNum & Mid(myString, myCount, 1)
End If
Next myCount
If myNum = "" Then myNum = 0
ParseNumbers = CDbl(myNum)

End Function

it cycles through a string, extracts decimals and decimal points, and returns a double.
It assumes that the data entered may be in the form of a number with the units added, eg, the string may be "100.1 kWh". This is in fact what the text boxes record once I've applied my number formatting to them, so I also run this function before saving back to the spreadsheet.

SamT
02-26-2017, 07:02 AM
So after running ParseNumbers, the textbox displays like "100.1"

In that case all you need before passing it to excel is CDbl(TextBox)

However, If the code is fast enough in your situation, I would not rewrite it just for that little bit of added speed.


The only changes I see to make ParseNumers more robust to check for multiple Dots, (decimal points and periods,) and

If myNum = "" Or MyNum = "." Then myNum = 0


If Instr(myString, ".") <> 0 And Instr(myString, ".") <> InstrRev(myString, ".") Then MyNum = "Error Message" Would have to change ParseNumbers to a Variant.

werafa
02-26-2017, 07:05 PM
I format the number back into the textbox so it 'reads' clearly,
and so must parse it again before saving back to excel.
receiving a string and returning a double is sufficient in each case, and I'll only ever strike trouble if a user enters two decimal points (at which point my model will fail and the user will need to correct the issue)

I'm happy with the speed of operation at this stage, as I'm only passing about 10 variables in any one save operation so far.

werafa
02-26-2017, 07:08 PM
I also avoid using variants where possible - simply as a matter of policy.
I'd rather understand what I need and define it as such than keep a variant out of laziness.
(I do acknowledge that variants are sometimes required or are advise)