setting Excel cell.value through Visual BASIC

[geniet]

Hello all of you,

I have some problem in setting cells values (or fromat or...) in my Excel spreadsheet using a VB statement like within my Module:
Range("C1").Value = 20

I have this in a function. If I am in debug mode and have a breakpoint at this line, I can see when hovering my mouse over "Range("C1").Value" the actual value in that cell (in my case 30), but when I want to step to the next statement, the VB programs ends (due to some error?).
The cell value is not changed to 20.

Can someone help me? I tried several other methods to change cells, like Range(AtmosProfile).Cells(1, 3).Value = 2 * 5
but also this does not work...

Do I need to declare in my Visual BASIC module that I allow the setting of cells in the spreadsheet (I don't have protection on in my worksheet).

I am using Microsoft Visual BASIC 6.0 within Microsoft Excel 2000

Thanks for the help.

All the best,


Victor

[fplesco]

Hello all of you,

I have some problem in setting cells values (or fromat or...) in my Excel spreadsheet using a VB statement like within my Module:
Range("C1").Value = 20

I have this in a function. If I am in debug mode and have a breakpoint at this line, I can see when hovering my mouse over "Range("C1").Value" the actual value in that cell (in my case 30), but when I want to step to the next statement, the VB programs ends (due to some error?).
The cell value is not changed to 20.

Can someone help me? I tried several other methods to change cells, like Range(AtmosProfile).Cells(1, 3).Value = 2 * 5
but also this does not work...

Do I need to declare in my Visual BASIC module that I allow the setting of cells in the spreadsheet (I don't have protection on in my worksheet).

I am using Microsoft Visual BASIC 6.0 within Microsoft Excel 2000

Thanks for the help.

All the best,


Victor

hi -

Maybe you can omit the keyword VALUE. See below

Range(AtmosProfile).Cells(1, 3) = 2 * 5
 

[geniet]

Maybe you can omit the keyword VALUE. See below

Range(AtmosProfile).Cells(1, 3) = 2 * 5
 

Does not work for me... Sorry. I hope I do something else wrong...

All the best,


Victor

[kadghar]

spreadsheet1.cells(1,1).value = "hello"

that method should work.

[geniet]

spreadsheet1.cells(1,1).value = "hello"

that method should work.

Same effect. My VB program is aborted. No errors mentioned, etc.; so don't know why and what goes wrong... Almost sounds like I need to re install excel.

Still desperate for a solution...

All the best,

Victor

[kadghar]

Same effect. My VB program is aborted. No errors mentioned, etc.; so don't know why and what goes wrong... Almost sounds like I need to re install excel.

Still desperate for a solution...

All the best,

Victor

Do you have an excel application in an object??? in that case its not necesary to specify the worksheet, lets say its in Obj1, just write

obj1.cells(1,1).value ="hello"

and it'll take the "active" worksheet, and try making the obj1.visible=true while debugging your program, even if later you wont need it to show

[geniet]

Do you have an excel application in an object???

Don't understand that. I just use Excel. Have a module (called Module1) where I have a function that should set a cell.

Public Function test()
Range("C1").Value = "hello"
End Function

Nothing gets in cell C1 and the test() results in #VALUE!

All the best,


Victor

[kadghar]

Don't understand that. I just use Excel. Have a module (called Module1) where I have a function that should set a cell.

Public Function test()
Range("C1").Value = "hello"
End Function

Nothing gets in cell C1 and the test() results in #VALUE!

All the best,


Victor

Sorry about that, its for working with another excel application.
In this case why dont you try with a procedure, something like this in your module:

Sub test2()
cells(1,3).value = "hello"
end sub

just put the cursor on any part of this code and press F5.

[geniet]

Sub test2()
cells(1,3).value = "hello"
end sub

I had to change it to a function (instead of sub) otherwise I can't use it in a Excel cell.

Public Function test2()
Cells(1, 3).Value = "hello"
End Function

But still same results. No 'Hello' in Cell(1,3) and error message #VALUE! in the cell with the '=test2()' function.

All the best,


Victor

[SammyB]

In your original post, you said that you were using VB6. You are not really, are you? The rest of your posts sound like you are just creating macros. Do you have your Security settings set to Medium? (Tools, Macros, Security...) Can you record a macro that sets 20 into cell C1? Can you run that macro? -Sam

[QVeen72]

Hi,

Did u open the Excel Object properly..? Post the Code here.. Also Check if the XL file what u r trying to Edit is "Read Only".
Post the Code of opening the XL Appl and Worksheet here..
May be u have opend it properly in a Sub Procedure declaring the variables Locally and when u r writing it in function, the Exl Obj / Range is not identifeid..

REgards
Veena

[geniet]

In your original post, you said that you were using VB6. You are not really, are you? The rest of your posts sound like you are just creating macros.

I am making user defined functions under the 'Tools' -> Macro -> 'Visual Basic editor' and that VB editor is version 6. So perhaps I was given wrong infromation...
The use defined functions are in Modules -> Module1

Do you have your Security settings set to Medium? (Tools, Macros, Security...)

I have it on Low.

Can you record a macro that sets 20 into cell C1? Can you run that macro?

I can record a macro indeed. It sets a cell active and then put the value in the active cell.

Sub testing2()
'
' testing2 Macro
' Macro recorded 05/09/2007 by vreijs
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "23"
End Sub

If I put the:

    Range("C1").Select
    ActiveCell.FormulaR1C1 = "23"
 

in a function (a sub does not work as a user defined function),

Public Function test2()
Range("C1").Select
ActiveCell.FormulaR1C1 = "23"
End Function
 

it also does not work, even the Range("C1").Select does not work... I have the cell C1 not protected.
If I do a function and call then the sub, it also does not work:

Public Function test2()
Call test3
End Function
 
Public Sub test3()
Range("C1").Select
ActiveCell.FormulaR1C1 = "23"
End Sub

I must do something obvius wrong, but what. Sorry again if I gave wrong info, it seems I am not used to this yet (although making user defined functison I done many times, but never tried to change cell values).

All the best,


Victor

[kadghar]

I must do something obvius wrong, but what..

Is your PC pluged to electricity?

=P

Try writing in a Module this:

Public Function mytest() as integer
    mytest=23
end function

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, write this on the same module:

sub othertest()
    cells(1,1).value = mytest
end sub

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.

[SammyB]

>I am making user defined functions under the 'Tools' -> Macro ->
OK, great, you are using VBA, Visual Basic for Applications

>I have it [security] on Low.
Dangerous! This makes it pretty easy for malicious Excel code to run. Medium is better.

>I can record a macro indeed. It sets a cell active and then put the value in the active cell.
Great!

>[But] in a function ... [neither line works].

Public Function test2()
Range("C1").Select
ActiveCell.FormulaR1C1 = "23"
End Function
 

That is correct, you cannot do this in a Function. A Function can only change the value of the cell in which it was entered.

Describe what you want to accomplish and we can tell you ways to do it. HTH --Sam

[geniet]

Describe what you want to accomplish and we can tell you ways to do it.

I want to put a calculated value into a certain cell of my worksheet. Of cause the value is calculated using some otherfunction, but in essence it is this simple;-)

So "put in cell C1 the value 23" and this within a function made in Tools macro VisualBasic Editor.

Hope you can help.

All the best,

Victor

[geniet]

Public Function mytest() as integer
    mytest=23
end function

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.

sub othertest()
    cells(1,1).value = mytest
end sub

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.Cells(1,"A").Value = "new value"

e.g. ActiveSheet.Cells(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!ThfromT(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 misinterpreating your e-mail!!! That indeed works!!! THANKS! I will work further on my procedure. Great this progress!
Thanks again.

All the best,


Victor