SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Mastering VBA Modeling: User Form Controls and Variable Management Techniques

gasha avatar
gasha
19 diapositives · 350 Views · Oct 21, 2014

This guide delves into the essentials of modeling using VBA, focusing on constructing user forms with a variety of controls like labels, text boxes, buttons, combo boxes, and more. You'll learn how to effectively utilize modules and procedures, understand the difference between procedures and functions, and grasp the concept of passing arguments by value or reference. The course also covers variable declaration, data types, and best practices for managing temporary information, enabling you to create robust and efficient VBA applications.

Mastering VBA Modeling: User Form Controls and Variable Management Techniques - slide 1
01 / 19Mastering VBA Modeling: User Form Controls and Variable Management Techniques
1 / 17

Télécharger la présentation

Mastering VBA Modeling: User Form Controls and Variable Management Techniques
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Modeling using VBA

  2. Using Toolbox Using the Toolbox select a GUI element and by mouse-click place it on the frame This is a label This is a text box This is a button

  3. Using User Form Work with Common Button, Text Box

  4. Using User Form Work with Common Button, Text Box

  5. Working with Combo Box

  6. Working with List Box

  7. Working with Image Control, Spin Button

  8. Work with Multipage, Option controls

  9. Work with Scroll Bar, Check Box, Frame controls

  10. Work with additional controls

  11. Modules & Procedures • Module– collection of logically related procedures & functions grouped together • Procedure– a group of ordered statements enclosed by Sub and End Sub • Function– the same as a procedure, but also returns some value and is enclosed between Function and End Function key words

  12. Procedure & Function Examples Sub ShowTime()Range("C1") = Now() End Sub Function sumNo(x, y) sumNo = x + y End Function Procedure: doesn’ t returns anything Function: returns something

  13. Calling procedures vs. calling functions If there are several sumNo functions in several modules/forms, need to use the full name of the function Sub z(a) MsgBox a End Sub Sub x()Call z("ABC") End Sub Sub y()z "ABC“ End Sub Sub ShowSum()varSum= Module1.sumNo(3,5) MsgBox varSum End Sub Function sumNo(x, y) sumNo = x + y End Function

  14. Passing Arguments by Value or by Reference • Passing arguments by reference – • Is the VBA default • Means, if any changes happened to the argument variables, they will be preserved after the function/procedure finishes • Passing arguments by value – • Is possible in VBA (by explicit definition) • Means, the pre-calling state of the argument variables will be preserved after the procedure/function finishes

  15. Arguments by Ref/by Val. Examples Sub TestPassing1() Dim y As Integer y = 50 AddNo1 y MsgBox y AddNo2 y MsgBox y End Sub Sub AddNo1(ByRef x As Integer) x = x + 10 End Sub Sub AddNo2(x As Integer) x = x + 10 End Sub public Sub TestPassing2() Dim y As Integer y = 50 AddNo3 y MsgBox y End Sub private Sub AddNo3(ByVal x _ As Integer) x = x + 10 End Sub

  16. Functions/Procedure Scope • Use public to allow any module to call the function/procedure • Use private to make limited access to the function/procedure (only from the owning module)

  17. VBA Variables • A variable is used to store temporary information within a Procedure, Function, Module… • A variable name • Must start with letter and can’t contain spaces and special characters (such as “&”, “%”, “\”) • Can’t be any excel keyword (“if”, “while”…) • Can’t have identical name to any existing class (“Worksheet”, “Workbook”…)

  18. VBA Data Type • Byte – positive integer numbers (0:255) • Integer – integers (-32,768 : 32,767) • Long – 4-byte integer • Currency – for fixed-point calculations • Single – 2-byte floating-point numbers • Double – double-precision floating-point numbers • Date – used to store dates and times as real numbers. • String – contains a sequence of characters

  19. Using Variables • Declaring Variables • Format: Dim varibleName AS dataType • Examples: • Dim myText As String • Dim myNum As Integer • Dim myObj As Range • The default value of • any numeric variable is zero • any string variable –“” (empty string) • an Object variable – is nothing