Couple of basic questions from a beginner:

[Simon.Whiteside]

Hi All,

New to VBA programming (learning from a book), I'm progressing well
and am actually really enjoying the learning but have a couple of
questions:

The book I am using is pretty good, but the bit on Dim statements is
confusing me a bit and to make things worse I actually managed to
leave it at home today... I've gone headfirst into developing a
userform and I'm not sure just what the quickest/ easiest way is to
name all the variables (and, if I'm honest, just what the variables
are). Questions as follows:

My userform has a plenty of DatePickers, TextBoxes, ComboBoxes etc in,
would each of these be classed as a variable? If not then what is?!
Would these be appropriate: DatePickers as Date, ComboBoxes as String
and TextBoxes (if containing numbers) as Integer?
Where do I put the Dim statements, do they need to go at the start of
each Sub or is it just under Userform or is it in a separate module??

Also, I'm just wondering, can I use IF statements within a MsgBox and
if so how? I want the MsgBox to display slightly different things
depending on the value of a ComboBox, do I need a separate MsgBox for
each choice in the MsgBox or is there some clever way of doing it?

As you can see I am just starting up and would really appreciate any
help.

Thanks

Simon

 

[NickHK]

The DatePickers, TextBoxes, ComboBoxes etc are part of the Controls
collection. They are all Objects, so you can:
Dim MyObj as Object
Set MyObj =TextBox1
or
Set MyObj =ComboBox1

What MyObj can then do will depend on the properties/methods of the TextBox
or Combox.

You can be more refined with
Dim MyTextBox as MSForms.TextBox
Set MyTextBox =TextBox1

but then you cannot do
<DOES NOT WORK - give type mismatch error>
Set MyTextBox =ComboBox1
</DOES NOT WORK>

But you seem to want to reference the properties of these controls, rather
than the controls themselves.
Dim StringValue As String
StringValue=ComboxBox1.Text
StringValue=TextBox1.Text

Note the TextBox.Text is always a string, even if a number a entered. You
can explicitly coerce this to a numeric if possible

Dim NumericValue as Double 'Or Long
If IsNumeric(TextBox1.Text) Then
NumericValue =CDbl(TextBox1.Text)
End If

The Object Browser (press F2 in the VBE) will show you the properties,
methods and events. Select MSForms from the top combobox, then in the
"Classes" panes you will see CheckBox, ComboBox etc.
Play around with that to see what is available.
This will be what you can see with Intellisense, but with the data
type/retun value shown at the bottom.

NickHK

 

[merjet]

Responding to your MsgBox question:

It is hardly more efficient or clever but you could build a String
variable that varies with the value of the ComboBox and use the String
variable in the MsgBox's prompt.

Hth,
Merjet