Let's get programmy!

[GinRei]

Oh good, I could use this thread.  I need to figure out some Excel macro code but not sure how to do it, so hopefully someone can assist (I'm more used to java (though even that's been about 8 years since I used it), and excel seems to be some weird combination of visual basic and C from what I can tell).

I want to do the following:
1) Hit a button
a) From said button, run multiple lines of formula to get various numbers and such (assuming the formula stuff is the same as normal cell formulae, this won't be hard)
b) Input the calculated value into another cell (this is the tricky part), as I want whatever it calculates to be a permanent value that won't get erased by closing the excel/restarting the computer/switching computers.  This needs to be able to be updated every time the button is pressed.
c) Show/hide applicable rows.

Thanks~

[Akonyl]

dunno about the other people who visit the thread, but I don't know the first thing about excel macros (and don't have excel to fiddle with anyway), so can't help you much there 

but after doing a quick googling for point b), it looks like you should just be able to do something like Cell(RowIndex, ColumnIndex).Value = "Something", so you could just replace Something with the value you got from your formula?

[Kogorou]

@GinRei,

Oh my, I haven't used excel in years. If you can wait a little bit I'll do some research and see what I can do.
The first problem is: I don't use excel, if anything I could see what libre office can do.

[GinRei]

dunno about the other people who visit the thread, but I don't know the first thing about excel macros (and don't have excel to fiddle with anyway), so can't help you much there 

but after doing a quick googling for point b), it looks like you should just be able to do something like Cell(RowIndex, ColumnIndex).Value = "Something", so you could just replace Something with the value you got from your formula?

Sounds like it'd probably work.  I'll fiddle around with it.  I've never used excel macros either, so still need to learn how to do that.  Oh how I wish I could just use Java code.

[Kogorou]

That macro stuff sure is annoying, plus it's a security matter.
If you only look for a macro inserting stuff into cells use what Akonyl said
To append :

Code: Select all

Cell(RowIndex, ColumnIndex).Value = "Something" & Cell(RowIndex, ColumnIndex).Value

[Kirbypepe]

Well assuming that you are doing the calculation in like a table like scenario where the very top row is the headers, and the rest of the data is organized accordingly you could potentially:

Code: Select all

Sub SomeSortofCalculations (inSheet As Worksheet)
    ' variables for table size
    Dim inRow As Integer
    Dim headerRow As Integer
    Dim firstRow As Integer
    Dim lastRow As Integer
    Dim lastColumn As Integer

    'Additional variables as where the info would be gotten from
    Dim inACol As Integer
    Dim inBCol As Integer
    Dim inCCol As Integer
    Dim inDCol As Integer
    Dim inECol As Integer

    ' variables for output AKA where you would place them
    Dim outRow As Integer
    Dim outACol As Integer
    Dim outBCol As Integer
    
    ' misc variables used to manipulate the calculation hold calculation values
    Dim aX As Double
    Dim bX As Double
    Dim cX As Double
    Dim dX As Double
    Dim eX As Double
    
    Dim carry As Double
    Dim total As Double
    
    ' initialize input
    headerRow = GetRow(inSheet, "Header1")
    firstRow = headerRow + 1
    lastRow = inSheet.cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    lastColumn = inSheet.cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

    inACol = GetColumn(inSheet, "A")
    inBCol = GetColumn(inSheet, "B")
    inCCol = GetColumn(inSheet, "C")
    inDCol = GetColumn(inSheet, "D")
    inECol = GetColumn(inSheet, "E")

    ' initialize output location
    outACol = SetColumnName(inSheet, headerRow, lastColumn + 1, "OUTPUT HEADER1")
    outBCol = SetColumnName(inSheet, headerRow, lastColumn + 2, "OUTPUT HEADER2")

    For inRow = firstRow To lastRow

        aX = cells(inRow, inACol)
        bX = cells(inRow, inBCol)
        cX = cells(inRow, inCCol)
        dX = cells(inRow, inDCol)
        eX = cells(inRow, inECol)
   
        'this method is another one that was created by me (Just an example of how to call another method)
        carry = GetCarry(aX, dX, eX, bX, cX)
        
        aX = cells(inRow, inACol)
        bX = cells(inRow, inBCol)
        cX = cells(inRow, inCCol)
        dX = cells(inRow, inDCol)
        eX = cells(inRow, inECol)
    
        'this method is another one that was created by me (Just an example of how to call another method)
        total = GetTotal(aX, dX, eX, bX, cX)
        
        'Saving your calculations into the same sheet under their own column
        inSheet.cells(inRow, outACol) = carry
        inSheet.cells(inRow, outBCol) = total

    Next
    
End Sub

I this something that you were thinking??? if not then........ whoops for going somewhat complex XD XD

[Kogorou]

There really is someone who knows that stuff

[GinRei]

I'll have to look that over when I get a bit more time, but yeah the data will be in a table.  As of now, there's no set range for the table (using formulae already to get the starting position of the table plus key columns), as it'll basically be able to take whatever sort of type its given and figure out what it needs automatically.

[Kirbypepe]

Well this gets the first data row, the header row, the last column, and the last row in order to determine the size of the table dynamically. It creates integers for the output columns so that you can place them next to the table (or wherever all you need to do is increase or move the offset that is hardcoded in). It reads from key columns, using the column headers, stores them within variables that you are creating, in this case all doubles. You could do the calculation there where I'm calling the other functions and then store them the way I did at the end. I think that's what you wanted???

[GinRei]

That does sound pretty all-encompassing to what I want.  Thank you very much!  Hopefully I get enough spare time soon to give it a try and learn more macro usage.

[Giogio]

Just allow me one question: why?

Guess I was a bit misunderstood here. The whole thing isn't about obtaining some AI able to operate my imaginary spaceship, it's about the AI's itself.
I want to give them a relatively free space of possibilities to evolve and see if they develop any of the behaviours we see on animals. Would they learn to cooperate? Hunt? Communicate? Fight? Would they need sleep? These things.
Why? Just because I think it would be freaking awesome  :P

I need different layers of data for each individual:
* environment stuff     (environment, input+output (external properties) of the individual, energy level)
* genes                       (encoding starting topology of neural net)
* learning state           (weights of neuron connections, excitation thresholds)
* thinking state           (as usual; influences learning state)

The main problem at this point would be how to encode the starting point and possible development of each neural net into the genes.
So that small mutations have small effects, and there are as many different neural nets encodeable as possible. Just straight-forward? Is there already any elegant approach? Can you allow recombination of genes?
(Edit: Lisp maybe? Looks promising Except for the recombination part, I guess)

However, I have to admit my only real question here would be
[img width=400 height=300]http://www.kinderundjugendmedien.de/ima ... kater1.jpg[/img]
Anyone wanna play with me?

[Akonyl]

The whole thing isn't about obtaining some AI able to operate my imaginary spaceship

I fail to see the point, then.

(Edit: Lisp maybe?

I just want to tell you that this is a horrible idea because I hate LISP and if I could go back in time I'd shoot the guy who made it.

but really it's an alright language but for how weird it is and all the parentheses that fly around everywhere in it, I don't get why it's supposed to be so amazing for AI and why you shouldn't just use whatever language you like most.

anyway, the main thing is that all of this seems rather lofty, an AI spontaneously learning to communicate/fight/sleep without pre-implanted notions of these things hasn't even been done in commercial AI I don't think, because to do that, you would need to have some greater understanding of perception and learning than I think we as a species even have. The majority of genetic algorithms/neural nets don't ever learn to do something different, they just learn how to reach their goals better (I only say majority because I could be wrong, but I've yet to see one that isn't this way).

Although you're obviously not looking for "simple", it'd probably be a lot more feasible to just start out with a genetic algorithm approach without the neural nets, and then once you've proven that you can do that to the level that you desire, you can replace the decision-making portions of the AI with neural net approaches and then add more genes in for those.

As for the encoding of net to gene, I would imagine you could just have a few gene pairs corresponding to each neuron that specify the weight of an axon and its destination neuron, maybe with a restriction that it can't mutate too far (so the input neurons don't end up boringly connecting themselves to the output neuron directly or something )

[Kogorou]

Okay that long answer alone is not what I expected.
I thought you would say : "Because I can"

But okay,
AI is one of the most complicated fields and is still under heavy research.
I wrote a very stupid AI for a game once; that took long enough (about three months).
As long as you are confident you can pull it off go for it.
There are a lot of research papers out there,  just google them; and have fun reading

[Giogio]

Ironically, that's the most motivating thing you could possibly say to me. I'm on it

(I'm glad that those papers are written way more fluently than the chemistry stuff I'm used to)

[Kogorou]

http://www.youtube.com/watch?v=RthZgszykLs

With this I rest my case


Yeah I read those documents a few years back and it was pure madness

Does anyone happen to have a deeper insight of the GCC?