VBA - passing Variables to subroutines

[Madduck]

HI I'm having a problem passing variable values around ..

I've done this

Option Explicit
Public Gemloc As Integer
Public Gemnumloc As Integer

Private Sub Gemwon2_Click()

Sheets("Rift_raid").Visible = True
Gemloc = 13 'Column M
Gemnumloc = 4 ' Row 4

Gemlist.Show

End Sub

Gemlist is a form that has a list box where you can select an item and on
submit uses this code

Sub SubmitButton1_Click()
Sheets("Rift_raid").Select

Gemid ' calls a subroutine in the form that gives an item a value.

Cells(Gemloc, Gemnumloc).Value = Gemname.Value

Range("o1") = ""
Gemname.Value = ""
Gemname.Clear
Gemlist.Hide

End Sub

What I want it to do is to place the Item name in Cell (Gemloc, Gemnumloc)
and the item value in the next cell.. but for some reason both Gemloc and
Gemnumloc are not getting passed from the Sheet to the form's subrountine..

Does this make sence?

Thanks

 

[FSt1]

hi
in gemwon2_click, you have.......

Gemloc = 13 'Column M
Gemnumloc = 4 ' Row 4

in submitbutton_click,you have......

Cells(Gemloc, Gemnumloc).Value = Gemname.Value

this is backwards. if gemloc is the column and gemnumloc is the row it
should be
cell(gemnumloc,gemloc).value = gemname.value
so the value is goint to d13 not m4.
what is gemname. are you sure gemid is returning a value. i can't test that.

regards
FSt1

 

[Dave Peterson]

Move those Public declarations to a General module.

And just because...

Change the names to something meaningful and declare them as long's.

Option Explicit
Public GemRowLoc as long
Public GemColLoc as long

I would think it would make your code a bit easier to understand. And since
integers can only go up to 32k (about), there's never a reason to use them in
any dim statement.

 

[Madduck]

Thanks Guys,

I have changed the varible names as suggested...
This script is far from complete, as I'm learning as I go....

as such I have a few "test outputs"

from the form "Gemlist" I can output Gemname to a cell.

What I can't seem to do is pass the value back to the main code...

I did not want to paste the whole code , and clog up the forums, but here is
the entire code behind the form "gemlist"

Sub SubmitButton1_Click()
Sheets("Rift_raid").Select

Gemid

Cells(GemRowloc, GemColloc).Value = Gemname.Value
Range("o1") = ""
Gemname.Value = ""
Gemname.Clear
Gemlist.Hide

End Sub
Sub Gemid()


If Gemname.Value = "Boots" Then
Gemvalue = 1
ElseIf Gemname.Value = "Gloves" Then
Gemvalue = 2
ElseIf Gemname.Value = "Leggings" Then
Gemvalue = 3
ElseIf Gemname.Value = "Chest" Then
Gemvalue = 4
ElseIf Gemname.Value = "Helm" Then
Gemvalue = 5
Else
Gemvalue = 6
End If
Range("o1") = Gemvalue ' for transporting the value out of the subroutine

End Sub

Private Sub userform_Activate()
Gemname.AddItem ("Boots")
Gemname.AddItem ("Gloves")
Gemname.AddItem ("Leggins")
Gemname.AddItem ("Chest")
Gemname.AddItem ("Helm")
Gemname.AddItem ("Shoulders")
Gemname.AddItem ("Weapon")
End Sub

 

[Madduck]

Then from there this is the code in the main section

Private Sub Gemwon2_Click()

Sheets("Rift_raid").Visible = True
GemRowloc = 13 'Column M
GemColloc = 4 ' Row 4

Gemlist.Show

End Sub


So what I want to happen is :

I click a control button located in a cell next to a name of a user.
that then brings up a form where I select the "gem"
the script then returns then selceted Gem name and its underlying value in
the cells next to the username.

THe user name list is 30 long, therefore I'm trying to do this in
subroutines .... ( i.e. I don't really want to replicate 30 forms that do the
same thing )

Thanks heaps as usual , you guys rock.

 

[Madduck]

Ohh... and the error I get is Object not defined in this row

Cells(GemRowloc, GemColloc).Value = Gemname.Value

both GemRowloc and GemColloc are empty, so the code can't reconcile it....

 

[Dave Peterson]

How/where did you declare the GemValue variable?

Did you put it in that same general module along with GemRowLoc and GemColLoc?

Option Explicit
Public GemRowLoc As Long
Public GemColLoc As Long
Dim GemValue As Long

 

[Dave Peterson]

Double check to make sure you've put those public variables in a General module.

They don't go behind a worksheet or behind ThisWorkbook or behind either
userform.

 

[Madduck]

Hi Dave,

thanks for your help on this....

yes this is the definitions so far..

Option Explicit
Public strName As String
Public strLogic As String
Public IntNameset As Long
Public strPaste As String
Public Gemvalue As Long
Public Raidnumber As String
Public GemRowloc As Long
Public GemColloc As Long

I am sorry for being a noob, but when you say a General module... I'm not
sure what you mean, if they don't go behind any of the sheets or the
workbook, how does one set up a general module? that will follow the file if
I was to send it home etc...

 

[Madduck]

Scrap that...

I think I get it .. you mean in the "modules" folder as opposed to
"Microsoft Excel Objects" or "Forms"

Thanks I'll move my option explicit...

 

[Madduck]

It worked...

its all about the location of my explicit

Thanks heaps for the help...

 

[Dave Peterson]

Select your workbook's project in the VBE.
Then click on Insert (on the menu bar), then Module (plain old module).

Check here:
http://www.contextures.com/xlVideos04.html#CopyCode
http://www.contextures.com/xlvba01.html#Regular

Move (not copy!) the declarations into that module.

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Dave Peterson]

It's not really the "option explicit" that's causing the trouble. It's the
location of the Public declarations.

That line is a directive to VBA that you want it to yell at you if you don't
declare any of the variables you use in that module.

It's a very good idea to have "Option Explicit" at the top of each and every
module--the userform modules, the worksheet modules, the ThisWorkbook module,
and the plain old general modules.