I have a project with classes, modules and user forms and I need to know how to structure it and put what functions/subs where, so it runs.
I have a 3rd party library installed in VBA that allows me to connect to a server and send orders to that server that are collected from a UserForm. This library has a couple of classes that I'm using: connection_ = exampleLib.Connection to connect to the server that uses WithEvents to call connection_.ConnectionStatus to find the status of that connection (connecting/connected/disconnected); and connection_.Sender(str1) that take a string from a User Form and sends it to the server.
I currently have the Connection and ConnectionStatus running when a user clicks a button which pops up a User Form to log in with username and password and that is successful at logging in and telling the user if they have connected. That is currently in a Forms module. My issue is when the user enters a string to send to the server using a sub in a different module. When the function starts it doesn’t recognize connection_ so I have to Dim connection_ as exampleLib.Connection and then through the whole procedure I can’t send to server since this connection_ = Nothing. My question is how do I set these up across the whole project so 1. My User Form Login will pop and connect to the server and WithEvents show the connection status. 2. Have a procedure that can take a string from another User form, keep the connection_ already established and send to server. Example of code is below. User Form Login is the form that opens login and User Form Input is the form to input string.
My Error is Run-time error ‘91’: Object variable or With block variable not set at Set sender = connnection_.GetSender. Any help is appreciated.
User Form Login:
Dim WithEvents connection_ As exampleLib.Connection
Private Sub Form_OKBTN_Click()
Dim user, pwd
If connection_ Is Nothing Then
Set connection_ = New exampleLib.Connection
End If
user = USERForm.Value
pwd = PWDForm.Value
PWDForm.Value = ""
USERForm.Value = ""
FormLOGIN.Hide
connection_.LogOn _
user, _
pwd
End Sub
Private Sub connection__ConnectionStateChanged(ByVal ConnectionState As exampleLib.ConnectionStatus, ByVal sReason As String)
Select Case (connection_.ConnectionState)
Case ConnectionStatus_Connected
Range("Form_CONNECTION_STATUS").Value = "Connected"
Case ConnectionStatus_Disconnected
Range("Form_CONNECTION_STATUS").Value = "Disconnected"
Case Else
Range("Form_CONNECTION_STATUS").Value = "Connecting..."
End Select
End Sub
User Form Input:
Private Sub ConfirmBtn_Click()
Dim inputStr As String
inputStr = FormInput.Value
FormInput.Value = ""
FromBox.Hide
SendString (inputStr)
End Sub
Module Sub:
Public Sub SendString(inputStr)
Dim connnection_ As exampleLib.Connection
Dim sender As exampleLib.Sender
Dim inputStr As String
If connnection_ Is Nothing Then
FormLOGIN.Show
ElseIf Not connnection_.ConnectionStatus = ConnectionStatus_Connected Then
FormLOGIN.Show
End If
Set sender = connnection_.GetSender
sender.Send _
inputStr
End Sub
Bookmarks