how to store comments in a string?

[antonyjones1]

Hi there,

I'm looking to store some comments in a string that I can then call at later points if certain criteria is met. Would the below be along the right track?

[VBA]Dim Output1 As String
Dim Output2 As String
Dim Output3 As String

Output1 = "Hello"
Output2 = "Hi"
Output 3 = "Hey"
[/VBA]

[macropod]

That's one of many ways. Another might be:

Dim StrCmt as String
StrCmt =",Hello,Hi,Hey,"

(note the commas at eithe end of the string, plus those separating the expressions)
This can be useful if you have many strings and you want to refer to them by number. For example:

Split(StrCmt, ",")(1)

will return 'Hello'.

Also useful if you want to find out if a proposed comment is valid, which you could do with code like:

If Instr(StrCmt, "," & InputBox("Test Comment") & ",") > 0 Then
  MsgBox "Valid Comment"
Else
  MsgBox "Invalid Comment"
End if

[antonyjones1]

Thanks for the prompt reply! This looks like the best way forme to do it as I will want to incorporate them into an if/else statement

[Paul_Hossler]

An alternative to consider in addition to Paul's E.'s approach

If you have a lot of messages, or if they change/grow, and the messages really don't need to be in a string

I'd rather let the computer keep track of some things. Using 'Enum' allows the VBA Intellisense to kick in and makes IMHO makes it easier to pick the message I want

[vba]
Option Explicit

Enum tMsg
tSmaller = 0
tSameAs = 1
tLarger = 2
tCommas = 3
tQuotes = 4
End Enum

Dim aMsgs As Variant

Sub InitMsgs()
aMsgs = Array( _
"Smaller than", "Same As", "Larger than", _
"Msg, with, commas", _
"Msg with ""QUOTE CHARS"" quotes")
End Sub

Sub ShowMsg(msgnum As tMsg)
If CLng(msgnum) < LBound(aMsgs) Or CLng(msgnum) > UBound(aMsgs) Then
MsgBox "Bad message number passed = " & msgnum
Else
MsgBox aMsgs(msgnum)
End If
End Sub

Sub drv()
Call InitMsgs
If 1 > 0 Then ShowMsg (tLarger)
If 1 < 2 Then ShowMsg (tSmaller)
If 1 = 1 Then ShowMsg (tSameAs)
If True Then ShowMsg (tCommas)
If Not False Then ShowMsg (tQuotes)
If True Then ShowMsg (111)
End Sub

[/vba]


You can really get carried away with this.

For Example, each msg in the array could start with a 'tag' and you could search the array for the msg that starts with that tag. That way you don't need to worry about a lot of housekeepng because the computer is doing it for you, and the message selection would be more intuitive

e.g. Array = "##LT##Less Than", etc. and then Call ShowMsg("lt"), using 'LT" instead of 0


Paul H.