[Dropped] Macro help on setvalue

[kerrylord]

Hi all,

I'm trying to add the amount in Categories.B24 to the amount in Budget.B3 using setvalue() that gets run inside a macro. Presently I"m getting an error stating 'Object variable not set'

setValue(Categories.B24+Budget.B3)

[Zizi64]

setValue(Categories.B24+Budget.B3)

Please upload all lines of your macro code. This one line is not enough for this task. You need get the Document, the Sheet, the source and target Cell/s/ first by API functions. Then you will able to set a value of a cell.

Do you want to create a custom Cellfunction or a Subroutine?

[kerrylord]

REM ***** BASIC *****

Sub Main

setValue(Categories.B24+Budget.B3)


End Sub

[FJCC]

Macros don't work like that. From what you have posted, setValue() doesn't exist as a function, you have to define it, and Categories.B24 & Budget.B3 don't have any meaning within the code. I know those sheets and cells exist in your spreadsheet, but the code doesn't know that. What do you want to do with the sum of the two cells?

[JohnV]

Here is an example.

Sub Main
oDoc=ThisComponent
oSheet=oDoc.Sheets(0) 'Sheet1
oCell=oSheet.getcellrangebyname("C1")
A=oSheet.getcellrangebyname("A1")
B=oSheet.getcellrangebyname("B1")
oCell.setValue(A.value+B.value)
End Sub

[Zizi64]

setValue(Categories.B24+Budget.B3)
...

Code: Select all

oSheet=oDoc.Sheets(0) 'Sheet1

Or you can get the Sheets by name:

Code: Select all

Sub Main
 Dim oDoc as object
 Dim oSheets as object
 Dim oSheet_Cat as object
 Dim oSheet_Bud as object
 Dim oCell_A as object
 Dim oCell_B as object
 
 Dim Value_A as double
 Dim Value_B as double

     oDoc = ThisComponent
     oSheets = oDoc.Sheets
     oSheet_Cat = oSheets.getByName("Categories")
     oSheet_Bud = oSheets.getByName("Budget")
     oCell_A = oSheet_Cat.getcellrangebyname("B24")
     oCell_B = oSheet_Bud.getcellrangebyname("B3")
     Value_A = oCell_A.value
     Value_B = oCell_B.value
'...
' you can summarize the value_A and value_B, but I not understood it:
'-------------- Where you want to put the result??? --------------
    oCell_somewhere.value = Value_A + Value_B
...
End sub

[Villeroy]

In other words: You have to write program. A program is not a magic spell.

[kerrylord]

Thanks so much for trying to help me out!
I suspected that I didn't have a good understanding of the macro scripting.

Here's what I'm trying to do. I have a budget.

I have three sub-sheets within my spreadsheet.
1-Budget - How much each week needs to go into each Category
Category Weekly Amount
Mortgage $90
Internet $7.25
Cell Phone $20
etc..

2-More Info - account numbers, addresses, notes about bills, etc.

3-Categories - What is the current total in each Category
Categories Amount
Mortgage $180
Internet $14.50
Cell Phone $40
etc.

So I am trying to create a Button in the Category SubSheet that would do the following:
1-Are you sure you want to add a week's paycheck to each category?
Allow Yes or No response to user
2-If YES button is clicked then do the following or else stop
3-Start with Categories.B2 which is the current total for Mortgage of $180 and add the amount from Budget.B2 of $90 to the $180 thus replacing Categories.B2 with the new sum of $270. Then move on to the next category item amount at Categories.B3 and add Budget.B3 to it, etc.

Do you see what I'm trying to do?
Thanks again!

[Zizi64]

Please, please UPLOAD your example .ods file here...