What is the code used to run all three macros in the below macro code (in Basic)?

[karolus]

@Madmaxneo

Now all I need to do is make it easier to read…lol.

Does this mean, youre going to break it again?

[Madmaxneo]

@karolus

Does this mean, youre going to break it again?

Haha, no. I have learned enough that if I do move something that isn’t tied directly to a named range I can go in and change the references. I’ve had to do this before.
Right now I am changing merged cells and aligning things up along with changing the color coding so it’s more balanced and doesn’t look so chaotic.

[karolus]

@Madmaxneo

Right now I am changing merged cells and aligning things up along with changing the color coding so it’s more balanced and doesn’t look so chaotic.

Now I’m pretty sure… Madmax will break it …

[Madmaxneo]

Not yet it hasn’t broken. Just not exactly sure what colors to use to make it not look so chaotic or bland…lol

[Madmaxneo]

@karolus

Now I’m pretty sure… Madmax will break it …

This was working just fine and it was working great. But, during an update in the last month of LibreOffice or possibly Windows all my macros got deleted. I have been trying to find out what happened but they seem to be completely gone from my PC. So I am attempting to rebuild them using what was on this forum.
So far I got the first one to work (which you’re macro above references in the line “TalList”) but I now get the error: “BASIC Runtime error. Variable not defined” at this line in from the code you sent me above: doc = thisComponent.

I need help getting this working again.

This is exactly how it is written in the macro:

REM ***** BASIC *****
Option Explicit

sub copy_and_clear
doc = thisComponent
source = doc.NamedRanges.getByName(“TalList”).ReferredCells
target = doc.NamedRanges.getByName(“TalOwn”).ReferredCells
empties = target.queryEmptyCells().getByIndex(0)
r = 0
for each cell in source.queryContentCells(15).Cells
empties.getCellByPosition(0, r).Formula = cell.Formula
r = r+1
next
source.clearContents(15)
end sub

[Wanderer]

@Madmaxneo

“BASIC Runtime error. Variable not defined”

The quick way: Remove option expicit
.
Better define (DIM) your variables.
.
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03103300.html

Edit: The other “part” of your problem seems to be in this thread:

Where did all my macros go?

Windows 11 Pro (Build 26100) Libre Office Version: 24.8.4.2 (X86_64) EDIT: I found the toolbar. I also Googled where the macros are stored and I looked in the user folder for a folder called My Macros and a search found nothing. I still do not want to believe they are gone, there has to be somewhere they are located other than in that area. I don’t understand how they could have gotten deleted anyway. Today, I go into to work on some sheets that I have quite a few useful macros in them and a …

[Madmaxneo]

@Wanderer

@Madmaxneo

“BASIC Runtime error. Variable not defined”

The quick way: Remove option expicit
.
Better define (DIM) your variables.
.
Option Explicit Statement

Edit: The other “part” of your problem seems to be in this thread:

Where did all my macros go?

I did try defining my variables (at least as per the other script that is working) but then I kept getting errors in other lines. That code above worked pretty much like it is set up already, so not even sure why it’s not working now.
I will re-add them again later and post the script with the error I am getting.

[Madmaxneo]

@Wanderer

The quick way: Remove option expicit
.
Better define (DIM) your variables.
.
Option Explicit Statement

Edit: The other “part” of your problem seems to be in this thread:

Where did all my macros go?

This is what I did:

REM  *****  BASIC  *****
Option Explicit

sub copy_and_clear
 Dim Doc as object
 Dim Source as object
 Dim Target as object
 Dim empties as object
 Dim r as long
	doc = thisComponent
	source = doc.NamedRanges.getByName("TalList").ReferredCells
	target =  doc.NamedRanges.getByName("TalOwn").ReferredCells
	empties = target.queryEmptyCells().getByIndex(0)
	r = 0
	for each cell in  source.queryContentCells(15).Cells
		empties.getCellByPosition(0, r).Formula = cell.Formula
		r = r+1
	next
	source.clearContents(15)
end sub

But now I am getting the same error of “BASIC Runtime error. Variable not defined”
at this line for each cell in source.queryContentCells(15).Cells

FYI, if you don’t know I am not a coder and I only mess with this sheet every so often and not enough to remember things and how they work. Like I said before this was working just fine until all my macros were deleted by an update. This time I am saving them in NPP+.

[robleyd]

Well, you haven’t defined the variable cell so you get an undefined variable error.

[Madmaxneo]

What variable is not defined? Source and target are defined so I don’t know what else needs to be defined in this.
This macro is supposed to take the data in the list of cells defined by TalList (source) and put them into a set of cells as defined by TalOwn (target), then clear out the TalList. There are no other cells to define.

[Madmaxneo]

Oh wow, nevermind.
I forgot to take out the Option Explicit at the beginning of this one. It works as intended.

Now on to the next script.

[robleyd]

@Madmaxneo

What variable is not defined?

@robleyd

Well, you haven’t defined the variable cell so you get an undefined variable error.

cell is a variable as you have used it, but it isn’t declared. Hence the error.

[Madmaxneo]

See above.
I forgot to take out the “Option Explicit”. Once I took that out it works fine and there is no error.

[Zizi64]

@Madmaxneo

I forgot to take out the “Option Explicit”.

The Option Explicit is a good “debugger tool”. Not a good idea to take out it. Just define the variable CELL.

[Madmaxneo]

It is working fine now.
I wouldn’t have any idea how to define the variable CELL as you mention.

[robleyd]

Perhaps my use of ‘define’ is misleading; the documentation refers to ‘declares’. In any case, it is probably useful to have at least a minimal knowledge of Basic if you are going to work with it; somewhat like at least knowing where to put the oil and water in your car.

[Wanderer]

@Madmaxneo

I wouldn’t have any idea how to define the variable CELL

Look at the source. Every Variable (places to store something) has a DIM-statement, giving its type (wich is needed to know, how much space to reserve). So the complicated part for you is to know wich type you need.
As you have a for each cell in source. you use the same type object as for the other “complex” things and come to an additional line below the other DIM-statements.

Dim cell as object

Especially somebody “messing around” should use option explicit because it can protect from typos like celll
.

@Madmaxneo

my macros were deleted by an update. This time I am saving them in NPP+.

Better: Learn to use a proper backup of your system, wich includes %APPDATA% wich is anbimportant folder in Windows. It will hold also bookmarks in Firefox, all downloaded emails in a regular setup for Thunderbird.
.
Manual copies of items of wich you know, because you already lost them once is a poor strategy for backups. It will fit in messing around…
.
PS: If you check for the safe-mode assistant you may find option to create a copy of your LibreOffice-profile, wich would include you macro-library (with exeption of macros stored in your files.)

[Madmaxneo]

FYI, I do have a regular backup of the user folder but whatever update in Libre Office that did this deleted it far enough back that the next backup replaced the Libre Office appdata with files after it had been deleted.
I think the last time I looked at the file was a month prior to realizing all my macros disappeared. Even then there’s a chance the macros were deleted before that because I do not always use the macros when I am working on the sheet.

[Madmaxneo]

@Wanderer

Look at the source. Every Variable (places to store something) has a DIM-statement, giving its type (wich is needed to know, how much space to reserve). So the complicated part for you is to know wich type you need.
As you have a for each cell in source. you use the same type object as for the other “complex” things and come to an additional line below the other DIM-statements.

Dim cell as object

PS: If you check for the safe-mode assistant you may find option to create a copy of your LibreOffice-profile, wich would include you macro-library (with exeption of macros stored in your files.)

Adding Dim cell as object is a little odd because before the macros got deleted by an update it worked fine without ever defining the “Cell” in this error.

Safe-mode assistant? I will look into that.

[Wanderer]

@Madmaxneo

is a little odd because before the macros got deleted by an update it worked fine

So you really can remember every line without understanding them? My guess is: If your memory is flawless you never had option explicit in your old code…
.
But with that kind of memory you should be able to restore your work without need of an backup. Speakingbof your backup:

@Madmaxneo

regular backup of the user folder but whatever update in Libre Office that did this deleted it far enough back that the next backup replaced the Libre Office appdata with files after it had been deleted.

So I’d say you have one copy of the folder somewhere. (Done manual?) Even on old standards this is no backup: If you computer dies when you do the copy you may loose/compromise both. Minimum would be two external copies wich are swapped as destnation for backup.
.
But in times, where hard-drives are cheap: Use a software with versioned backup. It will keep older versions of files. And (with exception of photo/video) nobody will fill a Terabyte-disk with self written data… There is plenty of space. (Photographers and video should be done separate.)