VBA variables and offset

Question

Im having trouble takign my assigned variable and offseting it. What am I doing wrong?

Public Sub SampleBox_Change() 
    Dim str As Integer 
    If (SampleBox.ListIndex > -1) Then 
        str = SampleBox.List(SampleBox.ListIndex) 
    End If 
End Sub 

Public Sub Samplesdel(str As Integer) 
    Range(Range("BA1").EntireColumn, Range("BA1").Offset(0, -str).EntireColumn).Select 
End Sub 

Public Sub CommandButton1_Click()
    Application.Run "Samplesdel" 
End Sub

So the variable (str) is a whole number. I would like to use this number to select a certain number of columns (from BA1 to "left however many columns valued as str"). so if the user selects 8 i would like to select BA1 and left 8 columns.

The combobox is in a userform where the code for the assigned variable is set.

I would like to use the assigned variable in a macro (where i used the select function).

so the variable str gets assigned from a userform combobox. I would then like to pass this variable to a macro where i use it in the offset function.

What do you want to do? Your code has several issues and it would help if you would try to explain in words what you are trying to do. — Dirk Vollmar
– Dirk Vollmar, Commented Jul 16, 2010 at 15:24

Michael · Accepted Answer · 2010-07-19 19:35:22Z

1

I just used your snippets of code here, I'll assume you know what you're trying to accomplish with this. As mentioned in another answer, I think it's an issue of scope; it doesn't seem like there's a good reason you can't combine your statements as follows:

Public Sub SampleBox_Change() 
        Dim str As Integer 
        If (SampleBox.ListIndex > -1) Then 
            Range(Range("BA1").EntireColumn, Range("BA1").Offset(0, SampleBox.ListIndex).EntireColumn).Select
        End If 
    End Sub

To add some unsolicited feedback, naming a variable that is an integer "str" will be very confusing to anyone else that has to read your code, the name "str" implies "string", a different data type.

answered Jul 19, 2010 at 19:35

Michael

1,7082 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

buckbova · Accepted Answer · 2010-07-16 15:16:55Z

0

James, you are setting the value of str here correct?

Public Sub SampleBox_Change() 
    Dim str As Integer 
    If (SampleBox.ListIndex > -1) Then 
        str = SampleBox.List(SampleBox.ListIndex) 
    End If 
End Sub

The scope for str will only be this function and it will not be able to be accessed by any other function.

What value are you passing to Samplesdel as the parameter?

Application.Run "Samplesdel"

answered Jul 16, 2010 at 15:16

buckbova

1,2336 silver badges11 bronze badges

Collectives™ on Stack Overflow

VBA variables and offset

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related