setting Excel cell.value through Visual BASIC

[geniet]

[CODE=vb]Public Function mytest() as integer
mytest=23
end function[/CODE]
then go to your excel worksheet and write on any cell:
=mytest()

this should return 23 in that cell. and check with some basic example
if it didnt work

Indeed this works, I use that method for all my user defined functions. Just to be sure, I don't want to have the resulting value in the cell where the function is defined, I want a particular cell in my spreadsheet to get the value 23.

[CODE=vb]sub othertest()
cells(1,1).valu e = mytest
end sub[/CODE]

This does not work as an user defined functions, subroutines are not allowed, only functions. If I change the Sub in Function, it is stillnot working.

and this time run it pressing F8 and press F8 each time to debug it step by step. this way you'll see where the error comes.

I put breakpoints in the Visual BASIC editor. No errors seen, accept the cell which called the function gets the value #VALUE!
As stated earlier, if I have the breakpoint on the statement that should set the cell, and hover over the

Range("C1").Value

part of the statement, I see the value which is in the spreadsheet already (so a read works), it only does not write the value in the cell...

All the best,


Victor

[SammyB]

You can only set a value in another cell with a SUB procedure. However, you can make this sub a response to a worksheet event, such as entering data into a particular cell. However, this sort of thing is done more effeciently with Excel's IF function. You still haven't said what you want to do.

[geniet]

You can only set a value in another cell with a SUB procedure.

I tried to use a sub called from a user defined function, but that does not work. See one of my earlier e-mails above.

However, you can make this sub a response to a worksheet event, such as entering data into a particular cell.

That is not what I want, I think. See below, I only want things put in these cells once... (say as initialization of a spreadsheet table/range)

You still haven't said what you want to do.

Oke the gorry details (it is related to refraction calculations using a height-temperature profile: http://www.iol.ie/~geniet/eng/tropospherelayers.htm ). I have a height-temperature table in Excel, from this height-temperature profile I want to calculate the air pressure based on the heigth and temperature. The air pressure should be in the third column of this table. I don't want to use a user defined functions in the third column of the table, but I just want to be able to calculate these pressure values using a user defined function on this height-temperature table (a range, which can be provided by a user) the uder defined function thus need to put pressure values the cells of this thrid column.
The three columned table is needed for other user defined functions which work on this three column table.
In some way, I want to use the Excel table as a global space to put values in (and caluate it only ONCE) and these values can be used then again and again by different user defined functions.

So in essence I just want to put a value in a cell from a user defined function (make in VBA).

I see for instance Excel Solver Add-In putting values in cells (and Solver is also a functions or perhaps it is not a user defined function;-)

All the best,


Victor

[nick0]

You can address the Column directly using

ActiveSheet.Cel ls(1,"A").Value = "new value"

e.g. ActiveSheet.Cel ls(rownum,"A"). Value = price_of_apples

This seems to getround the cells problem

[SammyB]

...
I have a height-temperature table in Excel, from this height-temperature profile I want to calculate the air pressure based on the heigth and temperature. The air pressure should be in the third column of this table. I don't want to use a user defined functions in the third column of the table, but I just want to be able to calculate these pressure values using a user defined function on this height-temperature table (a range, which can be provided by a user) the uder defined function thus need to put pressure values the cells of this thrid column.
The three columned table is needed for other user defined functions which work on this three column table....

You want to develop an Addin, but first you should get a simple worksheet working. For now, I would put a button on the worksheet that pops up a Userform which gets the info needed to generate a profile. I've gotten you started in the attached. It's a zip file because I also added a sample height-temperature data file. HTH --Sam
PS -- obviously my pressure formula is wrong: I've forgotten more than I knew about atmospherics.

[geniet]

You want to develope an Addin, but first you should get a simple worksheet working. For now, I would put a button on the worksheet that pops up a Userform which gets the info needed to generate a profile.

This is really helping Sam. I think I can use this method of yours for my work (I also never used froms, etc., so this also is great to learn!).

Thanks.

All the best,

Victor

[SammyB]

This is really helping Sam. I think I can use this method of yours for my work (I also never used froms, etc., so this also is great to learn!).

Thanks.

All the best,

Victor

I thought you had gone on holiday! So, do you understand what I did? I can walk through it in more detail if you want.

[geniet]

I thought you had gone on holiday! So, do you understand what I did? I can walk through it in more detail if you want.

I was indeed, but also I was working on getting the theory behind my refraction program better (getting the temp/height profile;-). Sorry.
I am now able to start to incorporate your ideas. So it might be I have a question;-)
I saw when doing multiple times your buttom click, I get an additonal row...
But I will now implement something myself first (using your idea).

THANKS.

All the best,


Victor

[geniet]

Hello Sammy,

I am able to make a sub that starts when pressing a button in the worksheet.

Sub Button2_Click()
    sunlow = Range("sunlow")
    viscous = Range("Viscous")
    Timehour = Range("Timehour")
    ABLday = Range("ABLday")
    entrainperc = Range("CBLentrainperc")
    ABLdeltanight = Range("ABLdeltanight")
    ABLdeltaday = Range("ABLdeltaday")
    NBLnight = Range("NBL")
    cappingperc = Range("NBLcappingperc")
    surfaceperc = Range("CBLsurfaceperc")
    Th0 = Range("Th0")
    Press0 = Range("Press0")
'    Th0 = GeoAstro.xla!ThfromT(T0, Press0)
    Range("A1") = "Height"
    Range("B1") = "Temperature"
    Range("C1") = "Pressure"
     For i = 0 To 40
        Cells(i + 2, 1) = i * 30
        Temp = ABLprofile(profiletype, heighttype, Timehour, sunlow, viscous, ABLday, entrainperc, ABLdeltanight, ABLdeltaday, NBLnight, cappingperc, surfaceperc, i * 30, Th0)
        Cells(i + 2, 2) = Press0
        Cells(i + 2, 3) = Temp
    Next i
End Sub

But I still have some scope/reach problem. I have most of my arthicmatic in an geoastro.XLA file (my Add-in file).
And I would thus like to call these functions from my button-sub in the worksheet.
So if I comment out the line:

    Th0 = GeoAstro.xla!ThfromT(T0, Press0)

I get an error "Run-time error '424'"

So how can I use my functions defined in my geoastro.xla? (if I move my functions from the xla to the same Module as the Button2_Click is; it works...)

Hope you can help.

All the best,


Victor

[SammyB]

Th0 = GeoAstro.xla!Th fromT(T0, Press0)

I've not done this, but I think it works :oD>>

In the VBA, use the Tools > References menu to set a reference to your xla. If you have restarted Excel after installing the addin, you will see GeoAstro in the list. Place a checkmark before it and press OK. Now, you should be able to use your functions directly:
Th0 = ThfromT(T0, Press0)

Hope this works, I don't want to create an xla to see what can go wrong! --Sam

[geniet]

In the VBA, use the Tools > References menu to set a reference to your xla. If you have restarted Excel after installing the addin, you will see GeoAstro in the list. Place a checkmark before it and press OK.

I have my Add-in already a few years, so that is done;-) I can refer to XLA (user defined) functions in the cells of my spreadsheet. VB module function-calls (part of the spreadsheet) in the button sub work oke. I am not able to do XLA function call from button sub or modules part of the spreadsheet.
It looks to be about visibility of xla function-calls from button subs or modules (as said; cells with xla function calls work).

Hope this is clear...
Hope someone can help.

All the best,


Victor

[SammyB]

"In the VBA, use the Tools > References menu to set a reference to your xla."

Have you done this?

[geniet]

"In the VBA, use the Tools > References menu to set a reference to your xla." Have you done this?

Oopps, no I did not know that!!!! Sorry, I was misinterpreatin g your e-mail!!! That indeed works!!! THANKS! I will work further on my procedure. Great this progress!
Thanks again.

All the best,


Victor