EXCEL VB - assign a value to a cell 1

[ottograham]

In VB, how would you assign a value to a cell? I created a PctChange function which puts the result into the cell where I've entered the function name, but what if I want to also change cell D7 to "Over Quota" within the function. How do I reference an individual cell in VB and assign a value to it?

thank you.

 

[Dreamboat]

I think it's Range("D7&quot.Value = 22 Anne Troy

http://www.TheOfficeExperts.com

 

[lode]

In VB ??

Cells(x,y)= whatever !!

 

[ottograham]

Dear Dreamboat and Lode:

I'm playing with it and it doesn't work. Here is what I've got:

Function PctChange(newvalue, oldvalue) As String
Dim x As Single

x = (newvalue - oldvalue) / oldvalue

Rem Worksheets("2002 SA vs Zurich&quot.Cells(2, 4).Font.Size = 20
Rem Cells(3, 3) = "dsfasdfjlksadfkasldfjsa"
PctChange = Format(x, "###,###%&quot
End Function

I don't see any changes in either of the cells (assume the REMARKS are removed).

Thanks

 

[ottograham]

Also, how do I make my utility functions available automatically to any WS?

 

[lode]

Hi,

Maybe this is a stupid question but do you know the meaning of REM.
The code after REM won't be executed !!

Lode.

 

[lode]

I'm sorry i didn't see (assume the REMARKS are removed).

 

[xlbo]

A function (user defined or otherwise), entered on a worksheet, cannot affect any cell other than the one it is entered in so you won't be able to do your "over quota" bit within the function

Function PctChange(newvalue, oldvalue) As String
Dim x As Single
x = (newvalue - oldvalue) / oldvalue
PctChange = Format(x, "###,###%&quot
End Function

should work fine for your pctchange tho why you'd write a UDF to do this I don't know Rgds
~Geoff~

 

[ottograham]

Dear Geoff:

I'm playing around and I saw an article in PC Magazine. I use this formula all the time, and sometimes when I copy a worksheet to another worksheet the formulas don't come (I've learned now to paste special).

Perhaps what I am thinking of is a Subprocedure. In Access and VB you can do just about anything you want. Is this the same in Excel.

How about answering the question of the VB syntax to set a value to a particular cell, and could I make a set of utility functions global to all my worksheets.

I'm curious, what types of functions have you written? Maybe I'm not seeing the real potential here.

Thank you

 

[xlbo]

The real potential for UDFs (User Defined Functions) is to do more powerful / strange calculations than your normal functions can
For example - here is a function that counts the occurances of any character (or characters) within a text string:

Function Howmany(what As String, where As Range)
testStr = where.Text
ctr = 0
nctsf = Len(what)
For i = 1 To Len(testStr)
If Mid(testStr, i, nctsf) = what Then
ctr = ctr + 1
Else
End If
Next i
Howmany = ctr
End Function

The only reason I asked about your particular function is that, because you ask for the variables (ie new and old nos.), it's pretty much as quick to physically write the formula in excel as it is to enter the function

A Sub CAN change pretty much anything in excel (to answer the 1st question, the syntax for putting a value in a cell can vary but goes along the lines of
Range("A1&quot.value = 22 (as Dreamboat said) or cells(1,1).value = 22 or [A1]=22)

A function can incorporate a lot of the same VBA that a sub can but it cannot alter the environment around it (when used in a cell) - only the cell it is in. You can however, also use the Functions within subs - esp useful if you have a calc that needs to be repeated often

In terms of utility functions for all your worksheets - absolutely possible - I have a set of subs and functions that I do that with - you need to save them in "PERSONAL.xls"
To create a "Personal.xls", create a new workbook and save it in your XLSTART directory as "Personal.xls"
This will open up as a "veryhidden" workbook whenever you open excel. Any code or functions that are saved on m,odules attached to that workbook will be available to any of your worksheets (but not other users if they have to use your worksheets)

Hope all this is of some help Rgds
~Geoff~

 

[ottograham]

Yes, it was helpful. I'll try the personal.xls recommendation.

I appreciate you taking the time to help.

Best Regards,

Scott