Use a custom VBA function within a VBA sub

Question

I am trying to create a function that will take the value of a cell formatted something like 5.23*10^-3 and be able to use it in a calculation.

Sub Multiplication_Stuff
Dim a As Double

a = Range('ActiveSheet!$B$2').Value   *****
c = multiplyme(a)

Range("Sheet1!B7) = C
End Sub

Public Function multiplyme(a As Double) As Double
multiplyme = a * 15
End Function

I received the following error in the code:

run-time error'1004' Method 'Range'Of object'_Global' failed

I have also had it formatted the way the range for c is formatted and recieved the same error. i would prefer it to not pull from active sheet but sheet 1 instead.

Chrismas007 · Accepted Answer · 2015-12-07 18:45:00Z

1

In VBA, a Sheet and a Cell are two DIFFERENT objects:

Sub Multiplication_Stuff
    Dim a As Double, c As Double

    a = ActiveSheet.Range("B2").Value
    c = multiplyme(a)

    Sheets("Sheet1").Range("B7").Value = c
End Sub

Public Function multiplyme(a As Double) As Double
    multiplyme = a * 15
End Function

answered Dec 7, 2015 at 18:45

Chrismas007

6,1154 gold badges27 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

Use a custom VBA function within a VBA sub

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related