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").Val ue = 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").Va lue" 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(AtmosProf ile).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").Val ue = 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").Va lue" 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(AtmosProf ile).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.ce lls(1,1).value = "hello"

that method should work.

[geniet]

spreadsheet1.ce lls(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=tr ue 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").Val ue = "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").Val ue = "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).valu e = "hello"
end sub

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

[geniet]

Sub test2()
cells(1,3).valu e = "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").Sel ect 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:

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

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

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