Continue to Site

Welcome to our site!

Electro Tech is an online community (with over 170,000 members) who enjoy talking about and building electronic circuits, projects and gadgets. To participate you need to register. Registration is free. Click here to register now.

Register Log in
  • Welcome to our site! Electro Tech is an online community (with over 170,000 members) who enjoy talking about and building electronic circuits, projects and gadgets. To participate you need to register. Registration is free. Click here to register now.

VBE variables initialized at the start

Status
Not open for further replies.
atferrari

atferrari

Well-Known Member
Most Helpful Member
In the lack of a more suitable forum, I am posting this here.

Visual Basic for Excel

I declare my variables: Dim ver_1, ver_2, ver_3 as Variant

I need them initialized to a certain value as soon as I open my application, most probably in Page 1 (but Page 2 could be eventually needed instead).

Stuck on this for the last day, I cannot see how to do it.

Could anyone show the actual code needed?

Gracias for that.
 
In older versions of excel a sub called auto_open would be run at start up.

Mike.
 
Initialized as Public but invisible to the rest

Brisbane members in sync. :p Thanks to you both for replying.:D

I am aware of what you pointed me at. My problem is that defining "verde" as Public in WorkBook still keeps it invisible to code in Hoja1 (Sheet1 for you).

Trying to find why, I even came to define the Sub Coloreta, twice, which I know is not correct but it allowed to know that "verde" was being ignored by the rest of the code.

I am in my first steps so bear with me. To be honest I cannot understand where every piece of code should go. Couldn't grasp the basic to know when to put code in Sheet1, WorkBook or a module. Nice mambo!

I accept that everything could be dead wrong here.

Gracias for any help.


Code: 
    Option Explicit    
    Public verde As Variant


Private Sub Workbook_Open()
    Let verde = 135
    Call Coloreta
End Sub

Private Sub Coloreta()
    Range("D15") = verde
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, verde, 0)
        .BackColor.RGB = RGB(0, 135, 0)
        .TwoColorGradient msoGradientFromCenter, 1
        .Transparency = 0#
    End With
End Sub

Code: 
    Option Explicit
    Public verde As Variant
    
Private Sub SpinButton_G_SpinUp()
    If verde < 255 Then
        verde = verde + 60
    End If
    Worksheets("Hoja1").Cells(5, 3).Value = verde - 2
    Worksheets("Hoja1").Cells(5, 7).Value = "Spinup"
    Call Coloreta1
End Sub

Private Sub SpinButton_G_SpinDown()
    If verde > 15 Then
        verde = verde - 60
    End If
    Worksheets("Hoja1").Cells(5, 3).Value = verde + 2
    Worksheets("Hoja1").Cells(5, 7).Value = "Spindown"
    Call Coloreta1
End Sub

Private Sub Coloreta()
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, verde, 0)
        .BackColor.RGB = RGB(0, 135, 0)
        .TwoColorGradient msoGradientFromCenter, 1
        .Transparency = 0#
    End With
End Sub
 
Last edited:
atferrari said:
Brisbane members in sync. :p Thanks to you both for replying.:D

I am aware of what you pointed me at. My problem is that defining "verde" as Public in WorkBook still keeps it invisible to code in Hoja1 (Sheet1 for you).

Trying to find why, I even came to define the Sub Coloreta, twice, which I know is not correct but it allowed to know that "verde" was being ignored by the rest of the code.

I am in my first steps so bear with me. To be honest I cannot understand where every piece of code should go. Couldn't grasp the basic to know when to put code in Sheet1, WorkBook or a module. Nice mambo!

I accept that everything could be dead wrong here.

Gracias for any help.


Code: 
    Option Explicit    
    Public verde As Variant


Private Sub Workbook_Open()
    Let verde = 135
    Call Coloreta
End Sub

Private Sub Coloreta()
    Range("D15") = verde
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, verde, 0)
        .BackColor.RGB = RGB(0, 135, 0)
        .TwoColorGradient msoGradientFromCenter, 1
        .Transparency = 0#
    End With
End Sub

Code: 
    Option Explicit
    Public verde As Variant
    
Private Sub SpinButton_G_SpinUp()
    If verde < 255 Then
        verde = verde + 60
    End If
    Worksheets("Hoja1").Cells(5, 3).Value = verde - 2
    Worksheets("Hoja1").Cells(5, 7).Value = "Spinup"
    Call Coloreta1
End Sub

Private Sub SpinButton_G_SpinDown()
    If verde > 15 Then
        verde = verde - 60
    End If
    Worksheets("Hoja1").Cells(5, 3).Value = verde + 2
    Worksheets("Hoja1").Cells(5, 7).Value = "Spindown"
    Call Coloreta1
End Sub

Private Sub Coloreta()
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, verde, 0)
        .BackColor.RGB = RGB(0, 135, 0)
        .TwoColorGradient msoGradientFromCenter, 1
        .Transparency = 0#
    End With
End Sub
Click to expand...

Drop the "Private" keyword from your procedure declarations

example:

Sub Coloreta()
...
...
End Sub
 
eTech said:
Drop the "Private" keyword from your procedure declarations

example:

Sub Coloreta()
...
...
End Sub
Click to expand...

Sorry eTech, but what that has to do with the visibility of the variable declared as "Public" at the event Open for my workbook?
 
atferrari said:
Sorry eTech, but what that has to do with the visibility of the variable declared as "Public" at the event Open for my workbook?
Click to expand...

1. If you declare them private then they can't be called from any other module.
Is that what u want?

2. The Range statement doesn't look right.
There should be two cell values separated
by a comma: ("cell1, cell5")
 
I am using Excel 2003 here, to access the public variable in the code of Sheet1 from the code of ThisWorkbook, simply use
Code: 
Sheet1.verde = "my initialisation string value"

If you want to access by the name of the workbook (using a constant string is not recommended in case the user renames the sheet), it's accessed in the same manner
Code: 
WorkSheets("Hoja1").verde = "my initialisation string value"
 
Last edited:
Solved

Gracias to those that replied.

The problem was trying to get the Public variable declared in ThisWorkbook. IT DOES NOT WORK.
Thanks to a poster in other forum, solved as follows:

In Module1:
Code: 
Option Explicit
Public verdeF As Byte

Sub Coloreta1()
    With ActiveSheet.Shapes("Oval 7").Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, verdeF, 0)
        .BackColor.RGB = RGB(0, 135, 0)
        .TwoColorGradient msoGradientFromCenter, 1
        .Transparency = 0#
    End With
End Sub

In ThisWorkbook

Code: 
Private Sub Workbook_Open()    
    verdeF = 135
    Worksheets("Hoja1").Range("F11").Value = verdeF
End Sub


In WorkSheet1
Code: 
Sub SpinButton_G_F_SpinUp()
    If verdeF < 255 Then
        verdeF = verdeF + 10
    End If
    Worksheets("Hoja1").Range("F11").Value = verdeF
    Call Coloreta1
End Sub

Sub SpinButton_G_F_SpinDown()
    If verdeF > 15 Then
        verdeF = verdeF - 10
    End If
    Worksheets("Hoja1").Range("F11").Value = verdeF
    Call Coloreta1
End Sub
 
atferrari said:
The problem was trying to get the Public variable declared in ThisWorkbook. IT DOES NOT WORK.
Click to expand...
It works both ways (accessing ThisWorkbook.verde from Sheet1, and accessing Sheet1.verde from ThisWorkbook). You may not be able to access it from a module, but you can access from the other sheets and ThisWorkbook. The public variable declared in a module will create a global variable.
 
dougy83 said:
It works both ways (accessing ThisWorkbook.verde from Sheet1, and accessing Sheet1.verde from ThisWorkbook). You may not be able to access it from a module, but you can access from the other sheets and ThisWorkbook. The public variable declared in a module will create a global variable.
Click to expand...

Read many tutorials the last two days but never seen that even mentioned. Have to see how to use it. :eek: How and where do you declare them?

In Excel - Office XP, I declared "Public verde as byte" at the top of ThisWorkbook. The variable "verde" was not recognized inside of Sheet1. Applying Option Explicit in Sheet1, rang the alarm every time.

Solved as I posted above.
 
As mentioned above, to access the variable verde that was defined in ThisWorkbook from Sheet1, you simply write ThisWorkbook.verde
If you wanted to access some public variable (e.g. myVar) declared in Sheet3 from ThisWorkbook you'd use Sheet3.myVar

I'm not sure what "office xp" is, but I've tested it on Excel 2003 and Excel 2010 without issue.
 
dougy83 said:
As mentioned above, to access the variable verde that was defined in ThisWorkbook from Sheet1, you simply write ThisWorkbook.verde
If you wanted to access some public variable (e.g. myVar) declared in Sheet3 from ThisWorkbook you'd use Sheet3.myVar

I'm not sure what "office xp" is, but I've tested it on Excel 2003 and Excel 2010 without issue.
Click to expand...

Gracias dougy! It is not XP, it's me!

I got the idea. Sorry but making head and tails of scope, DIM, Public, Private and Global in just two days was hard. I think I have a better idea now.

Thanks again.
 
Status
Not open for further replies.

Similar threads

camerart
  • Question
Watch variables in Oshonsoft showing 0x000 not 0x00?
Replies
6
Views
2K
Nigel Goodwin
Nigel Goodwin
P
Curious, why the second cast?
Replies
12
Views
2K
granddad
granddad
Lightium
reverse engineer calculator to update the constants.
2
Replies
21
Views
4K
ZipZapOuch
ZipZapOuch
MrDEB
  • Question
one for the books = new boards
Replies
0
Views
1K
MrDEB
MrDEB
sk001
  • Question
How to find the direction of target in complex environment?
Replies
7
Views
2K
sk001
sk001

Latest threads

New Articles From Microcontroller Tips

Back
Top