By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

setting Excel cell.value through Visual BASIC

P: 16
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
Aug 27 '07 #1
Share this Question
Share on Google+
27 Replies


P: 82
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
Expand|Select|Wrap|Line Numbers
  1. Range(AtmosProfile).Cells(1, 3) = 2 * 5
  2.  
Aug 28 '07 #2

P: 16
Maybe you can omit the keyword VALUE. See below
Expand|Select|Wrap|Line Numbers
  1. Range(AtmosProfile).Cells(1, 3) = 2 * 5
  2.  
Does not work for me... Sorry. I hope I do something else wrong...

All the best,


Victor
Aug 28 '07 #3

kadghar
Expert 100+
P: 1,295
spreadsheet1.cells(1,1).value = "hello"

that method should work.
Aug 28 '07 #4

P: 16
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
Aug 29 '07 #5

kadghar
Expert 100+
P: 1,295
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
Aug 29 '07 #6

P: 16
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
Sep 4 '07 #7

kadghar
Expert 100+
P: 1,295
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.
Sep 4 '07 #8

P: 16
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function test2()
  2. Cells(1, 3).Value = "hello"
  3. 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
Sep 5 '07 #9

SammyB
Expert 100+
P: 807
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
Sep 5 '07 #10

QVeen72
Expert 100+
P: 1,445
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
Sep 5 '07 #11

P: 16
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.
Expand|Select|Wrap|Line Numbers
  1. Sub testing2()
  2. '
  3. ' testing2 Macro
  4. ' Macro recorded 05/09/2007 by vreijs
  5. '
  6. ' Keyboard Shortcut: Ctrl+Shift+Y
  7. '
  8.     Range("C1").Select
  9.     ActiveCell.FormulaR1C1 = "23"
  10. End Sub
If I put the:
Expand|Select|Wrap|Line Numbers
  1.     Range("C1").Select
  2.     ActiveCell.FormulaR1C1 = "23"
  3.  
in a function (a sub does not work as a user defined function),
Expand|Select|Wrap|Line Numbers
  1. Public Function test2()
  2. Range("C1").Select
  3. ActiveCell.FormulaR1C1 = "23"
  4. End Function
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function test2()
  2. Call test3
  3. End Function
  4.  
  5. Public Sub test3()
  6. Range("C1").Select
  7. ActiveCell.FormulaR1C1 = "23"
  8. 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
Sep 5 '07 #12

kadghar
Expert 100+
P: 1,295
I must do something obvius wrong, but what..
Is your PC pluged to electricity?

=P

Try writing in a Module this:

Expand|Select|Wrap|Line Numbers
  1. Public Function mytest() as integer
  2.     mytest=23
  3. 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:

Expand|Select|Wrap|Line Numbers
  1. sub othertest()
  2.     cells(1,1).value = mytest
  3. 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.
Sep 5 '07 #13

SammyB
Expert 100+
P: 807
>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].
Expand|Select|Wrap|Line Numbers
  1. Public Function test2()
  2. Range("C1").Select
  3. ActiveCell.FormulaR1C1 = "23"
  4. End Function
  5.  
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
Sep 5 '07 #14

P: 16
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
Sep 5 '07 #15

P: 16
Expand|Select|Wrap|Line Numbers
  1. Public Function mytest() as integer
  2.     mytest=23
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. sub othertest()
  2.     cells(1,1).value = mytest
  3. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 5 '07 #16

SammyB
Expert 100+
P: 807
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.
Sep 5 '07 #17

P: 16
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
Sep 6 '07 #18

P: 1
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
Sep 23 '07 #19

SammyB
Expert 100+
P: 807
...
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.
Attached Files
File Type: zip Refraction.zip (12.7 KB, 770 views)
Sep 24 '07 #20

P: 16
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
Oct 4 '07 #21

SammyB
Expert 100+
P: 807
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.
Oct 4 '07 #22

P: 16
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
Oct 13 '07 #23

P: 16
Hello Sammy,

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

Expand|Select|Wrap|Line Numbers
  1. Sub Button2_Click()
  2.     sunlow = Range("sunlow")
  3.     viscous = Range("Viscous")
  4.     Timehour = Range("Timehour")
  5.     ABLday = Range("ABLday")
  6.     entrainperc = Range("CBLentrainperc")
  7.     ABLdeltanight = Range("ABLdeltanight")
  8.     ABLdeltaday = Range("ABLdeltaday")
  9.     NBLnight = Range("NBL")
  10.     cappingperc = Range("NBLcappingperc")
  11.     surfaceperc = Range("CBLsurfaceperc")
  12.     Th0 = Range("Th0")
  13.     Press0 = Range("Press0")
  14. '    Th0 = GeoAstro.xla!ThfromT(T0, Press0)
  15.     Range("A1") = "Height"
  16.     Range("B1") = "Temperature"
  17.     Range("C1") = "Pressure"
  18.      For i = 0 To 40
  19.         Cells(i + 2, 1) = i * 30
  20.         Temp = ABLprofile(profiletype, heighttype, Timehour, sunlow, viscous, ABLday, entrainperc, ABLdeltanight, ABLdeltaday, NBLnight, cappingperc, surfaceperc, i * 30, Th0)
  21.         Cells(i + 2, 2) = Press0
  22.         Cells(i + 2, 3) = Temp
  23.     Next i
  24. End Sub
  25.  
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:
Expand|Select|Wrap|Line Numbers
  1.     Th0 = GeoAstro.xla!ThfromT(T0, Press0)
  2.  
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
Oct 29 '07 #24

SammyB
Expert 100+
P: 807
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
Oct 29 '07 #25

P: 16
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
Oct 30 '07 #26

SammyB
Expert 100+
P: 807
"In the VBA, use the Tools > References menu to set a reference to your xla."

Have you done this?
Oct 30 '07 #27

P: 16
"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
Oct 31 '07 #28

Post your reply

Sign in to post your reply or Sign up for a free account.