OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

CALC: refresh DatabaseRanges

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Sun Mar 25, 2012 12:23 pm    Post subject: CALC: refresh DatabaseRanges Reply with quote

Hello I have a macro that has a index, but I get the error message that says "Inadmissible value or data type. Index out of defined range."

I checked my index and for the life of me I can't figure out what is wrong. Can you look at my macro and see what it's talking about?

Code:
REM  *****  BASIC  *****

Sub Main

Dim oDoc As Object
Dim oSheet As Object
Dim oCell As Object
Dim oCellCount As Object
Dim oCellCountCont As Object
Dim NPlayers as Integer
Dim Pause as Boolean
oDoc=ThisComponent
oSheet=oDoc.Sheets.getByName("TABLETOP")
oCell=oSheet.getCellRangeByName("D1")
oCellCount=oSheet.getCellRangeByName("C4")
oCellCountCont=oSheet.getCellRangeByName("A1")
NPlayers = oSheet.getCellRangeByName("C2").Value
ulit:
oCell.Value = 90
Do
If oSheet.GetCellRangeByName("A4").Value Then Exit Sub
ThisComponent.DatabaseRanges("E6:H25").refresh()
Pause = oSheet.GetCellRangeByName("B4").value
oCell.Value = oCell.Value + CInt(Not Pause)
Wait 1000
Loop While oCell.Value > 0
oCellCountCont.Value = oCellCountCont.Value + 1/NPlayers
oCellCount.Value = oCellCount.Value + 1
If oCellCount.Value = NPlayers + 1 then oCellCount.Value = 1


 
goto ulit
End Sub

Sub Reset
With ThisComponent.Sheets.GetByName("TABLETOP")
.GetCellRangeByName("A3").String = ""
.GetCellRangeByName("C3").String = ""
.GetCellRangeByName("E3").String = ""
End With
End Sub


This section right here is what it points to.

ThisComponent.DatabaseRanges("E6:H25").refresh()

thanks for looking.



Moderation probe1: moved to MACROS AND API section, where all macro related questions belong to; edited subject
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Mar 25, 2012 7:07 pm    Post subject: Reply with quote

Must admit I'm perplexed by the behaviour of ThisComponent.DatabaseRanges.
You had very similar code in the doc you previously emailed me, yet it did not cause any errors.
As far as I can tell Calc uses a zero based index number in DatabaseRanges property's parentheses, analogues to ThisComponent.Sheets(0) which refers to the document's first sheet.
Since this line from you emailed doc...
Code:
ThisComponent.DatabaseRanges("B7:E26").refresh()
did not result in an error I thought that calc had evaluated "B7:E26" to be zero and so referred to the document's first database range, as does
Code:
ThisComponent.DatabaseRanges(0)
. My thinking could be wrong though.

When you set up the database range you gave it a name.
Try replacing...
Code:
ThisComponent.DatabaseRanges("E6:H25").refresh()
with
Code:
ThisComponent.DatabaseRanges.GetByName("xxxxx").Refresh()
where xxxxx is the name you used for that database range.
Also, you might consider taking that line out of the Do Loop. Is it really necessary that it be refreshed that often, since I suspect that you would be making changes to the database range only between runs of the Main sub.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Mon Mar 26, 2012 6:44 am    Post subject: Reply with quote

Thanks for the suggestions KJ, I didn't give the full info in my OP because I was unaware of all the issues I was causing with my sheet.

I am in the process of making some changes to the layout of my spreadsheet and removing some things that are not needed any longer. As well as changing locations and make certain cells larger so as to be read easier when projected.

As such I decided to just create a new spreadsheet rather than adjust the current one. My thinking was that If I messed up the new one and couldn't get it working I would at least have the old one to fall back on.

I however have totally messed up both sheets because I am trying to use the same macros on both sheets and I believe that is the cause of my macro issues.

I am totally lost and frustrated by this because I had a lot of time and energy put into my first sheet and don't really want to redo it from scratch. Is there a way to fix this?

I know I'm not really giving a lot of useful info here but I'm not sure what you would need to help me. Please let me know what I can do and what info you might need.

Thank you so much for your input, you've been such a great help to me.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Mar 26, 2012 2:10 pm    Post subject: Reply with quote

Sithlyone wrote:
I however have totally messed up both sheets because I am trying to use the same macros on both sheets and I believe that is the cause of my macro issues.

I am totally lost and frustrated by this because I had a lot of time and energy put into my first sheet and don't really want to redo it from scratch. Is there a way to fix this?

If you need to get back to scratch perhaps you could access, via you gmail account, the document [TABLETOP (COPY).ods] that you emailed me Mar 13.
After that perhaps you could post any problems you are having either here or my email.

Ken Johnson
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Tue Mar 27, 2012 12:26 pm    Post subject: Reply with quote

Thanks KJ, after my meltdown I remembered that I did make a copy when I sent that to you. Just curious, did all the functions of the spreadsheet work for you when I sent it via email the second time? I sent it to a friend of mine and he couldn't get the timer nor the rollers to work. I'm just wondering if I attached the macros correctly.

Also I guess my concerns now are how do I use a single macro for multiple spreadsheets?
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue Mar 27, 2012 9:23 pm    Post subject: Reply with quote

Sithlyone wrote:
...did all the functions of the spreadsheet work for you when I sent it via email the second time? I sent it to a friend of mine and he couldn't get the timer nor the rollers to work. I'm just wondering if I attached the macros correctly.
From memory I had to make a few changes to get it to work...
1. reassign the Main macro from the timer1 module to the Start button. An error indicated the macro could not be found.
2. delete the autosort module (xl Worksheet Change code) because it resulted in an error when the Start button was clicked.

I have emailed you the resulting doc (TABLETOP (COPY) orig2.ods)

Sithlyone wrote:
Also I guess my concerns now are how do I use a single macro for multiple spreadsheets?
More info needed here. Also, I think you might be confusing "sheets" and "spreadsheets". To me, spreadsheets are spreadsheet documents and they contain sheets. I'm guessing you want the same macro to be used by different sheets in the same spreadsheet document.

Ken Johnson
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Wed Mar 28, 2012 4:27 am    Post subject: Reply with quote

I know what i was saying sounded like i was confused but i was really talking about two different spreadsheets. When i created the original document i didn't understand the difference between saving a macro to the document or saving it to my main macro area so i just created then all in my main area.

Even now that since i sent to you with macros attached to the document only accepts changes when i make them to the my main macros. I feel this may be where my problem is. Both documents are trying to use the same macro and it is confusing the system.

Let me know if this doesn't make sense and/or if you have a solution. Thanks so much.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Wed Mar 28, 2012 7:08 am    Post subject: Reply with quote

If the code you are running is the timer then you won't be able to run both documents at the same time, even when each document has its own copy of the code.

If the only code changes that take affect are changes to the 'My Macros' code then reassign the document's code to the button you click when you start the code.

Ken Johnson
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Wed Mar 28, 2012 11:26 am    Post subject: Reply with quote

wow, that is so simple! I feel like a moron.... Embarassed

Thanks so much, I appreciate your continued patience and help.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
Sithlyone
Power User
Power User


Joined: 13 Jan 2012
Posts: 98

PostPosted: Sat Mar 31, 2012 12:39 pm    Post subject: Reply with quote

Ok I created a new spreadsheet and copied the old macros and attached them to the new spreadsheet (document) and created the new area for the index and on the macro I changed the cells it is supposed to index and I get the same error message from above.

I searched everything and for some reason I always get the same error message where it tells me that my area is undefined or something like that. It isn't even connected to the old sheet any longer yet it still gives me the same error.

I mean it's like I am creating a totally new spreadsheet with a different macro (I changed the name and everything) I also made sure that I attached the "new" macro to the start button.

Any suggestions would be great.
_________________
I'm just a Newb trying to learn. Please don't assume I know what I am doing. Thanks for the help!
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Apr 02, 2012 2:58 am    Post subject: Reply with quote

Could you email me a copy of the problem spreadsheet?
I really need to see exactly what is happening.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group