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.

Looking for help : )

Hi Everyone,
I'm new to this forum but I have a quick question about how I should start implementing this flow diagram:
1. store sensor data into array x
2. if temp array index x-1 exists
yes: check if value in index x-1==x
no: compare with file entry i
3. x matches entry i
No: increment file entry i++
Any help is greatly appreciated!
Thanks
Cookie

You should look for this document in the Internet - "Fundamentals of Digital Logic with VHDL Design"

Why is Tasker storing the outcome of a calculation in scientific notation?

Not sure if this is the right forum for this question, sorry if it isn't.
I'm trying to calculate a time in the future, by adding some value to %TIMES. I'm adding 24 hours max. For some reason, the variable I'm storing the result in, ends up like this: 1.515....E9. Normally, this wouldn't be an issue, but for some reason, not a single application in Tasker seems to be able to handle this scientific notation, so I'm stuck with a useless number. Is there a way I can prevent Tasker from writing this number in this notation, or is there a way to convert it to a regular number?
Tim

The %TIMES variable is the current time in seconds since January 1, 1970. A very big number.
Typically time values are manipulated by doing a "variable convert" action.
Could you post your task DESCRIPTION here? Long press the task to select it -> 3-dot menu -> export -> description to clipboard.
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers

I'm currently doing this through Variable Set. It's been a long time since I used tasker (set once and forget, right), so it's probably something dumb I'm doing wrong.
Here's the description (and thank you for providing instructions on how to do it):
Update Times (25)
A1: AutoAlarm [ Configuration:No configuration needed Timeout (Seconds):5 ]
A2: Variable Set [ Name:%minsBefore To:20 Recurse Variablesff Do Mathsff Appendff ]
A3: Variable Set [ Name:%preAlarmTime To:%TIMES+%seconds-(%minsBefore*60) Recurse Variablesff Do Mathsn Appendff ]
A4: Variable Set [ Name:%dismissNotification_preAlarmTime To:%TIMES+%seconds-(120*60) Recurse Variablesff Do Mathsn Appendff ]
A5: Variable Convert [ Name:%preAlarmTime Function:Seconds to Date Time Store Result In:%readable_preAlarmTime ]
Click to expand...
Click to collapse
Some notes:
I know A2 is weird and useless currently, it's a placeholder for something I'm gonna add later.
Action A4 is also irrelevant at the moment, again a placeholder for later.
Action A5 throws an error, due to %minsBefore being stored in scientific notation (I'm assuming, see error below)
15.17.29/Variables doreplresult: |%preAlarmTime| -> |1.51539179987E9|
15.17.29/E Variable Convert: %preAlarmTime -> %preAlarmTime
15.17.29/E Variable Convert: %readable_preAlarmTime -> %readable_preAlarmTime
15.17.29/E Variable Convert: %preAlarmTime: failed.
15.17.29/E result: stop task (error)
15.17.29/Variables doreplresult: |%preAlarmTime| -> |1.51539179987E9|
15.17.29/Variables doreplresult: |%readable_preAlarmTime| -> |08:15|
15.17.29/E Error: 1
15.17.29/MacroEdit action finished exeID 1 action no 4 code 596 status: Err next 4
Click to expand...
Click to collapse

In A3, try using the round function on the seconds variable;
%TIMES+round(%seconds)-(%minsBefore*60)
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers

ktmom said:
In A3, try using the round function on the seconds variable;
%TIMES+round(%seconds)-(%minsBefore*60)
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers
Click to expand...
Click to collapse
I'm almost beyond belief.. That fixed it instantly, brilliant catch, thank you!
A tiny addition, for anyone else who might face this later:
Using round on just %seconds gave me an incorrect time later on, in conversions (one minute too early). I fixed this by using round on (%seconds-(%minsBefore*60)), which fixed it.

The other, probably better, solution would be to go into the AutoAlarm settings and set the decimal point setting to 0. The round() was my quick way to get to that [emoji56]
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers

ktmom said:
The other, probably better, solution would be to go into the AutoAlarm settings and set the decimal point setting to 0. The round() was my quick way to get to that [emoji56]
"Good judgment comes from experience, and a lot of that comes from bad judgment." - Will Rogers
Click to expand...
Click to collapse
That indeed also fixes it. I did notice the extra numbers, making for milliseconds, I should've realized that such a setting would be available in this kind of app, lol. Thanks again!

Tasker terms

First, let’s come to terms with… the terms in Tasker. Many of the concepts may be new to you, and they can be a bit overwhelming, especially to neophytes. But, once you get the logic of Tasker, you’ll find that it’s a truly powerful app even for programming noobs.
Action — The basic element of Tasker. It refers to phone or tablet functions and features that perform something or brings the phone to a certain state. Tasker supports more than 200 actions grouped into 21 categories.
Task — A group of actions. Usually linked to a trigger or “context”, but can also be a free-floating, standalone task executed manually. A task can be run within another task. You can clone, export, import, and lock tasks.
Context — Situations or conditions which, when true, trigger the execution of the task(s) associated with it.
Profile — Some sort of “container” or “package” for context(s) and linked task(s). You can define several contexts for a single profile, and all those conditions must be true for the linked tasks to run.
Variable — A name for an unknown value that can change over time, like the battery level or the date.
Scene — A custom-made user interface. You can create your own layout of buttons, menus, popups, and other UI elements.
Project — A group of profiles, tasks, scenes, and variables. Each project has its own tab (at the bottom of the main screen) with a user-defined project name. You can also export or import projects. You can even export a custom project as a standalone Android app (APK): just tap on the project name/icon, select Export, and choose “As App.”

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