Jump to content
  • Advertisement
Sign in to follow this  
Servant of the Lord

[VBA] Excel visual basic question

This topic is 2436 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have two questions having to do with Excel VBA.

My first one is with a line like this:
Worksheets("Main page").Range("$B5")

How do I specify a a Range() of a single cell, with the column fixed (column B) but the row relative to the current row passed in to the function? ([color="#2e8b57"]Private Sub Worksheet_Change(ByVal Target As Range))

Another question is, how do I specify a single cell? Why isn't there a 'Cell' variable? Do I always have to specify a single-cell as a type 'Range'?

And my final question is, how do I do a 'reference' or 'pointer' to a variable?

For example, instead of this:
Worksheets("Main page").Range("$B5").font.Color = fontColor
Worksheets("Main page").Range("$B5").font.Size = fontSizeWorksheets("Main page").Range("$B5").font.Bold = True


I'd rather do this:
Dim myCell As <reference to> Range
myCell = Worksheets("Main page").Range("$B5")

myCell.font.Color = fontColor
myCell.font.Size = fontSize
myCell.font.Bold = True



Or, can I do this?
Dim myCell As Range
myCell = Worksheets("Main page").Range("$B5")

myCell.font.Color = fontColor
myCell.font.Size = fontSize
myCell.font.Bold = True

Worksheets("Main page").Range("$B5") = myCell



I'm completely new to Visual Basic. An hour or two of googling and frustration left me with naught. I'll pickup a VBA book sometime soon, but for now I can't get (what seems to me) to be an absurdly simple script to work.

Share this post


Link to post
Share on other sites
Advertisement
You can use the Cells property of a Worksheet or Range to specify a particular cell. Ex: MyWorksheet.Cells(5, 3) will give you cell C5 of MyWorksheet. You can use the Row property of a given Range to get its row. AFAIK there's only the Range type, no Cell type, and I can't tell you why. If you want to set a bunch of properties of a given object all at once you can use a With block. Ex:

With Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!