Excel VBA Global Range Object
2

Question

Excel VBA Global Range Object

JoelTXCive (Civil/Environmental)

(OP)

30 Jul 17 19:51

I am having difficulty with range objects and global variables.

My overall goal is to have excel store a “FtInch_Hotcell” which is the most recent cell that a sub-routine has been run on. If I call a specific macro, I want to look at my current activecell location and compare it to the cell residing in the “FtInch_Hotcell.” If the cells are the same, then I want to do procedure A with the cell; and if the cells are different, then I want to do procedure B.

Here is what I have so far:

Upon opening a workbook I am declaring a global range object. I have one line of code in a separate module for global items.

'declare the HotCell as variable available to all subroutines
Public FtInch_HotCell As Range

I then assign a single cell to this range object. To start with, I just put a random cell in the FtInch_HotCell range. This occurs in the ‘workbook open’ module.

Set FtInch_HotCell = ActiveSheet.Range("a999")

In a new procedure, how do I compare the current active cell to the cell residing in ‘FtInch_HotCell’
I have tried several different combinations of logical arguments but cannot seem to get it to work. I’m not even sure I am accessing the global variable correctly.

Thank you.

Ps. Really big picture what I am doing is converting decimal feet to feet and inches. When you run the procedure the first time, the output is in 1/64 inches. The second run yields 1/32 inches and then 1/16... etc.. (if i can ever get it to work)

SkipVought (Aerospace) · Answer 1 · 2017-07-30 21:46:11

Hi,

"current active cell"

CODE

If ActiveCell.Value = FtInch_HotCell.Value Then
   'Do this if the values are equal
Else
   'Do this if they are not equal
End If

I personally use lots of Named Ranges in my sheets, so I'd name that A999 range FtInch_HC (one time).
Then the only reference and code you'd need is...

CODE

If Active.Cell.Value = [FtInch_HC].Value Then
'.....

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

JoelTXCive (Civil/Environmental) · Answer 2 · 2017-07-31 02:57:08

It worked! I had to change the property from FtInch_Hotcell.Value to *.ADDRESS, but it worked! The object variables seem to be finicky.

Thank you! Here's the working code:

Sub FtAndInch()

MsgBox "FtInch_Hotcell: " & FtInch_HotCell.Address

'Collect current active cell and compare to global hotcell.
'We will reset the index counter to 1 if it is a new cell.

If ActiveCell.Address <> FtInch_HotCell.Address Then
Set FtInch_HotCell = ActiveCell
SwitchIndex = 1
End If

Call StatusCheck

End Sub

IDS (Civil/Environmental) · Answer 3 · 2017-07-31 03:26:13

Just a suggestion: since the only property of the range that you are interested in is the address, why not just store the address (as a string), rather than creating a range object?

That will make the code simpler, quicker, and less like to suffer from unintended outcomes.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

JoelTXCive (Civil/Environmental) · Answer 4 · 2017-07-31 04:46:34

I'm open to using a string variable versus the range variable. I'm just learning as a programmer and was worried the string value might mess up further operations in the code.

Overall, I am going to pick up the value in the "Hotcell" and then convert it to ft and inches at a user selected level of precison (64ths, 32nds, 16ths, etc). The output will be put in a cell offset from the original cell since it will be a string.

For example, I might pick up the value 36.802 ft. out of cell A1. The macro will convert the 36.802 into 36'-9 5/8" and place the results in cell B1.

The offsetting of the results is what made me think I needed to use the range objects. I have a userform where the user selects relative cell offsets and desired outputs.

Here's the userform where the relative offset values are entered. If I use a string for the cell location will I still be able to offset?

IDS (Civil/Environmental) · Answer 5 · 2017-07-31 12:23:55

OK, if you are reading and writing from/to that cell (or an offset) it makes sense to create a range object. You could use the address string inside Range().Offset().Value, but it's probably easier to do it as you have done.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

SkipVought (Aerospace) · Answer 6 · 2017-07-31 15:15:23

???

Quote:
To start with, I just put a random cell in the FtInch_HotCell range

"Random"? You have coded A999 with intention. How is that random? I must be missing something.

You seem to be wanting to be testing for the intersection of your hot cell and the Active Cell. This could be accomplished using sheet events in the code window of the Sheet Object.

Right-click your sheet tab and select View Code.

Directly above of the code window...
...and to the left is the Object drop down. Select Worksheet.
...and to the right is the Procedure Drop Down. You will see in this drop down, a list of events like Change, that will fire when a change of value is made in the worksheet.

For instance, if you were to select SelectionChange, you would see the code stub...

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

This code will run each time you change your selection on this sheet. In your particular instance...

CODE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(FtInch_HotKey, Target) Is Nothing Then
        'do this if there's an intersection
    Else
        'do this if there's NO intersection
    End If
End Sub

New vistas of coding.......

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

JoelTXCive (Civil/Environmental) · Answer 7 · 2017-08-01 14:30:06

SkipVought - Random was a poor choice of words by me. I should have said I wanted to initiate the range variable with an arbitrary cell address of A999. I just wanted to put a cell address in the FtInch_Hotcell range so that comparisons could be made. The cell address in that range variable gets updated every time the program runs.

Thank you all for your help. I have the whole framework of my program up and running well now. I am still working on some specific sub-procedures, but hopefully I can get them worked out this week. If I get stumped for more than a day, then I'll post another question.

JoelTXCive (Civil/Environmental) · Answer 8 · 2017-08-02 02:56:58

I'm stuck again. Maybe I am not cut out to be a programmer and should stick with the engineering.

I cannot figure out how to get VBA to place a formula into a desired cell. Unfortunately, my formula has a single quote and a double quote in it, which confuses things. As soon as Excel sees the single quote, it wants to 'comment out' the remainder of the line.

I have unsuccessfully tried several methods including attempting to build a string and concatenate all together. Any input or suggestions would be appreciated.

Here is the code in text form:

Sample Code on the spreadsheet:
=INT(B3) & "' - " & TEXT(12*MOD(MROUND(B3,1/(32*12)),1),"# #/###") &""""

Current non working version in VBA:
FtInch_HotCell.Offset(Offset_Y, Offset_X).Formula = _
"=INT(B3) & chr(39) & "" - "" & TEXT(12*MOD(MROUND(B3,1/(32*12)),1),""# #/###"") &"""""

SkipVought (Aerospace) · Answer 9 · 2017-08-02 03:55:43

CODE

'
    Dim ref As String
    
    ref = "B3"
    
    FtInch_HotCell.Offset(Offset_Y, Offset_X).Formula = _
"=INT(" & ref & ") & char(39) & "" - "" & TEXT(12*MOD(MROUND(" & ref & ",1/(32*12)),1),""# #/###"") &"""""

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

JoelTXCive (Civil/Environmental) · Answer 10 · 2017-08-02 05:34:00

SkipVought! You are a genius!

Thank you so much. I would have messed around with that syntax for days.

Do you have any need for a spreadsheet that converts decimal feet to feet & inches at 1/8 , 1/16th, etc. accuracy? Once I finish the spreadsheet I can upload it here.

SkipVought (Aerospace) · Answer 11 · 2017-08-02 17:04:15

If you're agreeable, it would be instructive.

Glad you got things working.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

JoelTXCive (Civil/Environmental) · Answer 12 · 2017-08-02 18:05:53

Okay, I'll upload the file on here when I finish. It will probably take me a few more evenings to get it all up and running with the code cleaned up and commented well.

JoelTXCive (Civil/Environmental) · Answer 13 · 2017-08-04 16:02:18

Here is the Beta version of the spreadsheet. Everything is up and running.

I wrote a macro that converts decimal feet into feet and inches:

You input 25.613 and the program outputs 25’ – 3 13/16”

Enter a decimal foot in a cell and then hit CTRL-SHIFT-S

Running the program for a 2nd, 3rd, etc.. time will cycle through accuracy levels.

The control panel for the program can be reached with a right click.

http://files.engineering.com/getfile.aspx?folder=43afa60e-4333-4aeb-9c86-c0

IDS (Civil/Environmental) · Answer 14 · 2017-08-05 00:08:56

Thanks Joel, very nice.

The detailed notes on each routine are also very helpful. I recommend the download to anyone interested in VBA programming, even if not interested in feet and inches.

Have you seen my units spreadsheet (copy attached)? It's a bit of a different approach, but you might find some of the routines useful, especially if you want to do conversions outside the very limited built-in Convert function range.

One other thing - you know there is a specific VBA forum? I expect almost everyone who visits there also looks here, but there may be a few people who don't who are missing out.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

http://files.engineering.com/getfile.aspx?folder=fb88abc4-3276-4e8b-8063-f2

JoelTXCive (Civil/Environmental) · Answer 15 · 2017-08-09 02:52:44

IDS -

Thank you for the compliments!
Man, those are some awesome spreadsheets you have! That is some cool stuff for sure!

Come Join Us!

Posting Guidelines

Excel VBA Global Range Object
2