Database Develop

Excel VB question: re: Arrays

Hey,
I am working on something for work and it has to do with a simple database (excel file) with data kept in rows. This data is accessed using a userform that looks up a query that happens to be the data kept in the first column of the spreadsheet. Basically, each row contains 46 cells, including the first cell (the query).
I am hoping to use the same userform to search, update, save, print and quit excel. So far search works:
Code:
Sub CommandButton1_Click()
dim query as range
set query = range("A:A")
call readData(query.find(TextBox1, lookat:=xlWhole).row)
End Sub
Private Sub readData(x as integer)
Dim boxes as range
set boxes = range("A:A")
TextBox1 = boxes.cells(x).offset(0,0)
TextBox2 = boxes.cells(x).offset(0,1)
....
End Sub
This works fine for search; however, as there is 45 boxes containing different kinds of data, I would rather read the cells from the spreadsheet using a loop and setting the TextBoxN as a static array:
Code:
Sub readData(x as integer)
***** VB treats array(45) as an array having 46 elements *****
dim textBoxArray(45) as [u]Variant[/u]
set textBoxArray = array(TextBox1, TextBox2... TextBox46)
For i = 1 to 46
textBoxArray(i-1) = cells(x, i)
Next i
End Sub
Note that "query.find(TextBox1, lookat:=xlWhole).row" returns the row number of the query and this is passed as an integer to the readData sub-routine and is referred to as "x".
I would like to do this for the read and then do the same, although opposite for the update (i guess I could call it the 'write' back to the excel worksheet).
The problem is, with this code I get an error that says the array is not declared or arranged (Japanese laptop: 配列は割り当てられません。)
Question 1
Any of you developers with any vb experience know how I can correctly declare my array (would be nice if I could decare the array globally so that I don't have to re-declare it for every sub-routine, and I can just use the for loop in each sub-routine)?
At present, all the code is within the userform and I cannot seem to declare outside of a subroutine:
Code:
Public boxArray(45) as Variant
Sub CommandButton_Click()
...
End Sub
Question 2 SOLVED I removed the "set" from the line where I filled the array with each textbox. Tested it again and all data is flowing correctly. Only need question 1 answered.
Thank you.

Hey all, thanks for the replies; I didn't think the thread would be this popular.
I solved the problem with global variables. Instead of setting a fixed array size in the main code section dec area, I found another way of assigning values to all 46 textboxes.
Instead of:
Code:
Dim boxArray(45) as Variant
boxArray(45) = array(TextBox1, TextBox2, ... TextBox46)
then
Code:
For i = 1 to 46
boxArray(i-1) = cells(x,i)
Next i
I used vb's Control method/function (no array necessary):
Code:
For i = 1 to 46
Control("TextBox" + CStr(i)) = cells(x,i)
Next i
So i turned 46+ lines into 6+ lines into 3 lines.
MODS, this thread may be closed. THanks.

help VB

can some one tell me whats wrong with this code
Code:
Private Sub btnDisplay_Click( . . . ) Handles btnDisplay. Click
‘Toggle switch from on to off and from off to on.
Dim switchOn As Boolean
switchOn = CBool( InputBox( " Enter True or False. " , " The switch is on. " ) )
If switchOn Then
switchOn = False
End If
If Not switchOn Then
switchOn = True
End If
txtOutput. Text = CStr( switchOn)
End Sub
and also
Code:
Private Sub btnDisplay_Click( . . . ) Handles btnDisplay. Click
‘Display twice the length of a word.
Dim word As String
word = InputBox( " Enter your favorite word " )
txtOutput.Text = “When the word is written twice, “ &
Twice (word) & “ letters are used.”
End Sub
Function Twice (w As String) As Integer
‘Compute twice the length of a string.
Dim len As Integer
Return len = 2 * w.Length
End Function

Is that java? I haven't programmed in a while, if that's java then you need ; at the end of line
Edit: oh that's vb, disregard my post
Sent from my Xperia L using Tapatalk

fujirio said:
can some one tell me whats wrong with this code
Click to expand...
Click to collapse
The first one is wrong where you do this...
Code:
If switchOn Then
switchOn = False
End If
If Not switchOn Then
switchOn = True
End If
You need an else or you're setting it to false and then saying "if it's false make it true" immediately after....
Code:
If switchOn Then
switchOn = False
Else
switchOn = True
End If
Also, I'd recommend looking at using MessageBox as you can show a message and specify what buttons to have - MUCH better than asking someone to type true or false (and then have to worry about people typing other things, or spelling mistakes or upper/lower case characters).
The second one is wrong in the Twice function. You are returning the value of setting the variable len, not the actual value of it. It's easier to get rid of the variable in this case as it serves no purpose...
Code:
Function Twice (w As String) As Integer
‘Compute twice the length of a string.
Return 2 * w.Length
End Function

[Q] Variable Value

help after i finish installing Android Studio it tells me i have an invalid variable value. i have jdk-8u31-windows-x64.exe . what is a variable value and how do i find it.
also its located in environment variable in control panel, I have a ASUS Computer with 64 bits

how does boolean logic works on vaarible value context?

Hi,
I've searching on the web but with no luck so far.
As you know, variable value context has the option to add several conditions linked by AND/OR/XOR operations. So we can have something like
If %var1~val1 AND %var2~val2 OR %var3~val3
Since boolean operators are binary, my question is how are they grouped? Since (P AND Q) OR R is different than P AND (Q OR R)...
Regards,

You determine grouping by how you write your code.
If (P and Q) or R:
If P & Q
Do something
Else If R
Do something
End If
Or....
If P
If Q
Do something
End If
Else If R
Do something
End If
If P and (Q or R):
If P
If Q
Do something
Else If R
Do something
End If
End If
Or....
If P & Q
Do something
Else If P & R
Do something
End If
..... You might also find matching Regex useful.

Hi,
this works inside a task, but my question is how is P OR Q AND R interpreted in a variable value context, where we only have the if (no else) and only one if.
Regards,

Single conditions consist of a left-hand side (usually a variable name), an operator and a right-hand-side for example %number, Equals, 1 indicates that the action will be executed if the variable %number has the value 1.
When more than one condition is specified, they must be combined via And (all conditions must be true), Or (at least one condition must be true) or Xor (exactly one must be true). These 'combiners' are called boolean operators.
Usually, 2 or 3 conditions will be combined with all Ands or all Ors, but in order to allow more complicated logic, Tasker also offers And and Or in high-precedence versions. Of the 4 boolean operators which are available, the selection goes from low to high precedence ones.
The higher the precedence of a boolean operator, the further to the right it is shown. This enables the logical groups to be visualised.
Examples:
True | False & True | False is the same as ( True | False ) & ( True | False ) so is True.
True & False | True & False is the same as True & ( False | True ) & False so is False.
True & False | True |+ False is the same as True & ( False | ( True | False ) ) so is True.
Note that the order of the conditions can mean that some conditions are never evaluated. For instance, when two conditions are present and the one above an And is false then the condition below it will never be evaluated. This can be advantageous if the second condition takes relatively more resources e.g. involves matching against a lot of text.

hello, so if i'm getting this right, P & Q | R would be P&(Q|R)?

Overwrite array value

Hello
I’m trying to overwrite an array value. The array containing the values that need to be changed is %ObdFuelAvgArr the index of the value I’m trying to set is %ObdPlaceHolder and the value that I’m trying to insert is %ObdPercent. I’ve tried various ways of entering this into tasker. The latest iteration was this:
Code:
Variable Array
%ObdFuelAvgArr(%ObdPlaceHolder)
Values
ObdPercent
Could someone help me find a solution?
Thanks,
Loren

You need to Use the Array Push action probably in conjunction with the Fill Spaces parameter. Depending on whether the array element is already occupied, you may need to empty it before updating using a Variable Clear.
From the Tasker user guide: variables page;
Inserting Elements
Use the Array Push action.
The Fill Spaces parameter might need more explanation. It is only relevant if one or more of the array elements are undefined. As an example, if we have the array elements %arr1 and %arr3 containing apple and banana:
Array Push %arr1, 1, pear
leaves %arr1, %arr2 and %arr4 containing pear, apple and banana.
but Array Push %arr2, 1, pear, Fill Spaces
leaves %arr1, %arr2 and %arr3 containing pear, apple and banana.
Removing Elements
Use the Array Pop action. Note the difference between Array Pop and Variable Clear: Pop reduces the number of elements in the array, while Clear merely changes elements to undefined.
Example: if we have the array elements %arr1, %arr2, %arr3 containing apple,pear and banana:
Variable Clear %arr2
leaves %arr1 and %arr3 containing apple and banana.
but Array Pop %arr2
leaves %arr1 and %arr2 containing apple and banana.
Click to expand...
Click to collapse
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers