vba project sample code - revised 11-13-2012

7/28/2019 VBA Project Sample Code - Revised 11-13-2012

1/11

General DeclarationsForce variable declaration

Option Explicit

Create Arrays for EACH data worksheet column

Dim QuestionOne (5 to 140) as String

Dim QuestionTwo (5 to 140) as String

Dim QuestionThree (5 to 140) as StringDim QuestionFour (5 to 140) as String

Arrays and variables for whole numbers should be Long NOT Integer

Dim QuestionFive (5 to 140) as Long

Dim Answer (5 to 140) as StringDim Picture (5 to 140) as String

Create variables for EACH ONE of the arrays above

Dim strQuestionOne as String

Dim strQuestionTwo as String

Dim strQuestionThree as StringDim strQuestionFour as String

Dim lngQuestionFive as Long

Dim strAnswer as StringDim strPicture as String

Create variable for current workbook path

Dim strPath as String

Create variables for Questions (if necessary for translation questions or

questions that use checkboxes

Create variable for Question Five to use when saving combobox text string

Dim strQuestionFive

START ButtonLoad Arrays with Data from Worksheet

Dim X as Integer

create a loop to search arrays for an answer(s)

For X= 5 to 140Answer(X)=Worksheets(Data).Range(A & X).ValueQuestionOne(X)=Worksheets(Data).Range(B & X).Value

QuestionTwo(X)=Worksheets(Data).Range(C & X).Value

QuestionThree(X)=Worksheets(Data).Range(D & X).Value

QuestionFour(X)=Worksheets(Data).Range(E & X).ValueQuestionFive(X)=Worksheets(Data).Range(F & X).Value

Picture(X)=Worksheets(Data).Range(G & X).Value


2/11

Next

Clear all controls that are used for questions or displaying answers

cboQuestionOne.ClearcboQuestionTwo.Clear

cboQuestionThree.ClearcboQuestionFour.ClearcboQuestionFive.Clear

txtAnswer.text=

imgAnswer.Picture = LoadPicture("")

Disable all controls that are used forquestions or answers

cboQuestionOne.Enabled=FalsecboQuestionTwo.Enabled=False

cboQuestionThree.Enabled=False

cboQuestionFour.Enabled=FalsecboQuestionFive.Enabled=False

txtAnswer.Enabled=False

imgAnswer.Enabled=False

Setup First Question

cboQuestionOne.Enabled=True

NOTE: Add a clear command EACH time you AddItems to a combo or list box.

cboQuestionOne.Clear

cboQuestionOne.AddItem First ChoicecboQuestionOne.AddItem Second Choice

cboQuestionOne.AddItem Third Choice

FIRST QUESTIONSave ChosenAnswer

strQuestionOne = cboQuestionOne.Value

Disable Current Question

cboQuestionOne.Enabled=False

Setup Next (Second) QuestioncboQuestionTwo.Enabled=True

Text choice example

cboQuestionTwo.Clear

cboQuestionTwo.AddItem Choice AcboQuestionTwo.AddItem Choice B

cboQuestionTwo.AddItem Choice C


3/11

Numeric choice example

cboQuestionTwo.Clear

cboQuestionTwo.AddItem 1cboQuestionTwo.AddItem 2

cboQuestionTwo.AddItem 3

Note: If you are using a numeric variable to save the answer to a combobox, whenever

that control is cleared, it will try to save the value and result in an error. The solution tothis is to use the following IF..Then statement when saving to a non-text variable:

Private Sub cboQuestionOne_Change()

If cboQuestionOne.Value ThenlngQuestionOne = cboQuestionOne.Value

Add anything else you plan to do when the question is answered here so it only

happens when the user selects an answer

End If

End Sub

SELECT CASE Examples

Example IF Choices Depend on an Answer to Previous Question

Select Case strQuestionTwo

Case "First Choice"cboQuestionThree.Clear

cboQuestionThree.AddItem "Choice A"

cboQuestionThree.AddItem "Choice C"

Case "Second Choice"cboQuestionThree.Clear

cboQuestionThree.AddItem "Choice B"

cboQuestionThree.AddItem "Choice C"Case "Third Choice"

cboQuestionThree.Clear


cboQuestionThree.AddItem "Choice B"End Select

Example IF Choices Depend on Answers to Two Previous Questions

Select Case strQuestionOne

Case "Option A"Select Case strQuestionTwo


4/11

Case "First Choice"

cboQuestionThree.Clear


cboQuestionThree.AddItem "Choice C"Case "Second Choice"

cboQuestionThree.ClearcboQuestionThree.AddItem "Choice B"cboQuestionThree.AddItem "Choice C"

Case "Third Choice"

cboQuestionThree.ClearcboQuestionThree.AddItem "Choice A"

cboQuestionThree.AddItem "Choice B"

End Select

Case "Option B"Select Case strQuestionTwo

Case "First Choice"

cboQuestionThree.ClearcboQuestionThree.AddItem "Choice D"

cboQuestionThree.AddItem "Choice F"

Case "Second Choice"

cboQuestionThree.ClearcboQuestionThree.AddItem "Choice E"

cboQuestionThree.AddItem "Choice F"

Case "Third Choice"cboQuestionThree.Clear

cboQuestionThree.AddItem "Choice D"

cboQuestionThree.AddItem "Choice E"

End SelectEnd Select

IF you want to add some variety, you can use option buttons and/or check boxesanexample of each is below

OPTION BUTTON/CHECKBOX Examples

Enable the controls

optChoiceA.Enabled = TruechkChoiceOne.Enabled = True

Clear the controls

optChoiceA.Value = FalsechkChoiceOne.Value = False


5/11

Unique Issues:

Multiple checkboxes may be selected by the user; therefore, EACH Checkbox must have

a different variable in which to store the users choice(s)

Only one option button can be selected at a time; therefore, there is only one variableneeded for a question with option buttons. Note: If you want option buttons for morethan one question, you have to group them by specifying a Group Name in properties

then each option button in that group will be independent of option buttons in other

groups.

The value of either a checkbox or an option button is Booleaneither TRUE or FALSE.

You can put these values in a String variablethe result will be the words True or

False depending on the stored value.

QUESTION SEQUENCING OPTIONS

Setup Next Question after Two (or more) Option Buttons

Private Sub optQuestionOneA_Click()Capture Option Button Value in a Single Variable

If optQuestionOneA.Value = True Then

strQuestionOne = "A"End If

Then enable and populate Next question as above

End Sub

Private Sub optQuestionOneB_Click()

Capture Option Button Value in a Single VariableIf optQuestionOneB.Value = True Then

strQuestionOne = "B"

End If

Then enable and populate Next question as aboveEnd Sub

Setup Next Question after Two (or more) CheckBoxesNote: if possible, make your check boxes your LAST question. This will mean you will

need to create a FIND button instead of automatically looking for an answer after theusers answer your last question.

Private Sub chkQuestionFiveA_Click()

If Checked, Capture Check Button Value in a Single Variable; otherwise, if

unchecked Clear the VariableIf chkQuestionFiveA.Value = True Then

strQuestionFiveA = "A"


6/11

Else: strQuestionfiveA = ""

End If

End Sub

Private Sub chkQuestionFiveB_Click()

If Checked, Capture Check Button Value in a Single Variable; otherwise, ifunchecked Clear the Variable

If chkQuestionFiveB.Value = True Then

strQuestionFiveA = "B"Else: strQuestionfiveA = ""

End If

End Sub

TRANSLATING ANSWERSText answer translated into a Yes/No search on multiple mutually exclusive columnsCapture the Answer into a string variable

strQuestionOne=cboQuestionOne.ValueTranslate the answer into Three Yes/No variables

Select Case strQuestionOneCase Answer A

strColumnA=Yes

strColumnB=NostrColumnC=No

Case Answer B

strColumnA=No

strColumnB=YesstrColumnC=No

Case Answer CstrColumnA=NostrColumnB=No

strColumnC=Yes

End Select

Price Range (text answer) translated into a number(s) to allow for mathematical

search on a single column

Capture the Answer into a string variablestrQuestionOne=cboQuestionOne.Value

Translate the answer into a long variable using the highest number in the answer

Select Case strQuestionOneCase Answer 1-10

lngColumnA=10

Case Answer 11-20

lngColumnA=20Case Answer21-30

lngColumnA=30

End Select


7/11

Translate the answer into a two long variables for the high and low value of the range

Select Case strQuestionOneCase Answer 1-10

lngColumnAH=10

lngColumnAL=1Case Answer 11-20lngColumnAH=20

lngColumnAL=11

Case Answer 21-30lngColumnAH=30

lngColumnAL=21

End Select

Two Answers where one is exclusive and the other means BOTH

Private Sub optQuestionOneA_Click()CaptureExclusive Option Button Value by saving the opposite value

If optQuestionOneA.Value = True Then

strQuestionOne = "B"

End IfEnd Sub

Private Sub optQuestionOneB_Click()

CaptureInclusive Option Button Value by saving a blank text valueIf optQuestionOneA.Value = True Then

strQuestionOne = ""

End If

End SubIn the Find Button IfThen test, use a does not equal test

If strQuestionOne QuestionOne(X)

by doing this, if the choice was A and the variable has B and the only Choices areA or B in the data, then A will be returned by the above equation. On the other

hand, if the choice was B and that would mean both answers were correct matches, by

having a in the variable, either option would be returned.

Three (or More) Text Answers where there is a cumulative relationship is best done

by adding an additional column in the data that has simple numeric values. In the

following example, the second column has numeric values such that a 3 corresponds

to A in the original column, 2 corresponds to B, and 1 corresponds to C.Capture the Answer into a string variable

strQuestionOne=cboQuestionOne.Value

Translate the answer into a long variable using the highest number in the answerSelect Case strQuestionOne

Case A

lngQuestionOne=3Case B


8/11

lngQuestionOne=2

Case C

lngQuestionOne=1

End SelectIn the Find Button IfThen test, use a less than or equal to test

If lngQuestionOne


9/11

If strQuestionOne = QuestionOne(X) And strQuestionTwo =

QuestionTwo(X) And strQuestionThree = QuestionThree(X) And

strQuestionFour =QuestionFour(X) And strQuestionFive =

QuestionFive(X) ThenPut Answers in the Listbox

lstAnswer.Additem Answer(X)End IfNext

LISTBOX (if used after FIND Button)Save Listbox Answer

strFinalAnswer =lstAnswer.Value

Enable Answer Text BoxtxtAnswer.Enabled = True

Enable Answer Picture ControlimgAnswer.Visible=True

create a temp variable to use in your loop

Dim X As Integercreate a loop to search arrays for an answer(s)

For X = 5 to 140

Note: the following command is all on ONE lineIf strFinalAnswer = Answer(X) Then

Put value of string variable into text box

txtAnswer.text=Your choice is & strFinalAnswer & .

Put Picture in a string variablestrPicture=Picture(X)

Find Current Workbook Path

strPath=ActiveWorkbook.PathUse value of string variable to load correct picture

imgAnswer.Picture=LoadPicture(strPath & \ & strPicture & .jpg)

End IfNext

FIND Button Example for Using Both AND and OR testsFor instance, if you have Yes/No data in multiple columns and the data in each column is

not mutually exclusive of the other related columns, then you need to use a nestedIFThen statementinside the first IFThen statement.

Enable Answer Text Box

txtAnswer.Enabled = True

Enable Answer Picture ControlimgAnswer.Visible=True

Create a temp variable to use in your loop


10/11

Dim X As Integer

Create a loop to search arrays for an answer(s)

For X = 5 to 140

Note: the following command is all on ONE lineIf strQuestionOne = QuestionOne(X) And strQuestionTwo =

QuestionTwo(X) And strQuestionThree = QuestionThree(X) AndstrQuestionFour = QuestionFour(X) Thenif all of the above is True and need to use an OR test with multiple

possibilities where AT LEAST one must be True, then add a nested IF

StatementIf strQuestionFiveColumnA = QuestionFiveColumnA(X) Or

strQuestionFiveColumnB = QuestionFiveColumnB(X) Or

strQuestionFiveColumnC = QuestionFiveColumnC(X) Or

strQuestionFiveColumnD = QuestionFiveColumnD(X) ThenPut Answer in astring variable

strAnswer=Answer(X)

Put value of string variable into text boxtxtAnswer.text=Your choice is & strAnswer & .

Put Picture in a string variable

strPicture=Picture(X)

Find Current Workbook PathstrPath=ActiveWorkbook.Path

Use value of string variable to load correct picture

imgAnswer.Picture=LoadPicture(strPath & \ & strPicture &.jpg)

End If

End IF

Next


11/11

TROUBLESHOOTING

Problem with Next Question setupCapture question answer into a variable

strQuestionOne=QuestionOne.Value

Create a Msgbox to display the questions variable immediately after selection by UserMsgbox strQuestionOne

Problem with Getting an AnswerIF there are no Error Messages AND you do not get an Answer, you can use the following trouble-

shooting procedure to double-check the values being stored in your variables and the translated

value(s) you are using to search your data. You can then compare these to the actual data on your

data sheet.

Msgbox for each Question Answer

Capture question answer into a variable

strQuestionOne=QuestionOne.ValueCreate a Msgbox to display the questions variable immediately after selection by User

Msgbox strQuestionOne

Textbox for Search Variables (these will only be different for translated answers)

Create a TextBox forEACH search variable and place them on your sheet near the

question to which they correspond (you will delete these later, so just put them in close

proximity to the question and dont worry about the aesthetics). Then, add a command

BEFORE you search for an answer that puts the variable(s) that correspond to EACHcolumn of data in your data sheet into these textboxes.

NOTE: For any translation question, you MAY have more than one column to search inyour data; therefore, you will have more than one textbox for that question.

txtQuestionOne.text=strQuestionOneOR

txtQuestionOne.text=lngQuestionFive

Compare these results to find and fix any discrepancies

vba project sample code - revised 11-13-2012

Documents